SOLVED

File Name Macro

%3CLINGO-SUB%20id%3D%22lingo-sub-2539641%22%20slang%3D%22en-US%22%3EFile%20Name%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2539641%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20Macro%20(ExcelFileName)%20in%20the%20attached%20file%20that%20creates%20a%20new%20name%20for%20the%20file%20based%20on%20the%20highlighted%20cells%20in%20the%20screenshot%20below.%20The%20naming%20convention%20that%20we%20use%20is%20as%20follows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERule%20Code_Short%20Description_Dollar%20Amount_Cent%20Amount_Initials.xlsm%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22westcottk_1-1626115735663.jpeg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F295088iF060EF8346CA8A7E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22westcottk_1-1626115735663.jpeg%22%20alt%3D%22westcottk_1-1626115735663.jpeg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20what%20I%20would%20like%20to%20be%20produced%20for%20the%20attached%20entry%3A%20YRA_ReclassRev_400_00_KAW.xlsm%3C%2FP%3E%3CP%3EThis%20is%20what%20is%20currently%20being%20produced%20by%20the%20macro%3A%20YRA_ReclassRev_400_KAW.xlsm%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnfortunately%2C%20I%20am%20not%20able%20to%20get%20the%20Macro%20to%20include%20the%20%E2%80%9C_00%E2%80%9D%20when%20there%20is%20a%20round%20number%20such%20as%20%24400.00.%26nbsp%3B%20The%20Macro%20works%20in%20cases%20where%20we%20don%E2%80%99t%20have%20a%20round%20number%20such%20as%20%24400.87.%26nbsp%3B%20Here%20is%26nbsp%3B%20the%20part%20of%20the%20Macro%20that%20creates%20the%20file%20name%20based%20on%20the%20various%20cell%20values%20with%20Cell%20K13%20being%20the%20%24400.00%20in%20this%20example.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EstrName%20%3D%20Replace(Range(%22k15%22)%20%26amp%3B%20%22_%22%20%26amp%3B%20Range(%22k5%22)%20%26amp%3B%20%22_%22%20%26amp%3B%20Range(%22k13%22)%20%26amp%3B%20%22_%22%20%26amp%3B%20Range(%22k4%22)%2C%20%22%20%22%2C%20%22%22)%3C%2FP%3E%3CP%3EstrName%20%3D%20Replace(strName%2C%20%22.%22%2C%20%22_%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2539641%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2539769%22%20slang%3D%22en-US%22%3ERe%3A%20File%20Name%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2539769%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1101615%22%20target%3D%22_blank%22%3E%40westcottk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUse%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3E%20%20%20%20strName%20%3D%20Replace(Range(%22k15%22)%20%26amp%3B%20%22_%22%20%26amp%3B%20Range(%22k5%22)%20%26amp%3B%20%22_%22%20%26amp%3B%20Format(Range(%22k13%22)%2C%20%220.00%22)%20%26amp%3B%20%22_%22%20%26amp%3B%20Range(%22k4%22)%2C%20%22%20%22%2C%20%22%22)%0A%20%20%20%20strName%20%3D%20Replace(strName%2C%20%22.%22%2C%20%22_%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2539823%22%20slang%3D%22en-US%22%3ERe%3A%20File%20Name%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2539823%22%20slang%3D%22en-US%22%3EThanks%20so%20much%2C%20Hans!%3C%2FLINGO-BODY%3E
New Contributor

I have a Macro (ExcelFileName) in the attached file that creates a new name for the file based on the highlighted cells in the screenshot below. The naming convention that we use is as follows:

 

Rule Code_Short Description_Dollar Amount_Cent Amount_Initials.xlsm

 

westcottk_1-1626115735663.jpeg

 

 

This is what I would like to be produced for the attached entry: YRA_ReclassRev_400_00_KAW.xlsm

This is what is currently being produced by the macro: YRA_ReclassRev_400_KAW.xlsm

 

Unfortunately, I am not able to get the Macro to include the “_00” when there is a round number such as $400.00.  The Macro works in cases where we don’t have a round number such as $400.87.  Here is  the part of the Macro that creates the file name based on the various cell values with Cell K13 being the $400.00 in this example. 

 

strName = Replace(Range("k15") & "_" & Range("k5") & "_" & Range("k13") & "_" & Range("k4"), " ", "")

strName = Replace(strName, ".", "_")

 

Any suggestions would be greatly appreciated.

2 Replies
best response confirmed by westcottk (New Contributor)
Solution

@westcottk 

Use

    strName = Replace(Range("k15") & "_" & Range("k5") & "_" & Format(Range("k13"), "0.00") & "_" & Range("k4"), " ", "")
    strName = Replace(strName, ".", "_")
Thanks so much, Hans!