1

I would like to copy worksheet "Trial Sheet" located in workbook "Trial Data.xlsx" to a seaparate workbook called "Copy Worksheet". I would like to keep the format (column width, zoom, etc.), be able to copy the worksheet without having to open the "Trial Data" workbook which the macro doesn't seem to do.

The macro I recorded is as follows.

Sub worksheet_copy()


Windows("Trial Data.xlsx").Activate
Cells.Select
Selection.Copy
Windows("Copy Worksheet.xlsm").Activate
Cells.Select
ActiveSheet.Paste

End Sub

I came across a previous answer (Copy an entire worksheet to a new worksheet in Excel 2010) but don't understand how to modify for my specific files without an error. Any help would be greatly appreciated.

Community
  • 1
  • 1
rcm19
  • 37
  • 1
  • 6
  • how does [this answer](http://stackoverflow.com/a/8439771/1048539) not work? – enderland Sep 05 '13 at 18:57
  • If I am reading your question correctly, you want to copy a sheet into a "closed" workbook. Look at this answer? http://stackoverflow.com/q/9311188/2258 – Richard Morgan Sep 05 '13 at 18:59
  • 1
    are you using vba for a reason? Why not just copy or move the worksheet to the new workbook? – Cor_Blimey Sep 05 '13 at 18:59
  • Why not open the destination workbook? If you don't want to see it open, just turn off ScreenUpdating. – Tim Williams Sep 05 '13 at 20:03
  • Enderland - The answer referenced gives an error "Run-time error '9': Subscript out of Range" – rcm19 Sep 06 '13 at 11:49
  • Cor_Blimey, eventually I will use the code to open 6-10 workbooks weekly and consolidate all worksheets into one file. I don't want to reference the original workbooks because I am not the owner and they could be changed or updated at any time. – rcm19 Sep 06 '13 at 11:54

1 Answers1

1

This should give you direction:

Sub worksheet_copy()

    Thisworkbook.worksheets("SheetName").Range("A:AB").Copy  _
        destination:= workbooks("Copy Worksheet.xlsm").worksheets(1).cells(1,1)

End Sub

Why using those Windows() stuff ? Be clear: use Workbook for workbooks, Worksheet for...worksheets.
And stop using those slow and unnecessary .Activate !

iDevlop
  • 24,841
  • 11
  • 90
  • 149