VBA - How to access range from sheet without activating

[Jer Maine]

Hi, I am using the following code to count the number of rows in a range. For some reason, it only works when I have the source sheet activated. I need it to work no matter what sheet you are on and bypass having to activate a specific sheet. The variable sheetname is a string with the name of the worksheet where the data is coming from. Does anyone know what is causing this to not work?

When I'm on the right sheet, I get rowcount of 15 which is correct. When I'm on a different sheet, I get an error that says: "Run-time error '1004': Application-defined or object-defined error". I have researched this error but have not been able to find what I am doing wrong. It looks correct to me based on what I've found. Any help is greatly appreciated.

rowcount = Worksheets(sheetname).Range("A2", Range("A1").End(xlDown)).Cells.Count

 

[Jonmo1]

You have to specify the sheet on BOTH Rante references..

rowcount = Worksheets(sheetname).Range("A2", Worksheets(sheetname).Range("A1").End(xlDown)).Cells.Count

 

[Jer Maine]

Well, that totally worked! Thank you. Although I don't totally understand why it worked. It looks to me like both range parameters would "understand" that they belong to the initial Worksheets(sheetname) object.

 

[dreid1011]

Well, that totally worked! Thank you. Although I don't totally understand why it worked. It looks to me like both range parameters would "understand" that they belong to the initial Worksheets(sheetname) object.

They would if you had used a With statement. Otherwise, you have to qualify it every time. If I am correct, if you don't, then Excel assumes the active sheet is the target.

With Worksheets(sheetname)
 rowcount = .Range("A2", .Range("A1").End(xlDown)).Cells.Count
End With

 

[Jonmo1]

It looks to me like both range parameters would "understand" that they belong to the initial Worksheets(sheetname) object.

Well that's where they gotcha. Because they don't

Each range statement is a separate command on it's own.

So
Range("A1") - this refers to whichever sheet happens to be active, say it's "Sheet1"
Worksheets(sheetname).Range("A1") - this refers to the sheet named by the sheetname variable.
If they are not the same sheet, = Error.

 

[Jer Maine]

Okay, great to know. I appreciate both of your help. I'm new to this forum, but it's been a great source of guidance so far.