Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving multiple worksheets as separate files
I have an excel file that someone put 100 or more sheets in the file. I need
to know how I can extract all the worksheets at the same time as separate files that I can name maybe file1.xlsx, file2,xlsx etc. Right now I have to move or copy each sheet one at a time and that is slow. Thank you in advance for your help. Pauline Moreno |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving multiple worksheets as separate files
Sub Make_New_Books_Increment()
Dim wks As Worksheet Dim lng As Long lng = 1 For Each wks In ActiveWorkbook.Worksheets wks.Copy With ActiveWorkbook .SaveAs FileName:="C:\folder" _ & "\File" & lng & ".xlsx" .Close End With lng = lng + 1 Next wks End Sub Edit the path and folder to your choice. If the current sheets have unique names you may want to save as that name rather than File1, File2 Sub Make_New_Books_ShtName() Dim w As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False For Each w In ActiveWorkbook.Worksheets w.Copy With ActiveWorkbook .SaveAs FileName:="C:\Folder" _ & "\" & w.Name & ".xlsx" .Close End With Next w Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub Gord Dibben MS Excel MVP On Wed, 14 Jan 2009 15:34:13 -0800, Poli wrote: I have an excel file that someone put 100 or more sheets in the file. I need to know how I can extract all the worksheets at the same time as separate files that I can name maybe file1.xlsx, file2,xlsx etc. Right now I have to move or copy each sheet one at a time and that is slow. Thank you in advance for your help. Pauline Moreno |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving multiple worksheets as separate files
See also this example
http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Poli" wrote in message ... I have an excel file that someone put 100 or more sheets in the file. I need to know how I can extract all the worksheets at the same time as separate files that I can name maybe file1.xlsx, file2,xlsx etc. Right now I have to move or copy each sheet one at a time and that is slow. Thank you in advance for your help. Pauline Moreno |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving multiple worksheets as separate files
Thank you so much for your help. Everything worked out just fine.
Poli "Gord Dibben" wrote: Sub Make_New_Books_Increment() Dim wks As Worksheet Dim lng As Long lng = 1 For Each wks In ActiveWorkbook.Worksheets wks.Copy With ActiveWorkbook .SaveAs FileName:="C:\folder" _ & "\File" & lng & ".xlsx" .Close End With lng = lng + 1 Next wks End Sub Edit the path and folder to your choice. If the current sheets have unique names you may want to save as that name rather than File1, File2 Sub Make_New_Books_ShtName() Dim w As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False For Each w In ActiveWorkbook.Worksheets w.Copy With ActiveWorkbook .SaveAs FileName:="C:\Folder" _ & "\" & w.Name & ".xlsx" .Close End With Next w Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub Gord Dibben MS Excel MVP On Wed, 14 Jan 2009 15:34:13 -0800, Poli wrote: I have an excel file that someone put 100 or more sheets in the file. I need to know how I can extract all the worksheets at the same time as separate files that I can name maybe file1.xlsx, file2,xlsx etc. Right now I have to move or copy each sheet one at a time and that is slow. Thank you in advance for your help. Pauline Moreno |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving multiple worksheets as separate files
Good to hear
Thanks for the feedback Gord On Tue, 20 Jan 2009 15:14:00 -0800, Poli wrote: Thank you so much for your help. Everything worked out just fine. Poli "Gord Dibben" wrote: Sub Make_New_Books_Increment() Dim wks As Worksheet Dim lng As Long lng = 1 For Each wks In ActiveWorkbook.Worksheets wks.Copy With ActiveWorkbook .SaveAs FileName:="C:\folder" _ & "\File" & lng & ".xlsx" .Close End With lng = lng + 1 Next wks End Sub Edit the path and folder to your choice. If the current sheets have unique names you may want to save as that name rather than File1, File2 Sub Make_New_Books_ShtName() Dim w As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False For Each w In ActiveWorkbook.Worksheets w.Copy With ActiveWorkbook .SaveAs FileName:="C:\Folder" _ & "\" & w.Name & ".xlsx" .Close End With Next w Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub Gord Dibben MS Excel MVP On Wed, 14 Jan 2009 15:34:13 -0800, Poli wrote: I have an excel file that someone put 100 or more sheets in the file. I need to know how I can extract all the worksheets at the same time as separate files that I can name maybe file1.xlsx, file2,xlsx etc. Right now I have to move or copy each sheet one at a time and that is slow. Thank you in advance for your help. Pauline Moreno |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to open multiple excel files as separate tasks on the taskbar? | Setting up and Configuration of Excel | |||
Macro help - Moving 2 cells from 100 separate files into new folder | Excel Discussion (Misc queries) | |||
Merge worksheets from separate files into one workbook. | Excel Discussion (Misc queries) | |||
How do I separate worksheets in a workbook into individual files? | Excel Worksheet Functions | |||
separate worksheet into multiple worksheets by grouping | Excel Worksheet Functions |