Caveman1964

Board Regular
Joined
Dec 14, 2017
Messages
108
I received great help from Fluffy yesterday. I've been working all morning and just can't figure this out, its a mess. I am a beginner and putting in time to learn but running out of time. It doesn't help that I am not the sharpest tool in the shed. I appreciate any and all help.
I want to;
Save 4 other open workbooks from a main workbook. Each workbook takes its file name from a cell value + its workbook name.
I want to click a button from;
"Main Workbook", it creates a subfolder under "Job Packets" using value from cell H5, it then saves "workbook1", "workbook2", "workbook3", and "workbook4" using cell H5 as its name + workbook1. (note:, each workbook will have same value in cell H5)
meaning example, cell value from H5 is 123456, the workbook1 saves in a folder as 123456workbook1.I don't need any prompts, just want it done automatically.
so, after the macro has ran, I should be able to go into file explorer and see;
F:\Job Packets\123456\123456workbook1.xlsm
123456workbook2.xlsm
123456workbook3.xlsm
123456workbook4.xlsm

F:\Job Packets\ will always stay. The subfolders changes on job numbers and each job number will have 4 workbook files under it.

The current macro for just one sheet is as below, I got help completing it yesterday.

Sub CreateFolderAndCopy()
Dim fileName As String
With Sheets(1)
If .Range("H5").Value = vbNullString Then Exit Sub
On Error Resume Next
MkDir "F:\Job Packets" & .Range("H5").Value
On Error GoTo 0
Dim NewFN As Variant
NewFN = "F:\Job Packets" & .Range("H5").Value & "" & .Range("H5").Value & "workbook1" & ".xlsm"
ActiveWorkbook.SaveAs NewFN, FileFormat:=52
ActiveWorkbook.Close
End With

End Sub

Ahead of time, Thank You!
 
Last edited:

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Caveman1964

Board Regular
Joined
Dec 14, 2017
Messages
108
Re: Macro to save 4 other open workbooks Need HELP!

Actually, the code for one sheet is this one.

Dim fileName As String
With Sheets(1)
If .Range("H5").Value = vbNullString Then Exit Sub
On Error Resume Next
MkDir "F:\Job Packets" & .Range("H5").Value
On Error GoTo 0
Dim NewFN As Variant
NewFN = "F:\Job Packets" & .Range("H5").Value & " \ " & .Range("H5").Value & "workbook1" & ".xlsm"
ActiveWorkbook.SaveAs NewFN, FileFormat:=52
ActiveWorkbook.Close
End With
 
Last edited:

Caveman1964

Board Regular
Joined
Dec 14, 2017
Messages
108
Re: Macro to save 4 other open workbooks Need HELP!

I figured it out....finally.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top