Looking for advice / recommendations

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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.
 
Upvote 0
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
 
Upvote 0
You couls use this

Code:
Range("A5:E41").SpecialCells(xlCellTypeConstants).ClearContents
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
I have tried that but a message shows there is an error in the code
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top