View Full Version : Solved: Row count of non-Active worksheet
jwise
08-29-2007, 12:07 PM
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
08-29-2007, 12:27 PM
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
08-29-2007, 12:28 PM
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
08-29-2007, 12:58 PM
...and if you need to paste below previous data
ws1.Range("A1:H10").Copy ws2.Cells(Rows.Count, 1).End(xlUp).Offset(2)
jwise
08-31-2007, 09:24 AM
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
08-31-2007, 10:58 AM
Look for 'Object model' in VBA help, and work your way down.
jwise
08-31-2007, 02:57 PM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.