HI Rob

That did just the trick. I was aware of the file path issue (ie: needs to
exist) but that is fine as I didn't want users to create directories all over
the place. That way I keep the file structure as I want it.

Thank a lot

"broro183" wrote:


hi Chris,

The following may need some fine tuning b/c it hasn't been tested & has
no error checking (eg does path/file actually/already exist?):

Sub SavePayrollForClient ()
'to save template file as "\payroll\COMPANY\YEAR\MONTH\file.xls" &
reopen original file.
dim TemplateFile as string
Dim ClientFullPathName as string
dim StartOfPath as string
dim ClientCompany as string
dim ClientMonth as string
dim ClientYear as string
dim ClientFileName as string

TemplateFile = ActiveWorkbook.FullName
StartOfPath = "?:?\Payroll\"
ClientCompany = worksheets("?").range("a1")
ClientMonth = worksheets("?").range("A2")
ClientYear = worksheets("?").range("A3")
ClientFileName = "?.xls"
ClientFullPathName = StartOfPath & ClientCompany & "\" & ClientYear &
"\" & _
ClientMonth & "\" & ClientFileName

ActiveWorkbook.SaveAs Filename:= _
ClientFullPathName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
_
CreateBackup:=False

Workbooks.Open FileName:= TemplateFile
Workbooks.close FileName:= ClientFullPathName
end sub

nb: Just replace the question marks as needed & look up "save as" in
the VBE help files ([alt + F11] & then F1) if you want to see the other
possible arguments for the save as method.

I have included lines to 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.

I'm off to bed now but will check tomorrow to see if you had any
problems.

Hth
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