CODEWITHSUNDEEP
vbaexcel
Vignesh Subramanian
Vignesh Subramanian

Reputation: 7289

Copying a column to a new workbook sheet in VBA

I am trying to copy an existing column from a sheet to a column in a new workbook.

The code that I tried:

Set NewBook = Workbooks.Add
Sheets("Sheet1").Columns(1).Copy Destination:=NewBook.Sheets("Sheet1").Columns(2)

This is creating a sheet but no values are copied! This code works if I copy to another sheet in same workbook.

How should I create a new sheet in the NewBook and target columns in that sheet?

Upvotes: 1

Views: 715

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

You must remember the old book, otherwise Sheets("Sheet1").Columns(1) will refer to the first column of the Sheet1 of the newly added one since it is the ActiveWorkbook in this moment (so you actually copy something, but that something is an empty column): 

Sub qwerty()
    Set w1 = ActiveWorkbook
    Set NewBook = Workbooks.Add
    w1.Sheets("Sheet1").Columns(1).Copy Destination:=NewBook.Sheets("Sheet1").Columns(2)
End Sub

EDIT#1:

This will rename the sheet in the new workbook and then perform the copy:

Sub qwerty2()
    Set w1 = ActiveWorkbook
    Set NewBook = Workbooks.Add
    ActiveSheet.Name = "Report"
    w1.Sheets("Sheet1").Columns(1).Copy Destination:=Columns(2)
End Sub

Upvotes: 3

Related Questions