Sheet to Workbook

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

How can i copy all sheets in a workbook to their own
workbook with the name of the sheet as the new name of the
workbook?
 
Hi Ed,
How can i copy all sheets in a workbook to their own
workbook with the name of the sheet as the new name of the
workbook?
Click to expand...


Option Explicit

Sub SaveAsSheet()
Dim oSh As Worksheet
Dim sPath As String
sPath = "c:\data\"
For Each oSh In ThisWorkbook.Worksheets
oSh.Copy
ActiveWorkbook.SaveAs sPath & oSh.Name
ActiveWorkbook.Close
Next
End Sub

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
Try this

Sub test()
Application.ScreenUpdating = False
For a = 1 To Sheets.Count
Sheets(a).Copy
ActiveWorkbook.SaveAs Sheets(1).Name
ActiveWorkbook.Close
Next a
Application.ScreenUpdating = True
End Sub
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Save each sheet of multi-sheet workbook as a single-sheet workbook 3
Copying cells and formula from closed workbook to all sheets in open workbook 0
Macro for all Excel Workbooks to print to PDF 2
Export Sheet Values to new workbook 20
Copy cell value from one worksheet to matching worksheet in another workbook 4
adding footer to all existing sheets in workbook and also to newsheets which is added 1
Excel Move or Copy Stopped Working? 0
Excel shortcut options 0

Back
Top