CODEWITHSUNDEEP
vbaexcel
Dylan F
Dylan F

Reputation: 81

How to copy columns within same worksheet Excel VBA

I have a program that needs to copy select columns within the same workbook and worksheet. The current code results in Excel crashing, so I'm not sure if it is working or not.

Is there a better way to copy the columns within the same worksheets with the same workbook?

Code:

Sub Macro1()

Dim wb1 As Workbook

'Set it to be the file location, name, and file extension of the Working File
Set wb1 = Workbooks.Open("Z:\XXX\Working File.xlsx")

MsgBox "Copying Fields within Working File"

wb1.Worksheets(1).Columns("G").Copy wb1.Worksheets(1).Columns("H").Value
wb1.Worksheets(1).Columns("J").Copy wb1.Worksheets(1).Columns("O").Value
wb1.Worksheets(1).Columns("K").Copy wb1.Worksheets(1).Columns("N").Value
wb1.Worksheets(1).Columns("M").Copy wb1.Worksheets(1).Columns("P").Value

wb1.Close SaveChanges:=True

End Sub

Upvotes: 2

Views: 4445

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

Try this, it sets two ranges' values equal, which will keep the data, but no formatting. It should be quicker.

Sub Macro1()
Dim wb1 As Workbook
'Set it to be the file location, name, and file extension of the Working File
Set wb1 = Workbooks.Open("Z:\XXX\Working File.xlsx")

MsgBox "Copying Fields within Working File"

With wb1.Worksheets(1)
    .Columns("H").Value = .Columns("G").Value
    .Columns("O").Value = .Columns("J").Value
    .Columns("N").Value = .Columns("K").Value
    .Columns("P").Value = .Columns("M").Value
End With

wb1.Close SaveChanges:=True

End Sub

Note you're using a whole column, so it might hang up or take a little longer. If you want, you can instead just get the last Row of each column and use that to shorten the ranges being copied.

Edit: As mentioned above, you may be better off using a smaller range. This is a little more verbose, but you should be able to follow what it's doing:

Sub Macro1()
Dim wb1 As Workbook
Dim lastRow As Long
'Set it to be the file location, name, and file extension of the Working File
Set wb1 = ActiveWorkbook

MsgBox "Copying Fields within Working File"

With wb1.Worksheets(1)
    lastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
    .Range("H1:H" & lastRow).Value = .Range("G1:G" & lastRow).Value

    lastRow = .Cells(.Rows.Count, "J").End(xlUp).Row
    .Range("O1:O" & lastRow).Value = .Range("J1:J" & lastRow).Value

    lastRow = .Cells(.Rows.Count, "K").End(xlUp).Row
    .Range("N1:N" & lastRow).Value = .Range("K1:K" & lastRow).Value

    lastRow = .Cells(.Rows.Count, "M").End(xlUp).Row
    .Range("P1:P" & lastRow).Value = .Range("M1:M" & lastRow).Value
End With

wb1.Close SaveChanges:=True

End Sub

Upvotes: 4

Related Questions