Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy selected mulitple worksheets to mulitple new workbooks
Perhaps something like the code below, assuming the data is in Columns A and
B. Note that the "Name" property of a workbook is a read-only property and in order for that to become "NewWB_XX", the workbook has to be saved with that filename. Also, I have put "On Error Resume Next" to simplify and avoid error handling. I recommend you take out that line and add appropriate error handling. Sub test() Dim lRow As Long Dim wb As Workbook On Error Resume Next With ThisWorkbook.Sheets("Sheet1") lRow = 1 While .Range("A" & lRow).Text < "" Set wb = Nothing Set wb = Workbooks(.Range("B" & lRow).Text & ".xls") If wb Is Nothing Then Set wb = Workbooks.Add wb.SaveAs .Range("B" & lRow).Text End If ThisWorkbook.Sheets(.Range("A" & lRow).Text).Copy After:=wb.Sheets(wb.Sheets.Count) lRow = lRow + 1 Wend End With End Sub "Ian" wrote: I have a master workbook with several worksheets, some of which I want to copy to new workbooks based on a table within the original workbook So for example, I have worksheets TSheet1, TSheet2 .... TSheet8 in a workbook, and arelisted in a table together with the name of the NewWB that I want them copied to ..for example : TSheet1 NewWB_01 TSheet2 NewWB_01 TSheet3 NewWB_02 TSheet4 NewWB_03 TSheet5 NewWB_01 TSheet6 NewWB_03 TSheet7 NewWB_02 TSheet8 NewWB_01 So the result should be 3 new workbooks NewWB_01, NewWB_02, NewWB_03 which will each contain copies of the worksheets per the above list. The number and names of both worksheets and new workbooks will need to vary ( I have simplified them in the above example) -- Regards & Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When I try to click 1 cell mulitple cells are selected. | Excel Discussion (Misc queries) | |||
Mulitple Workbooks and Ignoring Blank Cells | Excel Discussion (Misc queries) | |||
Can mulitple Excel workbooks be summarized into 1 workbook? | Excel Discussion (Misc queries) | |||
link to mulitple workbooks | Excel Programming | |||
separate data into mulitple workbooks | Excel Discussion (Misc queries) |