Looking for advice / recommendations


Well-known Member
I have a worksheet that I enter data into each month.
At the end of the month I would like to make a copy of the page or the values entered into it then clear the cells & start over again for the next month.

The required advice is how should I go about creating a copy / saving a copy etc.

I like the ease of the way it works so a button press would then do what’s required it respect of the saved copy.
This saved copies are then printed later
So maybe save a range to a word doc or pdf ?

What do you advise.
Thanks. Thanks to


Well-known Member
[COLOR=#333333]The required advice is how should I go about creating a copy / saving a copy etc. [/COLOR]
Try creating a macro with the macro recorder.


Well-known Member
I am using the code supplied below which does exactly what i require.

Having said that there is one issue with it that i hope somebody can help me with please,
Here is the code.

Private Sub GrassSummarySheet_Click()    Dim strFileName As String
    strFileName = "C:\Users\Ian\Desktop\GRASS CUTTING\\" & Range("C3").Value & ".pdf"
    If Dir(strFileName) <> vbNullString Then
        MsgBox "SUMMARY SHEET " & Range("C3").Value & " WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly
        Exit Sub
    End If
    With ActiveSheet
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True
        MsgBox "SUMMARY SHEET " & Range("C3").Value & " WAS SAVED SUCCESSFULLY", vbInformation + vbOKOnly
    End With
End Sub

I think i have used the incorrect code with regards the code Range("A5:E41").ClearContents
I would like the sheet range AS:E41 cleared so the following month i can start again.
The issue is where the contents have been cleared is that the formula in each cell is now deleted.
can you advise who it should be cleared but to leave the formula still there.

Have a nice day


Well-known Member
As column C,D & E are populated from the contents from a cell in column B then i changed the code to just clear column B
Now columns C,D & E are empty.

Please could you adive how i can populate the cell C3 with the current month & year.
This will then save me each month having to then type out the following month & year as the code will do this for me.


Jaafar Tribak

Well-known Member
you could enter a formula in Cell C3


Bear in mind though, that if you run your code at the beginning of the next month (ie: after the month has already ended) then it will give you erroneous date
Last edited:


Well-known Member
I have tried that but a message shows there is an error in the code


Well-known Member
My mistake,i left a character at the start of the code.
It now works fine.

Can i ask please.
As i have a formatted the cell with border & colour,font size etc will this be overwritten next month ?
I mean is it better to record a macro of the changes i make then make vba code from it so its covered each month.


Well-known Member
I was thinking something like this.
Private Sub Worksheet_Activate()

With Sheets("GRASS INCOME")
    .Range("C3") = Text(TODAY(), "MMMM") & " " & Year(TODAY())
    With .Range("C3")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        With .Font
            .Name = "Calibri"
            .FontStyle = "Bold"
            .Size = 28
        End With
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlInsideVertical).LineStyle = xlContinuous
        .Borders(xlInsideHorizontal).LineStyle = xlContinuous
        With .Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
End Sub
When i open the sheet the code runs but fails at the point Text(TODAY
I think this would be better if i can get it to work.

Ha Ha

Some videos you may like

This Week's Hot Topics