Copy and paste a column from one sheet to another

most

Board Regular
Joined
Feb 22, 2011
Messages
89
Platform
  1. Windows
  2. 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.


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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows
Does this do what you need for the first column?
Code:
Sub most()
   Dim FromWs As Worksheet, ToWs As Worksheet
   
   Set FromWs = ActiveWorkbook.Sheets("Old Customer")
   Set ToWs = Workbooks("TheOtherSide.xlsm").Sheets("New Customer")
'Column 1
FromColumn = 3
ToColumn = 4
   lastrow = FromWs.Cells(Rows.Count, FromColumn).End(xlUp).Row
   FromWs.Range(Cells(2, FromColumn), Cells(lastrow, FromColumn)).Copy _
      ToWs.Cells(7, ToColumn)

End Sub
 

most

Board Regular
Joined
Feb 22, 2011
Messages
89
Platform
  1. Windows
  2. Mobile
Somebody should name a road "Fluff", THANKS!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows
Ok we can now expand that like
Code:
Sub most()
   Dim FromWs As Worksheet, ToWs As Worksheet
   Dim Ary As Variant
   Dim i As Long, Lrw As Long
   
   Set FromWs = ActiveWorkbook.Sheets("Old Customer")
   Set ToWs = Workbooks("TheOtherSide.xlsm").Sheets("New Customer")
   Ary = Array(3, 4, 2, 1)
   
   For i = 0 To UBound(Ary) Step 2
      Lrw = FromWs.Cells(Rows.Count, Ary(i)).End(xlUp).Row
      FromWs.Range(Cells(2, Ary(i)), Cells(Lrw, Ary(i))).Copy _
         ToWs.Cells(7, Ary(i + 1))
   Next i
End Sub
Which should do the first two columns
 

Watch MrExcel Video

Forum statistics

Threads
1,108,614
Messages
5,523,906
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top