Editing VBA code to create a new folder in a very specific location?

[thefastlife]

working with the following code. as of now, it just saves it as a new folder in the C drive.

i'd like to drill a bit deeper and have a relatively long location path.

is this possible? where would i input the exact path?

thanks!

'Create new folder to save the new files in
    DateString = Format(Now, "yyyy-mm-dd hh-mm-ss")
    FolderName = Sourcewb.Path & "\" & Sourcewb.Name & " " & DateString
    MkDir FolderName

 

[Grummet]

DateString = Format(Now, "yyyy-mm-dd hh-mm-ss")
FolderName = Sourcewb.Path & "\" & Sourcewb.Name & " " & DateString
MkDir FolderName


The FolderName string is the path that that folder will end up. It looks like the current code creates a folder in the same location that the workbook is in. If you wanted to change it to something exact and hard coded you would just need to put the location in quotes, like "C:\Test\MyNewSubFolder".

Just keep in mind that running the macro a second time will do nothing if that folder is still there.

For your reference:

Sourcewb.Path = The folder location that the opened workbook is located in.
Sourcewb.Name = The file name of the opened workbook.

Also, if you try to make a dynamic file location and want to test your string output, just add a single quote in front of the 'MkDir FolderName line so it becomes a comment. Then write out MsgBox (FolderName) on a new line. This will pop up a box and let you see what it's writing when you run the macro.

-Grummet

 

[thefastlife]

DateString = Format(Now, "yyyy-mm-dd hh-mm-ss")
FolderName = Sourcewb.Path & "\" & Sourcewb.Name & " " & DateString
MkDir FolderName


The FolderName string is the path that that folder will end up. It looks like the current code creates a folder in the same location that the workbook is in. If you wanted to change it to something exact and hard coded you would just need to put the location in quotes, like "C:\Test\MyNewSubFolder".

Just keep in mind that running the macro a second time will do nothing if that folder is still there.

For your reference:

Sourcewb.Path = The folder location that the opened workbook is located in.
Sourcewb.Name = The file name of the opened workbook.

Also, if you try to make a dynamic file location and want to test your string output, just add a single quote in front of the 'MkDir FolderName line so it becomes a comment. Then write out MsgBox (FolderName) on a new line. This will pop up a box and let you see what it's writing when you run the macro.

-Grummet

thanks Grummet! would i not have to also update this piece too?

    'Save the new workbook and close it
            With Destwb
                .SaveAs FolderName _
                      & "\" & Destwb.Sheets(1).Name & FileExtStr, _
                        FileFormat:=FileFormatNum
                .Close False
            End With

also would the updated code look like this?

DateString = Format(Now, "yyyy-mm-dd hh-mm-ss")
FolderName = "C:\VBA Testing\2016" & "\" & Sourcewb.Name & " " & DateString
MkDir FolderName

 

[thefastlife]

scratch that above question. i figured it out and it was exactly as i suspected. worked great!

one "issue" however. i am working off a pivot table on my main file. when i run this code it completely changes the look of the formatting. i assume b/c in the master file it's driven by that pivot table.

the odd thing is, when i 'Move or Copy' each sheet individually/manually it preserves this formatting.

is there anything that can be done about that?

 

[Grummet]

Glad you found a solution. I don't have an answer to your pivot table issue, sorry :\