Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to create a Weekly Workbook
I have a Master Workbook which I want to be able to create copies of as a
Weekly Workbook based on the initial date in cell G7 of the index page which is the start-up page. Then having the copies automatically renamed as the following weeks date if this is possible does anyone have such a code |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to create a Weekly Workbook
Try the below..
Sub Macro1() Dim dtTemp As Date dtTemp = Range("G7") ActiveSheet.Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = Format(DateAdd("d", 7, dtTemp), "dd-mm-yyyy") Range("G7") = dtTemp + 7 End Sub If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: I have a Master Workbook which I want to be able to create copies of as a Weekly Workbook based on the initial date in cell G7 of the index page which is the start-up page. Then having the copies automatically renamed as the following weeks date if this is possible does anyone have such a code |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to create a Weekly Workbook
You almost got it right, The macro you provided copied the index sheet with
the updated date and place it at the end of the active workbook. I'm sorry that is not what I meant. What I want Im finding it difficult to explain so please bear with me. If I simply copy a Workbook while it is unopened I would get a copy with the same name with the word €œcopy€ added. This is the type of copy macro that I actually want that it would copy the whole workbook, change the names to a date and save it to the same file where the Master Workbook is. "Jacob Skaria" wrote: Try the below.. Sub Macro1() Dim dtTemp As Date dtTemp = Range("G7") ActiveSheet.Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = Format(DateAdd("d", 7, dtTemp), "dd-mm-yyyy") Range("G7") = dtTemp + 7 End Sub If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: I have a Master Workbook which I want to be able to create copies of as a Weekly Workbook based on the initial date in cell G7 of the index page which is the start-up page. Then having the copies automatically renamed as the following weeks date if this is possible does anyone have such a code |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to create a Weekly Workbook
The below macro will create a copy OR save the current workbook in the same
location with the name as date in G7 + 7 days... Sub Macro() Application.DisplayAlerts = False Application.ScreenUpdating = False Dim wbBook As Workbook Dim dtTemp As Date Dim strFile As String strFile = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name dtTemp = Range("G7") Range("G7") = dtTemp + 7 ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\" & _ Format(DateAdd("d", 7, dtTemp), "dd-mm-yyyy") Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: You almost got it right, The macro you provided copied the index sheet with the updated date and place it at the end of the active workbook. I'm sorry that is not what I meant. What I want Im finding it difficult to explain so please bear with me. If I simply copy a Workbook while it is unopened I would get a copy with the same name with the word €œcopy€ added. This is the type of copy macro that I actually want that it would copy the whole workbook, change the names to a date and save it to the same file where the Master Workbook is. "Jacob Skaria" wrote: Try the below.. Sub Macro1() Dim dtTemp As Date dtTemp = Range("G7") ActiveSheet.Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = Format(DateAdd("d", 7, dtTemp), "dd-mm-yyyy") Range("G7") = dtTemp + 7 End Sub If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: I have a Master Workbook which I want to be able to create copies of as a Weekly Workbook based on the initial date in cell G7 of the index page which is the start-up page. Then having the copies automatically renamed as the following weeks date if this is possible does anyone have such a code |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to create a Weekly Workbook
You Beauty thats just Marvellous Thank you so much
"Jacob Skaria" wrote: The below macro will create a copy OR save the current workbook in the same location with the name as date in G7 + 7 days... Sub Macro() Application.DisplayAlerts = False Application.ScreenUpdating = False Dim wbBook As Workbook Dim dtTemp As Date Dim strFile As String strFile = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name dtTemp = Range("G7") Range("G7") = dtTemp + 7 ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\" & _ Format(DateAdd("d", 7, dtTemp), "dd-mm-yyyy") Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: You almost got it right, The macro you provided copied the index sheet with the updated date and place it at the end of the active workbook. I'm sorry that is not what I meant. What I want Im finding it difficult to explain so please bear with me. If I simply copy a Workbook while it is unopened I would get a copy with the same name with the word €œcopy€ added. This is the type of copy macro that I actually want that it would copy the whole workbook, change the names to a date and save it to the same file where the Master Workbook is. "Jacob Skaria" wrote: Try the below.. Sub Macro1() Dim dtTemp As Date dtTemp = Range("G7") ActiveSheet.Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = Format(DateAdd("d", 7, dtTemp), "dd-mm-yyyy") Range("G7") = dtTemp + 7 End Sub If this post helps click Yes --------------- Jacob Skaria "aussiegirlone" wrote: I have a Master Workbook which I want to be able to create copies of as a Weekly Workbook based on the initial date in cell G7 of the index page which is the start-up page. Then having the copies automatically renamed as the following weeks date if this is possible does anyone have such a code |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro (I Think) To Update List in Workbook With Weekly Updates? | Excel Discussion (Misc queries) | |||
Create Move Macro for Closed Workbook | Excel Discussion (Misc queries) | |||
create personal macro workbook | Excel Worksheet Functions | |||
Macro to create new workbook and sheets | Excel Discussion (Misc queries) | |||
Using a macro to create a macro in another workbook | Excel Worksheet Functions |