y0rk1e72
10-18-2012, 02:02 PM
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.
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.