ranges problem

I've tried searching google for this but no luck...

It's probably very simple to fix but I'm a noob.

Sheets.Add.Name = "temporary"
Sheets("Data Table").Select
ActiveSheet.Range(Cells(2, 1), Cells(21, 4)).Select
Selection.Copy
Sheets("temporary").Select
ActiveSheet.Paste
Application.CutCopyMode = False


I get an error saying "application-defined or object-define error" and when i click debug it points to the ActiveSheet.Range(Cells(2, 1.... line. However, it works if i use the range format "A2:D21"...

I need to use this specific format since I eventually need to put variables in there... any ideas?

Jeff

Hi Jeff,

The problem definitely is with this line:

ActiveSheet.Range(Cells(2, 1), Cells(21, 4)).Select


I think what is happening is that you're running this code within a sheet class module and this is causing a conflict. You are not qualifying the cells properties fully which means that they are properties of the sheet which the code is situated in. In comparison, you have qualified the range property and it is a property of the activesheet. If the activesheet and the sheet you have put your code in are different then you're going to have a problem.

What you need to do is amend your code to fully qualify the cells properties, like this:

ActiveSheet.Range(ActiveSheet.Cells(2, 1), ActiveSheet.Cells(21, 4)).Select

And then you can tidy it up further with:

With Activesheet
.Range(.Cells(2, 1), .Cells(21, 4)).Select
End With

Note the dots in front of the Range and Cells properties.

I hope that makes sense to you - learning about using worksheets and ranges is the bread and butter of working with Excel VBA. As you get more familiar with them you will find that you very rarely need to use the selection object at all and, in fact, it is usually desirable not to use it!

Eg:

With Activesheet
.Range(.Cells(2, 1), .Cells(21, 4)).Select
End With
Selection.Copy

'amend to:
With Activesheet
.Range(.Cells(2, 1), .Cells(21, 4)).Copy
End With



Colin

thanks man, you're awesome :)

You're welcome Jeff! :)

The main thing is that you understand why you were getting the error on this one because it isn't the easiest one to spot. ;)

Colin