Excel question: automating a copy & paste

Factual Questions
1

Let’s say I have a spreadsheet, with data in 2 tabs: Source and Destination.

I want to copy the data from source, appending it to the end of the data in Destination. However, I don’t know in advance how many rows are in either. (I do know the number of columns, if that helps).

I’d like to create a macro to do this, and if there was a keystroke combination that would select all the data in source, and then go to Column A in the first empty row of Destination, that would be perfect. Is there such a thing?

2

Start recording.

Go to the first page.

CTRL-END will take you to the last cell.

SHIFT-CTRL-HOME will select everything from the last cell to the first cell.

CTRL-C will copy all of that data.

Shift to your second page.

CTRL-END will take you to the last cell.

CTRL-LEFTARROW will take you to the first cell in that row.

DOWNARROW will take you to the first empty cell.

CTRL-V will paste all of the data

Stop recording.

3

Awesome.

This is almost perfect…CTRL-L ARROW doesn’t go to the first cell in the row; it goes to the last non-blank cell (from right to left). If column C is blank, it goes to column D.

This will probably work for my purposes, as I’m pretty sure that my data is fully populated and I’ll always want to be in column A. (So is there a way to always go to A of whatever row I’m in? Would clicking on “A” in the macro recorder go to a dynamic row, column A…or a hard-coded row, column A?)

4

Could you just switch the down arrow with the ctrl-L arrow?

5

The HOME key should take you to the first column.

6

I recorded two versions of the keystroke sequence in a spreadsheet with 2981 rows:

Ctrl End + Down Key + clicking on column A

’ EndDownA Macro
’
ActiveCell.SpecialCells(xlLastCell).Select
Range(“I2982”).Select
Range(“A2982”).Select
End Sub

Ctrl End + Down Key + Home Key
’
’ EndDownHome Macro
’

’ ActiveCell.SpecialCells(xlLastCell).Select
Range(“A2982”).Select
End Sub

The hard-coded row reference troubles me – I expect that neither macro will work if there’s a different number of rows.

7

Try this:


Sheets("Source").Select
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets("Destination").Select
Range("A1").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
ActiveSheet.Paste

I think the Offset is all you were missing.

8

Sorry, didn’t have time to look at the actual VBA.

You are correct, those hard ranges will cause trouble.

Try this:

Sheets("Destination").Select
ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(1, 1 - ActiveCell.Column).Select

Or what he said. :stuck_out_tongue:

9

If your data is contiguous, which means no blanks:

Range(“A1”).CurrentRegion.Copy

Will do.

10

I hate to get into “here, debug my code” mode, and I appreciate all the wonderful input…but just thought you should know that TroutMan’s macro resulted in the source data being pasted at cell A3 of the Destination sheet, instead of at the end.

11

Got it – replaced TroutMan’s offset line with redtail23’s equivalent, and it works like a charm.

Thanks, all!

12

Sometimes the xldown method fails for blanks, special characters, etc. So one way to find the bottom row is to take advantage of the fact that the area of the worksheet below the last line is empty. So “end” type navigation is usually perfect.
My favorite way of doing this is to -

Copy my source data
Go to the destination tab
go to the bottom of the sheet (I have excel 2010 so A900000 is a quick typed proxy for near the bottom)
jump “up” to the last line of the existing data
move one row down
paste

This code is listed below. If you have 2007 or greater it may work verbatim with just a few subs where I mentioned it.
The comments are coded to be ignored by the macro



'Copy your range
'sub your top-left area of your copy for A1
      Range("A1").Select  
      Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
      Selection.Copy

'Move to Destination
      Sheets("Destination").Select

'Move to point well past your possible last line
'Sub the known bottom row of your worksheet for A900000 
'or use it if you're confident that your destination table will never reach 900,000 rows
'Excel 2007 and later go to at least 1 million rows

       Range("A900000").Select   
                                 
'Jump up to last line of your destination table
       Selection.End(xlUp).Select

'Move cursor down one row to first empty row
        ActiveCell.Offset(1, 0).Select
'Paste
        Selection.PasteSpecial Paste:=xlPasteValues
13

Bumping, because I just thought of another wrinkle: this is the kind of macro that should be executed once and only once. I could make it auto-execute when the spreadsheet is opened; but how do I stop it from executing every time? (which would cause the source data to be appended to the destination over and over)

I thought of finishing the macro with code to delete the source data. Seems a bit of a kludge, though.

14

Doesn’t seem like a kludge to me. Change it from copy to cut, and first check for any data in the source so you don’t unnecessarily paste in blanks.