VBA that copies the next column

Question

I have an excel workbook that has a sheet that is linked to an external source, so this sheet is constantly adding new columns. I would like a VBA that will automatically copy the next Column to the right. So right now this is what I have.

Sub copycolumns1() 
Sheets("Productivity").Select     
lMaxCoulmns = Cells(Columns.Count, "N").End(xlUp).Column
Range(Range("N3"), Range("N3").End(xlDown)).Select     
Selection.Copy     
Sheets("Data").Select     
lMaxRows = Cells(Rows.Count, "D").End(xlUp).Row 
Range("D" & lMaxRows + 1).Select     
ActiveSheet.Paste 
End Sub

The way it is setup right now is, when macro is activated it will just copy column "N". I want the script to constantly be moving, so when I run the script it will automatically copy column "O" next.

Is this possible?

Yes, use a variable that holds last column. Google will tell you how to do that in 5 minutes... — findwindow, Nov 13 '15 at 17:34
I would make a habit of `Dim`ing my variables. Otherwise, little misspelling like `lMaxCoulmns` are going to cause hard-to-identify issues. `Option Explicit` is a good thing. — Marc, Nov 13 '15 at 17:41
Check out how you declared `lMaxCoulmns`, it's *always* going to return column N. See my answer for more. — BruceWayne, Nov 13 '15 at 18:31

score 2 · Accepted Answer · edited May 23 '17 at 11:45

Your main issue is setting your IMaxColumns. As of now, it's only going to return column N, because you have your use of Cells() backwards. Try the below:

Sub copycolumns1()
Dim prodWS As Worksheet, dataWS As Worksheet
Dim lMaxColumns&, lMaxRows&, lastRow&
Set prodWS = Sheets("Productivity")
Set dataWS = Sheets("Data")

With prodWS
    lMaxColumns = .Cells(1, .Columns.Count).End(xlToLeft).Column
    lastRow = .Cells(3, lMaxColumns).End(xlDown).Row
    .Range(.Cells(3, lMaxColumns), .Cells(lastRow, lMaxColumns)).Copy
End With

With dataWS
    lMaxRows = .Cells(.Rows.Count, "D").End(xlUp).Row
    .Range("D" & lMaxRows + 1).PasteSpecial
End With

Application.CutCopyMode = False
End Sub

A couple things to pay attention to.
First, note how I used worksheet variables to store your worksheets. This will prevent any confusion on your or VB's part as to which sheet you're working with. Note how I used With and . to set ranges.

Second, I removed the use of .Select, which is best practice (see this thread for more info.)

Also, I updated your line to get the copy range, as yours would always copy column "N", and therefore your lMaxColumns was not used.

Finally, I fixed the lMaxCoulmns issue. You originally had Cells(Columns.Count, "N").End(xlUp).Column

The use of Cells is Cells([row],[column]). So, you're setting the row to start at, as the number of columns (does that make sense? If you only have three columns, you'll start at row 3), then go Up...which could leave you at row 1.

I kept this close to your original VB, so you can see what I did. But, you can tweak this further to remove the use of Copy/Paste, by setting the ranges' values equal. Let me know if you're interested and I'll show you how.

score 1 · Answer 2 · answered Nov 13 '15 at 18:25

This is how I would do it... untested...

Sub CopyLastColumns1() 
    Dim wsP as Worksheet, wsD as Worksheet, lastCol as Long, lastRow as Long, destLastCol as Long
    Set wsP = Sheets("Productivity")     
    lastCol = wsP.Cells(1, wsP.Columns.Count).End(xlToLeft).Column
    lastRow = wsP.Cells(Rows.Count, lastCol).End(xlUp).Row
    wsP.Range(Cells(1, lastCol), Cells(lastRow, lastCol).Copy     
    Set wsD = Sheets("Data")
    destLastCol = wsD.Cells(1, wsD.Columns.Count).End(xlToLeft).Column + 1 
    wsD.Cells(1, destLastCol).PasteSpecial xlPasteValues
End Sub

VBA that copies the next column

2 Answers2