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

 

[SteveOranjin]

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.

When I run the above code, it says specifically, "Run-time error '1004': "Method 'SaveAs' of object '_workbook' failed."

Cell B3 Contains, "File Name"has The content it has in it is "BALFinishUpdate041618"
Cell M10 Contains "Brand Name. The current value of M10 is "Special Lite"
Cell N10 Contains "Brandcode". It currently has the value "SLT in it.

The header of each one is "File Name", "BrandName" and "Brandcode" respectively.

Please let me know if there are any other questions you have.

 

[Domenic]

With those values, the string resolves to...

M:\Vendor Product Data\Special Lite (SLT)\Data Loads (SLT)\BALFinishUpdate041618

Is the path correct? What happens when you manually save to that folder? Also, earlier you had "Special Light" instead of "Special Lite". Which one should it be? Could this be the issue?