Error when copying and pasting column from one sheet to another

%3CLINGO-SUB%20id%3D%22lingo-sub-2027193%22%20slang%3D%22en-US%22%3EError%20when%20copying%20and%20pasting%20column%20from%20one%20sheet%20to%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2027193%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20copy%20and%20paste%20columns%20from%20one%20sheet%20to%20another.%20The%20issue%20is%20that%20in%20the%20source%20sheet%2C%20the%20column%20starts%20from%20row%205%2C%20and%20in%20the%20destination%20sheet%2C%20the%20column%20starts%20in%20the%202nd%20row.%20I%20am%20having%20some%20issues%20getting%20this%20to%20work%20by%20finding%20the%20last%20row%20of%20the%20source%20sheet%20and%20that%20of%20the%20destination%20sheet%20to%20then%20copy%20it%20up%20to%20row%202.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20many%20iterations%2C%20this%20is%20what%20I%20have%20-%20I%20employed%20some%20help%20from%20stackoverflow%20but%20this%20was%20still%20limited%20in%20doing%20the%20job.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20CopyPasteDateUpdate()%0A%0A%0ASheets(%222%200%20Models%22).Select%0Alasr%20%3D%20ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row%0ARange(%22E%3AE%22%2C%20Sheets(%222%200%20Models%22).Cells(lasr%20-%205%2C%205)).Copy%0ASheets(%22Copied%20Data%22).Select%0Alasr1%20%3D%20ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row%0ADest%20%3D%20Range(%22A%3AA%22%2C%20Sheets(%22Copied%20Data%22).Cells(lasr1%20-%202%2C%201)).Value%0A%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20what%20the%20data%20looks%20like%3A%3C%2FP%3E%3CP%3ESource%20sheet%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Source%20sheet.png%22%20style%3D%22width%3A%20608px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243710iC7D50067A63A7611%2Fimage-dimensions%2F608x149%3Fv%3D1.0%22%20width%3D%22608%22%20height%3D%22149%22%20role%3D%22button%22%20title%3D%22Source%20sheet.png%22%20alt%3D%22Source%20sheet.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ETarget%20sheet%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Target%20sheet.png%22%20style%3D%22width%3A%20613px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243711iA5508195C72ABE21%2Fimage-dimensions%2F613x167%3Fv%3D1.0%22%20width%3D%22613%22%20height%3D%22167%22%20role%3D%22button%22%20title%3D%22Target%20sheet.png%22%20alt%3D%22Target%20sheet.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20copy%20and%20paste%20the%20date%20column%20from%20the%20source%20sheet%20to%20the%20target%20sheet.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20code%20works%20but%20brings%20back%20no%20values%20and%20copies%20the%20entire%20column.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2027193%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2027333%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20when%20copying%20and%20pasting%20column%20from%20one%20sheet%20to%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2027333%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F914722%22%20target%3D%22_blank%22%3E%40Desno13%3C%2FA%3E%26nbsp%3BAllow%20me%20to%20leave%20your%20request%20for%20a%20VBA%20solution%20for%20what%20it%20is.%20It%20seems%20that%20the%20Source%20table%20is%20one%20that%20is%20created%20through%20Power%20Query.%20It's%20not%20clear%20though%20where%20the%20Target%20table%20comes%20from%20or%20what%20you%20want%20to%20with%20them%2C%20but%20it's%20probably%20easier%20to%20continue%20using%20Power%20Query%20in%20order%20to%20re-order%20the%20Source%20columns%20and%20then%20Append%20or%20Merge%20the%20two%20tables%20in%20some%20way.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20starting%20row%20of%20the%20Source%20is%20the%20only%20issue%2C%20you%20can%20easily%20fix%20that%20in%20Power%20Query.%20Use%20the%20first%20row%20as%20headers%20and%20then%20just%20delete%20the%20top%20two%20rows.%20Then%20the%20Source%20data%20will%20also%20start%20on%20row%202.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2027994%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20when%20copying%20and%20pasting%20column%20from%20one%20sheet%20to%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2027994%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20response.%20The%20problem%20is%20trying%20to%20run%20a%20macro%20on%20power%20query%20tends%20to%20bring%20up%20an%20error.%20I%20have%20tried%20this%20but%20it%20failed.%20Unless%20you%20know%20of%20a%20fix%3F%20That%20too%20would%20be%20of%20great%20help%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2028320%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20when%20copying%20and%20pasting%20column%20from%20one%20sheet%20to%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2028320%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F914722%22%20target%3D%22_blank%22%3E%40Desno13%3C%2FA%3E%26nbsp%3BNot%20sure%20what%20you%20mean%20by%20%22run%20a%20macro%20on%20Power%20Query%22.%20Without%20an%20example%20of%20your%20file(s)%20and%20clarification%20on%20what%20exactly%20you%20want%20to%20achieve%2C%20it%20will%20be%20difficult%20to%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I am trying to copy and paste columns from one sheet to another. The issue is that in the source sheet, the column starts from row 5, and in the destination sheet, the column starts in the 2nd row. I am having some issues getting this to work by finding the last row of the source sheet and that of the destination sheet to then copy it up to row 2. 

 

After many iterations, this is what I have - I employed some help from stackoverflow but this was still limited in doing the job. 

 

 

 

Sub CopyPasteDateUpdate()


Sheets("2 0 Models").Select
lasr = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
Range("E:E", Sheets("2 0 Models").Cells(lasr - 5, 5)).Copy
Sheets("Copied Data").Select
lasr1 = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
Dest = Range("A:A", Sheets("Copied Data").Cells(lasr1 - 2, 1)).Value


End Sub

 

 

This is what the data looks like:

Source sheet

Source sheet.png

Target sheet

 

Target sheet.png

 

I am trying to copy and paste the date column from the source sheet to the target sheet. 

 

The code works but brings back no values and copies the entire column. 

 

5 Replies

@Desno13 Allow me to leave your request for a VBA solution for what it is. It seems that the Source table is one that is created through Power Query. It's not clear though where the Target table comes from or what you want to with it. But it's probably easier to continue using Power Query in order to re-order the Source columns and then Append or Merge the two tables in some way.

 

If the starting row of the Source is the only issue, you can easily fix that in Power Query. Use the first row as headers and then just delete the top two rows. Then the Source data will also start on row 2.

@Riny_van_Eekelen Thank you for your response. The problem is trying to run a macro on power query tends to bring up an error. I have tried this but it failed. Unless you know of a fix? That too would be of great help

@Desno13 Not sure what you mean by "run a macro on Power Query". Without an example of your file(s) and clarification on what exactly you want to achieve, it will be difficult to help.

@Riny_van_Eekelen Basically, I have gotten the data from the internet. When I try to run macro on it, it brings an error. I have tried and the best way I found was to copy and paste through a macro and then run other macros. 

@Desno13 Still don't understand. Sorry. Are you using Power Query or not? Can you upload an example file? I believe you can probably achieve what you want by using PQ. No macro's needed.