VBA: Dynamic Save Loaction

[SteveOranjin]

Hello,

I have a macro set up that is designed to copy all of the data in a particular tab on a sheet, and transfer it to a new sheet, and then to save that sheet. I'm wondering something though, since all of the folders that I save data sheets in have a certain naming convention to them, is there a way that I can have excel capture that so the data I'm saving is always moved to the right folder?

Here is an example:

Sub Copy_Save_Workbook()    '~~> This will create a new workbook with the relevant sheet
    ThisWorkbook.Sheets("Data Sheet").Copy


    '~~> Save the new workbook
    ActiveWorkbook.SaveAs "M:\Vendor Product Data\Site Light (SLT)\Data Loads (SLT)", FileFormat:=51
End Sub

The part that changes for the folders is highlighted in Green. That data ALWAYS ALWAYS enter every sheet I make. It is also contained in "Macro!M10" (That is the first section of the highlight) and "Macro!N10" (that is contained in the second part of the highlight.)

Hope you are well,

Steve

 

[Domenic]

Try...

Sub Copy_Save_Workbook()
    Dim sFolderPartA As String
    Dim sFolderPartB As String
    
    With ThisWorkbook
        sFolderPartA = .Sheets("Macro").Range("M10").Value
        sFolderPartB = .Sheets("Macro").Range("N10").Value
        .Sheets("Data Sheet").Copy
    End With


    ActiveWorkbook.SaveAs "M:\Vendor Product Data\" & sFolderPartA & " (" & sFolderPartB & ")\Data Loads (SLT)", FileFormat:=51
End Sub

Hope this helps!

 

[SteveOranjin]

Try...

Sub Copy_Save_Workbook()
    Dim sFolderPartA As String
    Dim sFolderPartB As String
    
    With ThisWorkbook
        sFolderPartA = .Sheets("Macro").Range("M10").Value
        sFolderPartB = .Sheets("Macro").Range("N10").Value
        .Sheets("Data Sheet").Copy
    End With


    ActiveWorkbook.SaveAs "M:\Vendor Product Data\" & sFolderPartA & " (" & sFolderPartB & ")\Data Loads ( & sFolderPartB & ")", FileFormat:=51
End Sub

Hope this helps!

It does. EXCEPT that the last "\Data Loads (" & sFolderPartB & ")" is one folder to shallow. It's sitting in the folder before the one I'm hoping it will go in. I tried adding another "" but it says there is a syntax error.

Steve

 

[Domenic]

Sorry, I missed the second SLT. Is this what you tried?

ActiveWorkbook.SaveAs "M:\Vendor Product Data\" & sFolderPartA & " (" & sFolderPartB & ")\Data Loads (" & sFolderPartB & ")", FileFormat:=51

 

[SteveOranjin]

Sorry, I missed the second SLT. Is this what you tried?

ActiveWorkbook.SaveAs "M:\Vendor Product Data\" & sFolderPartA & " (" & sFolderPartB & ")\Data Loads (" & sFolderPartB & ")", FileFormat:=51

Yes. It is saving it in "Site Light (SLT)" and naming it "Data Loads (SLT)"

 

[Domenic]

Sorry, but I'm a bit confused. Can you post in plain text (no variables) the full path and filename that you expect, along with the values contained in M10 and N10?

 

[SteveOranjin]

Sorry, but I'm a bit confused. Can you post in plain text (no variables) the full path and filename that you expect, along with the values contained in M10 and N10?

M:\Vendor Product Data\Special Light (SLT)\Data Loads (SLT)

"Data Loads (SLT)" is a folder unto itself. It is contained within "Special Light". "Data Loads (SLT)" is the folder I want the file to go into.

When I am saving the sheet with your macro, it is calling it, "Data Loads (SLT)".

M10 contains a "BrandName" so in this case it contains, Special Light. N10 contains the "BrandCode" or in this case, "SLT"

Here is a link to the file.

https://1drv.ms/x/s!AuLIQSvmFBoEiQxrjbE1KtU9Nrie

 

[Domenic]

Then you'll need to add a backslash (\) and the file name to your string. If you need help with this, you'll need to specify how the workbook will be named.

 

[SteveOranjin]

Sub Copy_Save_Workbook()
    Dim sFolderPartA As String
    Dim sFolderPartB As String
    Dim sFolderPartC As String
    
    With ThisWorkbook
        sFolderPartA = .Sheets("Macro").Range("M10").Value
        sFolderPartB = .Sheets("Macro").Range("N10").Value
        sFolderPartC = .Sheets("Macro").Range("B3").Value
        .Sheets("Data Sheet").Copy
    End With
    
    
    ActiveWorkbook.SaveAs "M:\Vendor Product Data\" & sFolderPartA & " (" & sFolderPartB & ")\Data Loads (" & sFolderPartB & ")\" & sFolderPartC & "", FileFormat:=51
End Sub

It doesn't like that.

 

[Domenic]

When you say that "it doesn't like that", what do you mean exactly? Do you get an error? If so, which one? Also, it might help if you posted the actual values contained in M10, N10, and B3.