Solved: Row count of non-Active worksheet

[jwise]

Seems like all my VBA macros need to copy data from one worksheet to another, and only one can be "Active". I know several ways to get the row count of this active sheet, but how do I get the other worksheet's number of rows?

I do not want to temporarily make this secondary worksheet "Active" just to get the row count. Bad things seem to happen when I do this! Nor do I want to check some value on the row to see if it is blank, since I may stop at a blank row as opposed to the "last" row.

TIA

[rbrhodes]

Hi jwise,

Depending on whjat you are trying to do there are different ways, of course. However, in the spirit of avoiding Select/Selection pairs and Sheets("Sheetname").Activate the best way is to dim a variable and then create a sheet object.

The following sample creates 2 sheet objects and 2 range objects and does a copy paste operation.



Option Explicit
Sub SomeSub()
'//Dim variables as sheets
Dim ws1 As Worksheet
Dim ws2 As Worksheet
'//dim variables for lastrow
Dim lastrow1 As Long
Dim lastrow2 As Long
'//dim range variables
Dim rng1 As Range
Dim rng2 As Range
'//Create sheet objects
'using Sheet name
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
'OR using code name
'Set ws1 = sheets(1)
'Set ws2 = sheets(2)

'//Get last row sheet 1
lastrow1 = ws1.Range("A65536").End(xlUp).Row
'//Get last row sheet 1
lastrow2 = ws2.Range("A65536").End(xlUp).Row
'//Set a range on sheet 1
Set rng1 = ws1.Range("A1:C" & lastrow1)
'//Set a range on sheet 2
Set rng2 = ws2.Range("C5")
'//copy/paste operation
rng1.Copy Destination:=rng2
'//destroy objects
Set ws1 = Nothing
Set ws2 = Nothing
Set rng1 = Nothing
Set rng2 = Nothing

End Sub

[Bob Phillips]

Forget about active.

Forget about selecting.

It is all unnecessary.



Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

ws1.Range("A1:H10").Copy ws2.Range("A1")

[mdmackillop]

...and if you need to paste below previous data

ws1.Range("A1:H10").Copy ws2.Cells(Rows.Count, 1).End(xlUp).Offset(2)

[jwise]

Thanks to those who replied. Not only did I learn how to solve my problem, I learned some totally new ideas as well.

When I use VBE, it prompts me to select the next choice as I work down the hierarchy (e.g. Select.Range... or perhaps Object.SubOject...). I do not see the structure to these choices. How does one acquaint himself with these options so I don't have to ask these questions?

My delusion that I am capable of understanding this stuff remains. I simply can't get it out of the IDE.

[Bob Phillips]

Look for 'Object model' in VBA help, and work your way down.

[jwise]

Thank you so much. This is exactly what I was looking for. Now it's a matter of "Can I comprehend?" instead of "Where is it?"

I really appreciate this.