Count, Insert and Copy Rows from different Worksheets

[gguerra333]

I am a VBA novice and need a little help

I have a workbook with three sheets. Sheet 1 is the main sheet and contains formulas and formatting (no data). Sheet 2 contains the data that I need to merge with Sheet 1. That data is copied from Sheet 3 which has a connection to an external file (data.xls). I've set up a macro to copy the necessary data from Sheet 3 to Sheet 2 and format it correctly. What I need is to count the number of records in Sheet 2, insert the same number of rows in Sheet 1 then copy the formulas down to the new rows thereby populating Sheet 1.

I have been able to count the the rows in Sheet 2 as follows (NewRows)

Sub countRows()
Dim NewRows As Long
NewRows = Range("A1").CurrentRegion.Rows.Count
End Sub

The only problem is that it uses CurrentRegion and since I will be inserting the new rows in Sheet 1, I will need to reference Sheet 2 from Sheet 1.
I've tried Worksheets("Sheet2").Rows.Count and can't get it to work. Pretty sure I don't have the syntax correctly, As I've said I'm a novice. Did some searching and could not find a definitive answer

So what I need to do is count the number of rows in Sheet 2, insert that number of rows in Sheet 1 starting at Cell "A3". Then also in Sheet 1 copy Row 3 down the same number of times.

Pretty simple in theory but..

 

[PHV]

I've tried Worksheets("Sheet2").Rows.Count
What about this ?
Worksheets("Sheet2").Range("A1").CurrentRegion.Rows.Count

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[gguerra333]

No go. I get "subscript out of range".

 

[PHV]

Seems like the active workbook don't have a sheet named 'Sheet2'.
Perhaps this ?
Worksheets(2).Range("A1").CurrentRegion.Rows.Count

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[gguerra333]

Thanks PHV, it was the name, got it to work. I also created a simple loop to insert the correct number of rows