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

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

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

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

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