save a file using the date and the sheet name, in that order, to diff directory

[Sacharny]

Hello everyone, thank you. Here is my question. I need to save a file using the date and the sheet name, in that order, like this:

ActiveWorkbook.SaveAs Filename:=Format(Now(), "yymmdd") & ActiveSheet.Name, FileFormat _
:=xlText, CreateBackup:=False

This code works fine, except that it saves only to whatever directory you are already in - but I need to change directory.

So I wrote:

ChDir "\\Buffalo\website\AmazonPriceQtyUpdates"
ActiveWorkbook.SaveAs Filename:=Format(Now(), "yymmdd") & ActiveSheet.Name, FileFormat _
:=xlText, CreateBackup:=False

The result is, the directory is not changed - it ignores that instruction and saves in the current directory I am in (My Document!)

If I write the whole saveas filename routine then the instruction to save the date in front of the sheet name does not work anymore and I get a compile error. Like this:
ChDir "\\Buffalo\website\AmazonPriceQtyUpdates"
ActiveWorkbook.SaveAs Filename:= _
"\\Buffalo\website\AmazonPriceQtyUpdates\Format(Now(), "yymmdd") & ActiveSheet.Name", FileFormat:= _
xlText, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Please help - what am I missing?

[Sacharny]

I found the code somewhere else. If anyone is interested:

'First establish that the file name is the date:
Dim MyFile As String

MyFile = Format(Now(), "yymmdd")


'Then use MyFile to start the name of it. This way you can put the entire path in the save instruction, with the date first, without getting errors, and the file gets to be saved in the external server I want to use


'Save file
ChDir "\\Buffalo\website\AmazonPriceQtyUpdates"
ActiveWorkbook.SaveAs Filename:="\\Buffalo\Website\AmazonPriceQtyUpdates\" & MyFile & ActiveSheet.Name, FileFormat _
:=xlText, CreateBackup:=False

[Sacharny]

I found the code somewhere else. If anyone is interested:

'First establish that the file name is the date:
Dim MyFile As String

MyFile = Format(Now(), "yymmdd")


'Then use MyFile to start the name of it. This way you can put the entire path in the save instruction, with the date first, without getting errors, and the file gets to be saved in the external server I want to use


'Save file
ChDir "\\Buffalo\website\AmazonPriceQtyUpdates"
ActiveWorkbook.SaveAs Filename:="\\Buffalo\Website\AmazonPriceQtyUpdates\" & MyFile & ActiveSheet.Name, FileFormat _
:=xlText, CreateBackup:=False