Looking for advice / recommendations

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,609
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

Well-known Member
Joined
Sep 21, 2016
Messages
2,619
Code:
[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

Well-known Member
Joined
Nov 16, 2010
Messages
2,609
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.

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

Well-known Member
Joined
Dec 5, 2002
Messages
7,420
Office Version
2016
Platform
Windows
You couls use this

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

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,609
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

Well-known Member
Joined
Dec 5, 2002
Messages
7,420
Office Version
2016
Platform
Windows
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
 
Last edited:

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,609
I have tried that but a message shows there is an error in the code
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,609
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

Well-known Member
Joined
Nov 16, 2010
Messages
2,609
I was thinking something like this.
Code:
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
 

Forum statistics

Threads
1,078,148
Messages
5,338,522
Members
399,240
Latest member
mominul2241

Some videos you may like

This Week's Hot Topics

Top