Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a copied workbook - 2 questions
I have a workbook that has data for entire month. Date, Sales, Credits,
Debits, Expenses, etc. At the end of the month when I make a copy of this workbook and paste it to a new workbook the Date in the first row changed to the previous day (last day of the previous month). All the other dates also change to match. I end up with dates from Dec 31 to Jan 30th instead of Jan 1 - Jan 31st. The other data remains correct and doesn't shift. Here is the line of code that does the copy: Workbooks("CheckUp.xls").Worksheets("CURRENT MONTH").Copy Then I have vba open the SAVE AS dialog so the user can give it the name of the previous month and save it. Is there a way to automate naming the copied workbook with the name of the previous month - ""Jan 07" (just month and year) for example? fileSaveName = Application.GetSaveAsFilename( fileFilter:="Excel Files (*.xls), *.xls") If fileSaveName < False Then Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=fileSaveName Application.DisplayAlerts = True Thanks for any help given. I cetrainly appreciate it. Jon |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a copied workbook - 2 questions
Check out the 1904 Date System option under Tools options Calculation
It sounds like the option is checked in one workbook and not in the other one. That will actually change the date by 4 years and 1 day. If you just have the format to show MM/DD you would not notice the year being off. I'll have to think about the other part of the question. David "jonco" wrote: I have a workbook that has data for entire month. Date, Sales, Credits, Debits, Expenses, etc. At the end of the month when I make a copy of this workbook and paste it to a new workbook the Date in the first row changed to the previous day (last day of the previous month). All the other dates also change to match. I end up with dates from Dec 31 to Jan 30th instead of Jan 1 - Jan 31st. The other data remains correct and doesn't shift. Here is the line of code that does the copy: Workbooks("CheckUp.xls").Worksheets("CURRENT MONTH").Copy Then I have vba open the SAVE AS dialog so the user can give it the name of the previous month and save it. Is there a way to automate naming the copied workbook with the name of the previous month - ""Jan 07" (just month and year) for example? fileSaveName = Application.GetSaveAsFilename( fileFilter:="Excel Files (*.xls), *.xls") If fileSaveName < False Then Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=fileSaveName Application.DisplayAlerts = True Thanks for any help given. I cetrainly appreciate it. Jon |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a copied workbook - 2 questions
Workbooks("CheckUp.xls").SaveAs "Jan 07"
'to delete old-named file Kill "C:\path\CheckUp.xls.xls" Hth, Merjet |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a copied workbook - 2 questions
Correction. Last line should have only one ".xls"
Hth, Merjet |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a copied workbook - 2 questions
The month changes each month.
It won't always be Jan 07. Next month it'll be Feb 07. It needs to get the date from a cell..... Cell A4 for instance. Thanks' Jon "merjet" wrote in message oups.com... Workbooks("CheckUp.xls").SaveAs "Jan 07" 'to delete old-named file Kill "C:\path\CheckUp.xls.xls" Hth, Merjet |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a copied workbook - 2 questions
Workbooks("CheckUp.xls").SaveAs Format(Range("A4"), "mmm yy") & ".xls"
-- David jonco wrote The month changes each month. It won't always be Jan 07. Next month it'll be Feb 07. It needs to get the date from a cell..... Cell A4 for instance. Thanks' Jon "merjet" wrote in message oups.com... Workbooks("CheckUp.xls").SaveAs "Jan 07" 'to delete old-named file Kill "C:\path\CheckUp.xls.xls" Hth, Merjet |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a copied workbook - 2 questions
Thanks for the answers guys. That looks like it will answer my questions.
I DO appreciate the assistance. "David" wrote in message ... Workbooks("CheckUp.xls").SaveAs Format(Range("A4"), "mmm yy") & ".xls" -- David jonco wrote The month changes each month. It won't always be Jan 07. Next month it'll be Feb 07. It needs to get the date from a cell..... Cell A4 for instance. Thanks' Jon "merjet" wrote in message oups.com... Workbooks("CheckUp.xls").SaveAs "Jan 07" 'to delete old-named file Kill "C:\path\CheckUp.xls.xls" Hth, Merjet |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Answers to questions posing more questions in a workbook | Excel Worksheet Functions | |||
2 Questions, Saving a file from a specific cell value, and Searchi | Excel Discussion (Misc queries) | |||
Saving Posts Questions & Answers In My Computer | Excel Discussion (Misc queries) | |||
Saving copied info while running a Selection Change Macro | Excel Programming | |||
Why my cell format is not copied when I copied workbook? | Excel Programming |