1

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?

Improve this question
1
  • 1
    Would it not be simpler to just copy the sheet? Commented Apr 20, 2015 at 7:46

1 Answer 1

Reset to default
4

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.