Macro to create new folder based on cell reference

simpleman56

New Member
Joined
Apr 6, 2004
Messages
3
Hi Everybody,

I'm new to VB and am having a problem trying to perform a couple of different tasks with one macro. I have been able to find two seperate macros but I need them combined into one. I am trying to
(1) Create a new folder based on the contents of Sheet2.RangeB2
(2) If the folder exists, just skip to the next step
(3) Copy Sheet2 and
(4) Save the sheet into the new folder as Sheet2.RangeB2 & Sheet2.RangeB8

Sheet2.RangeB2 is variable text
Sheet2.RangeB8 is the current date - 6-Apr-04 format

Here are the two macros that I found to accomplish each task individually
Sub Create_Folder()


Dim Msg As String
On Error Resume Next
Msg = "Folder Not Created" & vbCrLf & vbcrl
Msg = Msg & "Make Sure The File Path Is Valid" & vbCrLf
Msg = Msg & "And That It Contains Valid Characters."
MkDir "C:\" & Sheets(2).Range("B2")
If Err <> 0 Then MsgBox Msg, vbCritical


Sub CopyMe()
Dim SaveMeAs As String
SaveMeAs = Sheets("Sheet2").Range("B2") & Sheets("Sheet2").Range("B8").Text
Sheets("Sheet2").Copy
ActiveWorkbook.SaveAs Filename:="C:\My Documents\" & SaveMeAs
End Sub

Thanks in advance for your help :biggrin:
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Well, off Hand, something like

Code:
Sub Create_Folder() 


Dim Msg As String 
On Error Resume Next 
Msg = "Folder Not Created" & vbCrLf & vbcrl 
Msg = Msg & "Make Sure The File Path Is Valid" & vbCrLf 
Msg = Msg & "And That It Contains Valid Characters." 
MkDir "C:\" & Sheets(2).Range("B2") 
If Err <> 0 Then MsgBox Msg, vbCritical 

Application.Run CopyMe

End Sub


Sub CopyMe() 
Dim SaveMeAs As String 
SaveMeAs = Sheets("Sheet2").Range("B2") & Sheets("Sheet2").Range("B8").Text 
Sheets("Sheet2").Copy 
ActiveWorkbook.SaveAs Filename:="C:\My Documents\" & SaveMeAs 
End Sub

Should work.

Keep the Two Subs Separate. Just run Copy me, which should call CopyMe.

(y)
 
Upvote 0
This will save the sheet into C:\My Documents and not the new folder created by the first program. I just need to know how to get it to save to the new folder, which could change, but may not.
 
Upvote 0
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CreateFolderAndCopy()
    <SPAN style="color:#00007F">Dim</SPAN> fileName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    
    <SPAN style="color:#00007F">With</SPAN> Sheets(2)
        <SPAN style="color:#00007F">If</SPAN> (.Range("B2") = vbNullString) <SPAN style="color:#00007F">Or</SPAN> (.Range("B8") = vbNullString) <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        
        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
        MkDir "C:\" & .Range("B2")
        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0

        fileName = .Range("B2") & "\" & .Range("B8").Text
        .Copy
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    
    ActiveWorkbook.SaveAs "C:\" & fileName
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
Im doing sort of the same thing, but cant get the remaining parts to work. I have it creating the folder. One thing I do need it to do is overwrite the folder if it is already present. Also what I am trying to do is publish a range to the folder I created. The range is on worksheet "Stats" and the range is "A1:C41". I need this file to be exported as a single file web page with the .mht format and the name of the file should be sigs 2009.
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

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
Back
Top