Better way of copy and pasting values

1
votes

I'm working on a template part of which requires me to copy and paste multiple rows of values with the same number of columns from different sheets and append them together (i.e. copy values in sheet 1, paste in sheet 10, copy value from sheet 2, paste in sheet 10 but after the last row of the values copied from sheet 1, and so on). I usually use

Range(Selection, Selection.End(xlDown)).Select
Range(Selection,Selection.End(xlToRight)).Select

to copy the data.

Then I use a do while statement to find the last row of the target sheet (i.e. sheet 10) and paste it there:

Range("A6").Select
Do While ActiveCell.Value <> ""             
ActiveCell.Offset(1, 0).Select
Loop

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

But I'm having a problem if one of the source sheets only has one row. It copies the row and all the empty rows after it till the bottom row of excel, and thus, excel is unable to paste it to the target sheet because of its size. Any better way to do this?

2
vbaexcel
Range(Selection, Cells(Rows.Count, Selection.Column).End(xlUp)).Select would be a better way of selecting the initial set of rows, but please read this answer about how to avoid using Select – YowE3K
What does the range look like you are trying to copy? Columns A through what? – VBA Pete
Columns A thru N. – jamz_javier

2 Answers

0
votes

you can use this to get the last row in the source sheets and select cells

Dim lr As Long
lr = Cells(Rows.Count, Selection.Column).End(xlUp).Row
Range(Selection, Cells(lr, Selection.Column)).Select

similarly in your target sheet you could use 

Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select

However I would highly recommend to not use .Select in your code

0
votes

When putting together a macro to go to the last row, using something like activecell.end(xldown).select will select the very final row if there is only one row.

One way I get around this is using an IF statement to check whether row 2 contains data in the column: (selecting from A1 down to A3 will work with activecell.end(xldown).select)

Sub Example()
Range("A2").Select
If IsEmpty(ActiveCell.Value) then
'do this set of instructions
else
'do that set of instructions
end if
End Sub

In your case, selecting an unknown number of rows to copy to another sheet could be done using something simialr to this:

Sub Example2()

Dim ws as worksheet
Set ws = ThisWorkbook.Sheets("Sheet10") 'change quotes to sheet 10 name if different

Dim i as integer
i = 1

While i = <=9
ThisWorkbook.sheets("sheet" & i).activate
Range("A2").Select
If IsEmpty(ActiveCell.Value) then
EndRow = "2"
Go To NoA2 'Go to will skip to NoA2 below
Else
ActiveCell.End(xlDown).Select
EndRow = activecell.row 'this will get the row number for later
End If
NoA2: 'skip will continue from here
ActiveCell.end(xlToRight).Select
EndCol = Split(ActiveCell(1).Address(1, 0), "$")(0) 'Get column letter
Range("A1:" EndCol & EndRow).Select
Selection.Copy
ws.activate
Range("A2").Select
If IsEmpty(ActiveCell.Value) then
ActiveCell.PasteSpecial Paste:=xlPasteValues
Else
Range("A1").Select
ActiveCell.End(xlDown).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues
i = i+1
Wend

End Sub

Both sets of code are untested, but the second should do what you need by looping through each sheet based on sheet name being "Sheet1", "Sheet2" etc. up to sheet 9

Hope that helps you