# Excel question: automating a copy & paste **URL:** \ **Category:** Factual Questions\ **Created:** [December 18, 2012, 8:48pm UTC](https://boards.straightdope.com/t/excel-question-automating-a-copy-paste/644453 "2012-12-18T20:48:48Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\ **Post date:** [December 18, 2012, 8:48pm UTC](https://boards.straightdope.com/t/excel-question-automating-a-copy-paste/644453/1 "2012-12-18T20:48:48Z")
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? ---
**Author:** ![redtail23](https://avatars.discourse-cdn.com/v4/letter/r/3e96dc/32.png) [@redtail23](https://boards.straightdope.com/u/redtail23)\ **Post date:** [December 18, 2012, 9:01pm UTC](https://boards.straightdope.com/t/excel-question-automating-a-copy-paste/644453/2 "2012-12-18T21:01:09Z")
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. ---
**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\ **Post date:** [December 18, 2012, 10:20pm UTC](https://boards.straightdope.com/t/excel-question-automating-a-copy-paste/644453/3 "2012-12-18T22:20:26Z")
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?) ---
**Author:** ![mcgato](https://avatars.discourse-cdn.com/v4/letter/m/ac8455/32.png) [@mcgato](https://boards.straightdope.com/u/mcgato)\ **Post date:** [December 18, 2012, 10:58pm UTC](https://boards.straightdope.com/t/excel-question-automating-a-copy-paste/644453/4 "2012-12-18T22:58:31Z")
Could you just switch the down arrow with the ctrl-L arrow? ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [December 18, 2012, 11:11pm UTC](https://boards.straightdope.com/t/excel-question-automating-a-copy-paste/644453/5 "2012-12-18T23:11:30Z")
> [@jsc1953](#): > > 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?) The HOME key should take you to the first column. ---
**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\ **Post date:** [December 18, 2012, 11:21pm UTC](https://boards.straightdope.com/t/excel-question-automating-a-copy-paste/644453/6 "2012-12-18T23:21:08Z")
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. ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [December 18, 2012, 11:38pm UTC](https://boards.straightdope.com/t/excel-question-automating-a-copy-paste/644453/7 "2012-12-18T23:38:25Z")
Try this: ```auto 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. ---
**Author:** ![redtail23](https://avatars.discourse-cdn.com/v4/letter/r/3e96dc/32.png) [@redtail23](https://boards.straightdope.com/u/redtail23)\ **Post date:** [December 18, 2012, 11:39pm UTC](https://boards.straightdope.com/t/excel-question-automating-a-copy-paste/644453/8 "2012-12-18T23:39:48Z")
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. 😛 ---
**Author:** ![Dave\_Hartwick](https://avatars.discourse-cdn.com/v4/letter/d/7feea3/32.png) [@Dave\_Hartwick](https://boards.straightdope.com/u/Dave_Hartwick)\ **Post date:** [December 18, 2012, 11:52pm UTC](https://boards.straightdope.com/t/excel-question-automating-a-copy-paste/644453/9 "2012-12-18T23:52:10Z")
If your data is contiguous, which means no blanks: Range(“A1”).CurrentRegion.Copy Will do. ---
**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\ **Post date:** [December 19, 2012, 12:44am UTC](https://boards.straightdope.com/t/excel-question-automating-a-copy-paste/644453/10 "2012-12-19T00:44:55Z")
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. ---
**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\ **Post date:** [December 19, 2012, 12:53am UTC](https://boards.straightdope.com/t/excel-question-automating-a-copy-paste/644453/11 "2012-12-19T00:53:37Z")
Got it – replaced TroutMan’s offset line with redtail23’s equivalent, and it works like a charm. Thanks, all! ---
**Author:** ![BubbaDog](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bubbadog/32/333_2.png) [@BubbaDog](https://boards.straightdope.com/u/BubbaDog)\ **Post date:** [December 19, 2012, 3:25pm UTC](https://boards.straightdope.com/t/excel-question-automating-a-copy-paste/644453/12 "2012-12-19T15:25:32Z")
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 ```auto '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 ``` ---
**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\ **Post date:** [December 21, 2012, 7:57pm UTC](https://boards.straightdope.com/t/excel-question-automating-a-copy-paste/644453/13 "2012-12-21T19:57:24Z")
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. ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [December 21, 2012, 8:51pm UTC](https://boards.straightdope.com/t/excel-question-automating-a-copy-paste/644453/14 "2012-12-21T20:51:42Z")
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.