PDA

View Full Version : [SOLVED] Save specific sheets as separate workbooks



Sir Babydum GBE
03-01-2019, 05:52 AM
Hi, long time no sea!

I would like to have Sheets 3, 4, 5, 6, 7 and 8 of a 10-sheet workbook saved as six individual workbooks (in the same location as wherever the current workbook is saved in). IE each new wb will consist of only the one sheet.

Columns B & C need to have all formulae converted to values

The Names that appear on the sheet tabs may be changed by the user, so i need vba to ignore them and use the hidden vba name for the sheets which is sheet3, sheet4 etc.

the filename for the sheet needs to be a date (yyyy/mm) a space and whatever is in cell B2 of each of the sheets.

Any help would be greatly appreciated.

thanks

Sir BD

Paul_Hossler
03-01-2019, 10:00 AM
I'd just do something simple like this




Option Explicit

Sub MakeSomeWorkbooks()
Application.ScreenUpdating = False
Application.CalculateFull
CopySheet Sheet3
CopySheet Sheet4
CopySheet Sheet5
CopySheet Sheet6
CopySheet Sheet7
CopySheet Sheet8
Application.ScreenUpdating = True
End Sub




Private Sub CopySheet(ws As Worksheet)
Dim wb As Workbook
Dim sFile As String

sFile = ws.Parent.Path & Application.PathSeparator & Format(Date, "yyyy-mm") & " " & ws.Range("B2").Text

ws.Copy
Set wb = ActiveWorkbook

wb.SaveAs sFile, xlOpenXMLWorkbook
wb.Close

ThisWorkbook.Activate
End Sub

Kenneth Hobs
03-02-2019, 07:11 PM
If you have a lot of formulas to convert to values, it would probably be faster for the macro to copy the workbook, change columns B and C as I did, and then work from there. That would also eliminate the extra save.

If don't have a lot of data, this change to Paul's routine should suffice.


Private Sub CopySheet(ws As Worksheet)
Dim sFile As String, r As Range

sFile = ws.Parent.Path & Application.PathSeparator & Format(Date, "yyyy-mm") & " " & ws.Range("B2").Text

ws.Copy
With ActiveWorkbook
.SaveAs sFile, xlOpenXMLWorkbook
Set r = Intersect(.Worksheets(1).UsedRange, .Worksheets(1).Columns("B:C"))
r.Value = r.Value
.Save
.Close
End With

ThisWorkbook.Activate
End Sub

Paul_Hossler
03-03-2019, 07:43 AM
Yea, I forgot the B:C to values part :crying:

Sir Babydum GBE
03-05-2019, 04:09 AM
Thanks both. That worked perfectly. Thanks for your time and sorry for the delay in my response.