Looking for advice / recommendations

[ipbr21054]

Hi,
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

 

[footoo]

[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.

 

[ipbr21054]

Morning,
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
        Range("A5:E41").ClearContents
        Range("A3").Select
        ActiveWorkbook.Save
    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

 

[Jaafar Tribak]

You couls use this

Range("A5:E41").SpecialCells(xlCellTypeConstants).ClearContents

 

[ipbr21054]

Hi,
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.

Thanks

 

[Jaafar Tribak]

you could enter a formula in Cell C3

=TEXT(TODAY(),"MMMM") & "-" & YEAR(TODAY())

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

 

[ipbr21054]

I have tried that but a message shows there is an error in the code

 

[Jaafar Tribak]

I have tried that but a message shows there is an error in the code

Which error and where in the code ?

 

[ipbr21054]

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.

 

[ipbr21054]

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