CODEWITHSUNDEEP
excelvba
Luis Andrés García
Luis Andrés García

Reputation: 5982

Excel VBA: copy lots of columns

I´d like to copy all data from a Sheet to another sheet of another new Excel file. I have tried this:

Set wkb = Workbooks.Add
wkb.SaveAs myNewFile
ThisWorkbook.Worksheets("Sheet 2").Activate
Set Ticker = ThisWorkbook.Worksheets("Sheet 2").Range("A1").CurrentRegion
Ticker.Copy
wkb.Worksheets(1).Activate
Cells(1, 1).PasteSpecial xlPasteAll
wkb.Save
wkb.Close

The problem I´ve found is that it won´t work when there are a great amount of columns (for instance, from A to OV). Do you know other way to to this?

Upvotes: 1

Views: 42

Answers (1)

SierraOscar
SierraOscar

Reputation: 17647

You could skip the clipboard altogether:

Set wkb = Workbooks.Add
copyAddress$ = ThisWorkbook.Worksheets("Sheet 2").Range("A1").CurrentRegion.Address

With wkb
    .SaveAs myNewFile
    .Sheets(1).Range(copyAddress).Value = ThisWorkbook.Worksheets("Sheet 2").Range(copyAddress).Value
    .Save
    .Close True '// Assuming you would want to save changes
End With

Or you could just copy the entire sheet:

Set wkb = Workbooks.Add
ThisWorkbook.Sheets("Sheet 2").Copy Before:=wkb.Sheets(1)

Upvotes: 4

Related Questions