Automatically Save

[Guest]

Hi,

I have created a quote template that ultimately gets saved to a network
drive. I know I can have VBA automatically save to a specific folder that I
program.
My question is, my group saves this quote in a particular folder based on
order #. So I have about 10 different folders that it could potentially be
saved in, based on where the order # falls. For example, the order # is
30256-10. The "-10" of the order # makes no difference. Therefore this file
would be saved in the 30000-39999 range. There are folders with ranges of
20000-29999, 30000-39999, 40000-49999 etc.. How can I have VBA analzye the
sales order # (say in cell L10) and save to the appropriate folder (this case
30000-39999). These folders would be located in G:\server1\orders.

Thanks for any help.

 

[Tom Ogilvy]

sOrderNumber = "30256-10"
(so sOrderNumber = worksheets(1).Range("L10").text)
sBase = Trim(Left(sOrderNumber,instr(1,sOrderNumber, _
"-",vbTextCompare)-1))
sUpper = application.RoundUp(clng(sBase),-4)-1
sLower = application.RoundDown(clng(sbase),-4)

after doing the above in the immediate window (for demo purposes), then
testing the results shows

? sbase, sUpper, sLower
30256 39999 30000

so your next step would be something like

thisworkbook.SaveAs "G:\server1\orders\" & sLower & "-" & sUpper & "\" & _
sbase & ".xls"