most
Board Regular
- Joined
- Feb 22, 2011
- Messages
- 107
- Office Version
- 365
- 2019
- Platform
- Windows
- Mobile
I can't get the code below to work, anybody can see what the issue is? Or have a better way to solve it?
What I want the script to do is to copy data from a column to another sheet in a another workbook. Multiple times, so I need to reuse code to make it sustainable.
As you can see in the example below the first line is different between the workbooks, 2 vs 7.
What I want the script to do is to copy data from a column to another sheet in a another workbook. Multiple times, so I need to reuse code to make it sustainable.
As you can see in the example below the first line is different between the workbooks, 2 vs 7.
Code:
Sub CopyPasteColumn()'Global settings
AWB = ActiveWorkbook.Name
DGW = "TheOtherSide"
'Column 1
FromSheet = "Old Customer"
ToSheet = "New Customer"
FromColumn = 3
ToColumn = 4
LastRow = Cells(Rows.count, FromColumn).End(xlUp).row
Application.Workbooks(AWB).Worksheets(FromSheet).Range(Cells(2, FromColumn), Cells(LastRow, FromColumn)).Copy _
Application.Workbooks(DGW).Worksheets(ToSheet).Range(Cells(7, ToColumn), Cells(LastRow, ToColumn))
'Column 2
FromSheet = "Old Customer"
ToSheet = "New Customer"
FromColumn = 2
ToColumn = 1
LastRow = Cells(Rows.count, FromColumn).End(xlUp).row
Application.Workbooks(AWB).Worksheets(FromSheet).Range(Cells(2, FromColumn), Cells(LastRow, FromColumn)).Copy _
Application.Workbooks(DGW).Worksheets(ToSheet).Range(Cells(7, ToColumn), Cells(LastRow + 5, ToColumn))
'Column 3
'...And so on...
End Sub