Hi Chris

I know you are happy with what you have but I just saw the syntax for
how to prevent the need of having to...
Chris Wrote:

... reopen the Template file & close the Client's
file at the end of the macro. If they are not needed just delete
them.
There may be a tidier way of doing this but I don't know it.
[/color]

in a recent post by Ron DeBruin
(http://excelforum.com/showthread.php?t=518959) & thought you may like
it to tidy your code up.

With a little adaptation I have incorporated it into your code as
follows:

Sub RevisedSavePayrollForClient()
'to save template file as "\payroll\COMPANY\YEAR\MONTH\file.xls".
Dim TemplateFileFullPath As String 'renamed
Dim TemplateFile As Workbook 'new/redefined variable
Dim ClientFullPathName As String
Dim StartOfPath As String
Dim ClientCompany As String
Dim ClientMonth As String
Dim ClientYear As String
Dim ClientFileName As String

TemplateFileFullPath = ActiveWorkbook.FullName 'renamed
Set TemplateFile = ActiveWorkbook 'new/redefined variable
StartOfPath = "?:\?\"
ClientCompany = Worksheets("?").Range("a1")
ClientMonth = Worksheets("?").Range("A2")
ClientYear = Worksheets("?").Range("A3")
ClientFileName = "?.xls"
ClientFullPathName = StartOfPath & ClientCompany & "\" & ClientYear &
"\" & _
ClientMonth & "\" & ClientFileName

TemplateFile.SaveCopyAs ClientFullPathName 'new line
End Sub

The key is the second last line. This prevents the need for saving the
ClientFullPathName, reopening the Template & then closing the
ClientFullPathName b/c it is all done in the single line
"TemplateFile.SaveCopyAs ClientFullPathName". The new line keeps the
template file open (with no changes being saved to it) & saves the
Client's file in the chosen directory.

Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=522105