Solved: count, insert, copy, paste rows from one worksheet to another

[y0rk1e72]

I have 2 sheets with the following macros.
1 macro counts the number of rows on sheet1 and then inserts the correct number of blank rows on sheet2
The 2nd macro is supposed to copy the range of used cells and paste it to the newly created blank cells on sheet2.

Macros are as follows

 
Sub Count_rows_and_insert()
 
 
     Dim i As Integer
 
   endrow = Cells(ActiveSheet.UsedRange.Rows.Count + _
   1,ActiveCell.Column).End(xlUp).Row
       Sheets("sheet2").Select
 
For i = 1 To endrow 
 
Rows("2:2").Select
     Selection.Insert shift:=xlDown, 
copyorigin:=xlFormatFromLeftOrAbove
   Next i
End Sub 

second macro.

Sub Select_Copy_Paste()
     With ActiveSheet
   .Range("C7",.Cells.SpecialCells(xlCellTypeLastCell)).Select
     End With
 
Selection.Copy
 
Sheets("Sheet2").Select
 
Range("C3").Select
 
ActiveSheet.Paste
   Columns.AutoFit
End Sub 

initially they worked however now macro1 inserts just one row even when there are 47 rows.
Macro2 only seems to copy 5 rows when there could be upto 47

what i need to do is the following.

Have them working again and hopefully if possible combine both the above macros into one.

many thanks for looking and your assistance.

[Teeroy]

Hi y0rk1e72,

Try this to do what you are after in one macro.

[VBA]
Sub allInOne()

Dim nRows As Long

'count rows
With Sheets("sheet1").UsedRange
nRows = .Cells(.Rows.Count, 1).Row - .Cells(1, 1).Row + 1
'insert rows
Sheets("sheet2").Range("A2").Resize(nRows, 1).EntireRow.Insert
'copy and paste
.Copy Sheets("sheet2").Range("A2")
End With
End Sub[/VBA]

[y0rk1e72]

Hi y0rk1e72,

Try this to do what you are after in one macro.

[vba]
Sub allInOne()

Dim nRows As Long

'count rows
With Sheets("sheet1").UsedRange
nRows = .Cells(.Rows.Count, 1).Row - .Cells(1, 1).Row + 1
'insert rows
Sheets("sheet2").Range("A2").Resize(nRows, 1).EntireRow.Insert
'copy and paste
.Copy Sheets("sheet2").Range("A2")
End With
End Sub[/vba]

That works perfectly wow! Thanks a million. I'd done the two that worked as single but couldn't get them combined nd working. That above makes life easier thank you very much.