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 ws1 As Worksheet
Dim ws2 As Worksheet
Dim lastrow1 As Long
Dim lastrow2 As Long
Dim rng1 As Range
Dim rng2 As Range
' 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.