CODEWITHSUNDEEP
vbaexcel
mdo8105
mdo8105

Reputation: 67

VBA that copies the next column

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?

Upvotes: 2

Views: 1073

Answers (2)

BruceWayne
BruceWayne

Reputation: 23283

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.

Upvotes: 2

Chrismas007
Chrismas007

Reputation: 6105

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

Upvotes: 1

Related Questions