I have an Excel spreadsheet that contains several tabs which will each be
saved off to the same folder but with different names. The file names will
follow the same naming convention (i.e. YYYY-MM Name of compay - location).

I would like to have the code create the file name for me. This files will
always be created the 2nd day after the month end. I need the date portion
of the file name to be the 4-digit year and the previous 2 digit month. I
would like a message box to be displayed prior to saving each file that will
display the proposed file name. If the name is not correct, I would like the
message box to allow you to change the file name before saving. Is there any
to do this?

--
Hlewis

Hi Hlewis

You can use this in the file name for thw month

MsgBox Format(Month(Now) - 1, "mm")


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hlewis" wrote in message ...
I have an Excel spreadsheet that contains several tabs which will each be
saved off to the same folder but with different names. The file names will
follow the same naming convention (i.e. YYYY-MM Name of compay - location).

I would like to have the code create the file name for me. This files will
always be created the 2nd day after the month end. I need the date portion
of the file name to be the 4-digit year and the previous 2 digit month. I
would like a message box to be displayed prior to saving each file that will
display the proposed file name. If the name is not correct, I would like the
message box to allow you to change the file name before saving. Is there any
to do this?

--
Hlewis

Okay. That helps me a little bit. I am going to include the code that I
have so far. I don't know if this will help explain what I am trying to do.
I'm pretty new to VBA so I'm not sure in what order things have to happen or
the correct commands to use. I will practice it though so I won't have to
keep bugging you guys. :-)

Sub MoveCopy()
'
' MoveCopy Macro


'This macro will save the individual sheets into their respective workbooks
and save them to the Obso Need Files
Dim stDate As Date
stDate = Now()



'Co 755 and 764 Southwestern
Sheets(Array("Co 755 Exploded", "Co 764 Exploded")).Select
Sheets("Co 755 Exploded").Activate
Sheets(Array("Co 755 Exploded", "Co 764 Exploded")).Copy
ActiveWorkbook.SaveAs Filename:= _
"T:\Period End Schedules\Inventory Account Schedules\Inventory
Obsolescence\Obso Need Files\2006-02 Obsolescence Files - Heaverneck.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Windows("2006-02 Obsolescence Files.xls").Activate
--
Hlewis


"Ron de Bruin" wrote:

Hi Hlewis

You can use this in the file name for thw month

MsgBox Format(Month(Now) - 1, "mm")


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hlewis" wrote in message ...
I have an Excel spreadsheet that contains several tabs which will each be
saved off to the same folder but with different names. The file names will
follow the same naming convention (i.e. YYYY-MM Name of compay - location).

I would like to have the code create the file name for me. This files will
always be created the 2nd day after the month end. I need the date portion
of the file name to be the 4-digit year and the previous 2 digit month. I
would like a message box to be displayed prior to saving each file that will
display the proposed file name. If the name is not correct, I would like the
message box to allow you to change the file name before saving. Is there any
to do this?

--
Hlewis

OK

Try this

Dim str As String
str = "T:\Period End Schedules\Inventory Account Schedules\Inventory Obsolescence\Obso Need Files\" _
& Format(DateSerial(Year(Date), Month(Date) - 1, Day(Date)), "yyyy-mm") & " Obsolescence Files - Heaverneck.xls"
MsgBox str

You can use this now

ActiveWorkbook.SaveAs str

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hlewis" wrote in message ...
Okay. That helps me a little bit. I am going to include the code that I
have so far. I don't know if this will help explain what I am trying to do.
I'm pretty new to VBA so I'm not sure in what order things have to happen or
the correct commands to use. I will practice it though so I won't have to
keep bugging you guys. :-)

Sub MoveCopy()
'
' MoveCopy Macro


'This macro will save the individual sheets into their respective workbooks
and save them to the Obso Need Files
Dim stDate As Date
stDate = Now()



'Co 755 and 764 Southwestern
Sheets(Array("Co 755 Exploded", "Co 764 Exploded")).Select
Sheets("Co 755 Exploded").Activate
Sheets(Array("Co 755 Exploded", "Co 764 Exploded")).Copy
ActiveWorkbook.SaveAs Filename:= _
"T:\Period End Schedules\Inventory Account Schedules\Inventory
Obsolescence\Obso Need Files\2006-02 Obsolescence Files - Heaverneck.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Windows("2006-02 Obsolescence Files.xls").Activate
--
Hlewis


"Ron de Bruin" wrote:

Hi Hlewis

You can use this in the file name for thw month

MsgBox Format(Month(Now) - 1, "mm")


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hlewis" wrote in message ...
I have an Excel spreadsheet that contains several tabs which will each be
saved off to the same folder but with different names. The file names will
follow the same naming convention (i.e. YYYY-MM Name of compay - location).

I would like to have the code create the file name for me. This files will
always be created the 2nd day after the month end. I need the date portion
of the file name to be the 4-digit year and the previous 2 digit month. I
would like a message box to be displayed prior to saving each file that will
display the proposed file name. If the name is not correct, I would like the
message box to allow you to change the file name before saving. Is there any
to do this?

--
Hlewis