Automatically create a copy every week

[seepra]

Hello everyone,

I have a main worksheet. It contains data in columns A to E. The data changes/(is updated) every day.
What i wish to do is,
I want to create a new worksheet every week, name it by the date,.. This worksheet will contain data only from two columns A and E of the main worksheet. All these new sheets will be stored in another folder.

Its like i have the main data in one sheet, and i wish to auto-generate "weekly status reports" of that data.

Please let me know if this can be done by using a code..

Thank you,
Seepra

 

[Runsk1]

Hi!
Here is small VBA macro what might work for you:

Sub WeeklyReport()
Dim pth As String
[COLOR=#ff0000]''''''''' Change the path below. This will be the path where your weekly reports are saved. '''''''''''''''''''''''''''''''''''''''''''''''''''''[/COLOR]
pth = "[COLOR=#ff0000]C:\Users\Username\Documents\Myexcels\WeeklyReports\[/COLOR]"
Workbooks.Add
With ActiveWorkbook
[COLOR=#ff0000]''''''''' Change the name of "MyDataworkbook.xlsm" below to match your main workbook ''''''''''''''''''''''''''''''''''''''''''''''''''''''''[/COLOR]
.Sheets(1).Cells.Range("[COLOR=#ffa500]A:A[/COLOR]").Value = Workbooks("[COLOR=#ff0000]MyDataworkbook.xlsm[/COLOR]").Sheets(1).Cells.Range("[COLOR=#ffa500]A:A[/COLOR]").Value
.Sheets(1).Cells.Range("[COLOR=#ffa500]E:E[/COLOR]").Value = Workbooks("[COLOR=#ff0000]MyDataworkbook.xlsm[/COLOR]").Sheets(1).Cells.Range("[COLOR=#ffa500]E:E[/COLOR]").Value
.SaveAs (pth & Date & ".xlsx")
.Close
End With
End Sub

Run this from your main workbook. Change the path to match the folder you want to save in. Replace "Mydataworkbook.xlsm" with the name of your main workbook. Then, when you want to save the weekly report, just run the macro.

NOTE: I used 2 whole columns as range because I dont know how much data you might have. It takes a while to run the macro. You might want to change the ranges a bit. (e.g. "A1:A5000" and "E1:E5000")

I hope this is helpful for you!

Matias

 

[seepra]

Thank you Runsk1,

I'll try this one..

I have a doubt..
In .SaveAs (pth & Date & ".xlsx") , the "Date" is for the file name right..? If i run the macro today, my report will be saved by the name "31-7-2015" automatically?

Another doubt,
I have to manually run this macro to save the reports. Is it possible to have a code by which the reports will be automatically generated every week..?
I mean without me doing anything, my reports folder will get filled with reports every week..
May be in the code i could define a 7 day period..or i could provide the dates on which i want these reports generated..

Let me know if something like this is possible..

Thank you so much for the help

 

[Runsk1]

Thank you Runsk1,

I'll try this one..

I have a doubt..
In .SaveAs (pth & Date & ".xlsx") , the "Date" is for the file name right..? If i run the macro today, my report will be saved by the name "31-7-2015" automatically?

Another doubt,
I have to manually run this macro to save the reports. Is it possible to have a code by which the reports will be automatically generated every week..?
I mean without me doing anything, my reports folder will get filled with reports every week..
May be in the code i could define a 7 day period..or i could provide the dates on which i want these reports generated..

Let me know if something like this is possible..

Thank you so much for the help

Date is built-in function that returns todays date. When i ran that macro, my file was saved as "31.7.2015.xlsx" but you get the idea.

One way to automatically run this macro is using Workbook_Open. Let me explain:

Private Sub Workbook_Open()
Run "WeeklyReport"
End Sub








Sub WeeklyReport()
Dim pth As String
Dim LastDate As Date
pth = "C:\Users\Username\Documents\Myexcels\WeeklyReports\"
LastDate = Workbooks("MyDataworkbook.xlsm").Sheets(1).Cells.Range("F1").Value
If Date >= LastDate + 7 Then
Workbooks.Add
With ActiveWorkbook
.Sheets(1).Cells.Range("A:A").Value = Workbooks("MyDataworkbook.xlsm").Sheets(1).Cells.Range("A:A").Value
.Sheets(1).Cells.Range("E:E").Value = Workbooks("MyDataworkbook.xlsm").Sheets(1).Cells.Range("E:E").Value
.SaveAs (pth & Date & ".xlsx")
.Close
End With
Workbooks("MyDataworkbook.xlsm").Sheets(1).Cells.Range("F1").Value = Date
End If
End Sub

Dump this in "Thisworkbook"-module. (you can find it on the left side of the window when you press ALT+F11)

What it does is it calls for "WeeklyReport" when you open your main workbook. In cell F1 (main workbook) keep the date when was the last time you saved the report.
This macro will then check the cell F1 and if it is equal or more than 7 days ago it will create new report. It will also automatically replace the date in F1 with todays date.

Problem is, it will be bit slower to open your main workbook when it is time to update.

Try it out and play with it a bit, changing the old date in F1 and see how it works out.

Oh, just stating the obvious, you have to open your main workbook for this to work. It will not save weekly reports when you haven't opened the workbook.


EDIT:
If it's not working, change the line

Run "WeeklyReport"

with just

WeeklyReport

 

[seepra]

Thanks a lot,
It exactly does what i wanted, the date plus 7 part is perfect, the save to my path part is also perfect.
But i have to run the macro when i open my main workbook..it isn't saving the report on its own when I open the main workbook..
So now I am looking for auto running of macros on given dates..
Will post if i get something..

Thanks a lot for the code,
Seepra

 

[Runsk1]

This part should run it automatically when you open the workbook:

Private Sub Workbook_Open()
Run "WeeklyReport"
End Sub

If it doesnt do that, as I said, change it like this:

Private Sub Workbook_Open()
WeeklyReport
End Sub

Also,It should be in "myworkbook"-module. Are you sure you stored it in the right module?

Edit: "thisworkbook"-module. not myworkbook.

 

[seepra]

Hi,

Yes, it's working perfectly fine now.
You were right..I had not placed the code in thisworkbook module. I did and it works well.

Thank you so so much!

Seepra