PDA

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



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.

Teeroy
10-19-2012, 12:15 AM
Hi y0rk1e72,

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


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

y0rk1e72
10-19-2012, 01:06 AM
Hi y0rk1e72,

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


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

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.