Macro for File Management Keeps Adding a Subdirectory!

Hi everyone,

I have a new Macro (written in VBA 7.1) which is supposed to do the following:

• In the same directory as the executing Excel spreadsheet, create a new directory called "SAVED_FILES" (if the directory doesn't already exist)
• For each Tab/Worksheet in the Spreadsheet, perform a "Save As" on the Tab/WS, using the tab's name as the new ___.csv filename. Each CSV file should be placed in "SAVED_FILES". If another file is already in the subdirectory with the same name, that file should be overwritten.

E.g., if I have spreadsheet with TabA, TabB, and TabC, then after the Macro is run, I should see this in my current directory:

Please Login or Register  to view this content.

My code is almost there. The first time it runs, it works perfectly. But then the second time it runs, it does this:

Please Login or Register  to view this content.

That's no good. Each time the Macro runs, adds a new "SAVED_FILES" directory in the previous "SAVED_FILES" directory.

Below is the code of the full Macro. (Apologies for the length!) I'll have some observations below:

Please Login or Register  to view this content.

From debugging, I'm pretty sure the problem is here:

Please Login or Register  to view this content.

With every iteration of the Macro, the `currentPath` variable seems to be the `savedFilesPath` variable from the previous iteration. This is why a new subdirectory is appended to the string every time.

But why? I thought that when the Macro exits, no variable data is saved. Is there a way to clear this? Or is the problem elsewhere? Thank you.

Originally Posted by redapplesonly

I have a new Macro (written in VBA 7.1) which is supposed to do the following

Very not what it does so just removing the bad & the useless and adding the appropriate Excel logic like this VBA demonstration :

PHP Code: 

Sub Demo1()
    Dim P$, Ws As Worksheet
        P = ThisWorkbook.Path & "\SAVED_FILES\":  If Dir(P, 16) <> "." Then MkDir P
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    For Each Ws In ThisWorkbook.Worksheets
        Ws.Copy
        ActiveWorkbook.SaveAs P & Ws.Name, 6
        ActiveWorkbook.Close
    Next
        .Speech.Speak "Done!", True
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Yes, this worked perfectly!!! Thank you, this was great! 10,000 Kudos and Karma to you!