Excel VBA : copy lots of columns

Question

我想将所有数据从工作表复制到另一个新 Excel 文件的另一工作表。我试过这个:

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

我发现的问题是当有大量列时(例如，从 A 到 OV)它不会工作。你知道其他方法吗？

Answer 1

您可以完全跳过剪贴板:

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

或者您可以只复制整个工作表:

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