Saving worksheets in workbook as separate files in separate folders

[derek.hughes]

I have a workbook with payroll actuals data for all our managers to review and assure we are not making mistakes in our process and that we paid people the correct amounts. It is approximately 19MB large and is shared on a network drive that takes WAY too long to open.

I want to:

• Export each sheet in the workbook to a separate file, AND within a separate folder
  • i.e. Sheet1 in Folder1, Sheet2 in Follder2, etc.
• Be able to save each file as the current payroll date, so it doesn't overwrite the previous file

Can anyone help me with a macro that will do this?
I should note - you will need to specify where I paste the folder save locations as I am not very well versed in VB script writing.

Thank you!

Derek

 

[tlowry]

Here is a first cut at what you described:

Instructions:

1. Open your test Excel file (the one that will be “the really big file”)
2. Copy this code to a module
3. Set PAYDATE to the correct payday string (don't use / or \)
4. Set SAVEDIR to an existing directory where the new directories will be stored (make sure that it ends with a “\”)
5. Run MakeFiles
6. Verify files are created and contain correct sheets

[B]Const PAYDATE = "10-23-2012 44"
Const SAVEDIR = "C:\TEMP\SAVE\"[/B]
Sub MakeFiles()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    On Local Error Resume Next
    Set newwb = Workbooks.Add
    For i = 1 To ThisWorkbook.Sheets.Count
        dirpath = SAVEDIR & "folder" & Right("000" & i, 3)
        MkDir dirpath
        ThisWorkbook.Sheets(i).Cells.Copy
        With newwb
            .Sheets(1).Cells(1, 1).PasteSpecial xlAll
            .SaveAs dirpath & "\" & PAYDATE & " " & ThisWorkbook.Sheets(i).Name
        End With
    Next i
    newwb.Close
    newwb = Nothing
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

 

[derek.hughes]

Unfortunately it is creating a blank sheet...

I came up with this, but it saves every sheet in the SAME folder, and the name is the sheet name versus the date. Can you help me modify this code?



Option Explicit
Sub testme()


Dim wks As Worksheet
Dim newWks As Worksheet


For Each wks In ActiveWorkbook.Worksheets
wks.Copy 'to a new workbook
Set newWks = ActiveSheet
With newWks
Application.DisplayAlerts = False
.Parent.SaveAs Filename:="C:\Users\derek.hughes\Desktop\ZZ-TEST\" & .Name, _
FileFormat:=xlWorkbookNormal
Application.DisplayAlerts = True
.Parent.Close savechanges:=False
End With
Next wks


End Sub

 

[tlowry]

derek.hughes

It's time to go back to the forum...

So, I need it to be:
-Sheet 1 to (let's call it) ATHLETICS folder
-Sheet 2 to MARKETING folder
-etc.

I need more precise information on the folder names vs. worksheet names for all sheets in the workbook. Or you need to specify a folder to which you have write access (probably local; or one the system admin has set up for you).