Hi, Without seeing your code, I suggest that the following may work: In the existing Macro sheet create a variable with the filename based on the invoice, and use it as the 'SaveAs' filename, then Pass this variable to a VBA subroutine, in a fixed location such as Personal.xls This is my Macro code: strFN="C:\Temp\Teach.xls" =CALL(PERSONAL.XLS!Module1.Ttest(strFN)) (The filename 'Teach.xls' would be replaced by your Invoice-based filename) The Ttest subroutine in PERSONAL.XLS!Module1.Ttest receives the new filename, modifies it and then calls the subroutine in the Invoice file. Sub TTest(strM As String) strNewName = "'" & Right(strM, 9) & "'!Ttest2" Run strNewName End Sub strNewName looked like this 'Teach.xls'!Ttest2, enclosed in double quotes. My manipulation of the filename with RIGHT() is crude and can be improved, but in any case will require a change to match your invoiced-based filenames. There may be a way to do this by changing the CALL() in the Macro sheet, but I couldn't get it to work - it is many years since I used the Macrosheet macros. So in summary a two step process. Pass the invoice based filename to a fixed location VBA subroutine. The VBA subroutine does the manipulation on the filename and calls the subroutine in the changing invoice-based workbook. HTH Regards

0

Thanks Humar. I don't really understand when you say: "In the existing Macro sheet create a variable with the filename based on the invoice, and use it as the 'SaveAs' filename, then Pass this variable to a VBA subroutine, in a fixed location such as Personal.xls" Currently, I pick up teh template invoice file names INVSALE.XLS Run a series of macros to pick up teh next Invoice number, populate it with products, quantities, print a packing sheet and write to a log file. This file is then saved as a new file using the invoice number. So INVSALE.XLS beccomes 10058.XLS. Same for teh next invoice, and so on 10059.XLS, 10060.XLS .... In the second stage, and this is where the VBA code is called, I pick up the file 10059.XLS add in posting details and postage costs, use a series of macros to write to other log files, and print final invoice copies. Part of thsi process calls a VB code from teh Macro sheet. (We sorted this problem now and teh Macro CALLS teh VB rather than RUNs. However, the macro directs teh call to a VB that references the template file INVSALE.XLS. I have not understood the first part of your instructions how I will overcome this constantly changing location reference. I have not got past this point to be able to say anything about the following stages you gave me... Sorry amy be I am being a bit thick here. T.

0

Hi, Sorry for not being clear. OK, so you have said that you do this: So INVSALE.XLS beccomes 10058.XLS. Same for the next invoice, and so on 10059.XLS, 10060.XLS. This means that in your macros you create new filenames such as 10058.XLS. Now add a new macro step which assigns this new name to a variable, for example strFN= I don't know how you create these new file names, but if you can create them, then you can assign them to a variable (strFN) with an = , strFN = <the next file name> Then when you call your Visual Basic Macro include the variable strFN between the brackets in the call command. =CALL(PERSONAL.XLS!Module1.Ttest(strFN)) Perhaps posting the code you use to create the new filenames would help. Regards

0

Thanks. This means that in your macros you create new filenames such as 10058.XLS. This is done manually. After the template (INVSALE.XLS) with new invoice number and details have been populated, I just do a manual "save as" and save the file under the new file name which is the invoice number. I suppose I can try and adapt it so that this is also done automatically as part of a MACRO or VB code. I will leave that for later. I tend to do improvements in small manageable chunks and as the need arise. This whole appliaction has been growing and getting bit more sophisticated over many years; hence the changeover from MACRO sheets to VB codes, but still retaining the old macros. T.

0

Hi, I didn't realize that you were doing the renaming manually - hence the confusion. How about an Input dialog box to get the new name. In Visual basic in INVOICE.xls in Module1 I have the following: Sub SaveNew() Dim strFN As String Dim strCodeName As String 'get new filename to save Invoice file as strFN = InputBox("Enter new filename - no need to include .xls", "Invoice Filenames") If strFN <> "" Then If Right(strFN, 3) <> "xls" Then strFN = strFN & ".xls" End If 'save the invoice file with the new name Workbooks("Invoice.xls").SaveAs strFN 'run code in renamed Invoice workbook strCodeName = "'" & strFN & "'!Ttest2" Run strCodeName End If End Sub Sub Ttest2() MsgBox ActiveWorkbook.Name End Sub This allows you to enter the new filename, and then it saves the INVOICE.xls with the new name and then calls a Macro in the new workbook. As an example the macro Ttest2 just puts a message on the screen with its own name. Regards

0