Thread: Auto-generate new weekly workbook based on a saved template

Hi,

I would really grateful if someone could help with the issue I am having with Excel 07.

I have created and saved a template in excel 07 that contains macros for sorting data, and vlookups for analysing the data. This data is added daily.

The template has 5 tabs for data for each day of the week, and a sixth tab for analysing that weekly data.

Is there a bit of code that could be added to the template that auto-generates a new workbook based on the above each week, with all the macros etc in template, minus the data.

If someone could advise how that is possible it would be much appreciated.

thanks,

James

James,

Welcome to the forum.

What you're requesting can be accomplished, the code would just need to know what fields need clearing, where to save the new workbook (if different from the current workbook's folder) and what to name the new workbook. The code would look something like this:

Sub CreateNew()
    
    'Change these ranges as necessary
    Dim WeekdayFieldsToClear As String:     WeekdayFieldsToClear = "A2:D10, F12:G15, J5, J8, I2:K5"
    Dim AnalysisFieldsToClear As String:    AnalysisFieldsToClear = "J10, J12, I2:I12"
    
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        'Change the workday sheet names as necessary
        If ws.Name = "Monday" _
        Or ws.Name = "Tuesday" _
        Or ws.Name = "Wednesday" _
        Or ws.Name = "Thursday" _
        Or ws.Name = "Friday" Then
        
            ws.Range(WeekdayFieldsToClear).ClearContents
            
        'Change the analysis sheet name as necessary
        ElseIf ws.Name = "Analysis" Then
        
            ws.Range(AnalysisFieldsToClear).ClearContents
            
        End If
    Next ws
    
    'Currently the file will be saved in the same folder as the active workbook
    'Change the FilePath if necessary. Make sure to include the ending \
    Dim FilePath As String: FilePath = ActiveWorkbook.Path & "\"
    
    'This prompts the user to input the name of the new workbook
    Dim NewName As String:  NewName = vbNullString
    While NewName = vbNullString
        NewName = InputBox(Title:="New Filename", _
                           Prompt:="Type a name for the new workbook to be saved as.")
        
    Wend
    
    'Saves the now blank template as the new filename in the specified path, as a .xls file
    ActiveWorkbook.SaveAs Filename:=FilePath & NewName & ".xls", _
                          FileFormat:=xlExcel8
    
End Sub

Hope that helps,
~tigeravatar

an easier way to erase you old data would be to name your data range for each sheets and perform a ClearContents for each of those ranges.
You can manage to create a dynamic range name that expand with the data you input.

that would replace the first part of the macro suggested by Tiger.

The rest of the macro Tiger wrote is still valid.
Pierre

Range(Monday_data).ClearContents
Range(Tuesday_data).ClearContents
Range(Wednesday_data).ClearContents
Range(Thursday_data).ClearContents
Range(Friday_data).ClearContents
Range(Analysis_data).ClearContents

an even simpler solution is to keep a BLANK workbook in a directory and open it each time you need to start a new week. Don't forget to change the name of the file the first time you save it. You could protect the BLANK workbook so you wont mess with it or make it read only. This will force you to save it with a new name.
Pierre

Guys,

thanks so much for your help, I wasnt expecting such a speedy response, much appreciated.

James