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.
lMaxCoulmns = Cells(Columns.Count, "N").End(xlUp).Column
lMaxRows = Cells(Rows.Count, "D").End(xlUp).Row
Range("D" & lMaxRows + 1).Select
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?
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
. 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
The use of
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.
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