Looking for advice / recommendations

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,380
Office Version
2007
Platform
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,039
Office Version
2016
Platform
Windows
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
3,380
Office Version
2007
Platform
Windows
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,846
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
3,380
Office Version
2007
Platform
Windows
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,846
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
3,380
Office Version
2007
Platform
Windows
I have tried that but a message shows there is an error in the code
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,380
Office Version
2007
Platform
Windows
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
3,380
Office Version
2007
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,602
Messages
5,487,806
Members
407,610
Latest member
bellakim00

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top