Jan 02 2021 04:09 PM
Jan 02 2021 04:09 PM
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:
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.
Jan 02 2021 10:58 PM - edited Jan 02 2021 10:59 PM
@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.
Jan 03 2021 01:06 PM
@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
Jan 03 2021 09:11 PM
@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.
Jan 06 2021 01:30 PM
@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.
Jan 07 2021 04:02 AM
@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.