Template
 

Hello

I have a business type spreadsheet which I open up new every week. It is
saved as a template. When I open a new week I would like to import some data
from the previous week i.e. previous week last day sales.

I would also like to add the dates and days for the current week as well.

Is there any way I can add this information without having to set up 12
months in advance

Thanks
Steve

Template
 

write a macro that runs when you create a new sheet, have the macro open
your previous week and get the data.

Example code (to be added to the thisworkbook code):


Option Explicit
Private Sub Workbook_Open()

Dim Filename As String
Dim PreviousWorkbook As Workbook

' Newly created from template? if path is empty then it is new
If ActiveWorkbook.Path = "" Then ' New workbook is being created from
template
' ask for filename to get data from
Filename = Application.GetOpenFilename("Workbook (*.xls), *.xls", ,
"Where to get last weeks data?")
' Open old workbook read-only
PreviousWorkbook = Application.Workbooks.Open(Filename, , True)
' Copy data


' Now here add your code to copy the data from your old workbook to
your new one


' Done copying, so close the old workbook, don't save changes
PreviousWorkbook.Close savechanges:=False
PreviousWorkbook = Nothing
' let user know we're finished
MsgBox "Your new workbook is ready to use!"
Else
' do nothing, existing workbook is being opened
End If

End Sub




"wally" wrote in message
...
Hello

I have a business type spreadsheet which I open up new every week. It is
saved as a template. When I open a new week I would like to import some
data from the previous week i.e. previous week last day sales.

I would also like to add the dates and days for the current week as well.

Is there any way I can add this information without having to set up 12
months in advance

Thanks
Steve

Template
 

write a macro that runs when you create a new sheet, have the macro open
your previous week and get the data.

Example code (to be added to the thisworkbook code):


Option Explicit
Private Sub Workbook_Open()

Dim Filename As String
Dim PreviousWorkbook As Workbook

' Newly created from template? if path is empty then it is new
If ActiveWorkbook.Path = "" Then ' New workbook is being created from
template
' ask for filename to get data from
Filename = Application.GetOpenFilename("Workbook (*.xls), *.xls", ,
"Where to get last weeks data?")
' Open old workbook read-only
PreviousWorkbook = Application.Workbooks.Open(Filename, , True)
' Copy data


' Now here add your code to copy the data from your old workbook to
your new one


' Done copying, so close the old workbook, don't save changes
PreviousWorkbook.Close savechanges:=False
PreviousWorkbook = Nothing
' let user know we're finished
MsgBox "Your new workbook is ready to use!"
Else
' do nothing, existing workbook is being opened
End If

End Sub


"wally" wrote in message
...
Hello

I have a business type spreadsheet which I open up new every week. It is
saved as a template. When I open a new week I would like to import some
data from the previous week i.e. previous week last day sales.

I would also like to add the dates and days for the current week as well.

Is there any way I can add this information without having to set up 12
months in advance

Thanks
Steve