PDA

View Full Version : Copy worksheets to new workbooks



cthai
03-18-2007, 05:48 PM
Hi all,

Hoping for some help, it would be greatly appreciated. I'm new to vba and my task at hand involves copying worksheets from multiple workbooks and pasteing them into a new workbook with multiple sheets.

I have 12 workbooks to start with. File names are Jan2006 thru Dec2006. Each workbook has about 50+ worksheets, named T01 thru T58.

What I would like to happen is to copy the range("C4:O58") of each worksheet, titled T01, from each workbook and paste them into a new workbook, to be named table01. So the new work book will be named table01, and have 12 worksheets, named Jan, Feb, Mar... Dec, corresponding with the workbook they are coming from.

Could I have some help with a template to get me started. If you need anymore details please let me know. I really appreciate the help. thank you

Bob Phillips
03-18-2007, 06:32 PM
Untested.
cSheets = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 12
Set oWB = Workbooks.Add
Application.SheetsInNewWorkbook = cSheets
For i = 1 To 12
Workbooks(Format(DateSerial(Year(Date) - 1, 1, 1), "mmmyyyy")).Open
ActiveWorkbook.Worksheets("T01").Range("C4:O58").Copy oWB.Worksheets(i).Range("A1")
oWB.Worksheets(i).Name = Format(DateSerial(Year(Date), 1, 1), "mmm")
Next i

Bob Phillips
03-18-2007, 06:33 PM
Malcolm, help!

lucas
03-18-2007, 06:45 PM
Hope I got the lines correct Bob...

Bob Phillips
03-18-2007, 08:47 PM
Thanks Steve. No idea why this has started to happen.