Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: Looking for advice / recommendations

  1. #1
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Looking for advice / recommendations

    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
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  2. #2
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Looking for advice / recommendations

    Code:
    The required advice is how should I go about creating a copy / saving a copy etc. 
    Try creating a macro with the macro recorder.

  3. #3
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looking for advice / recommendations

    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
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  4. #4
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,351
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Looking for advice / recommendations

    You couls use this

    Code:
    Range("A5:E41").SpecialCells(xlCellTypeConstants).ClearContents
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  5. #5
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looking for advice / recommendations

    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
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  6. #6
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,351
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Looking for advice / recommendations

    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 by Jaafar Tribak; Jul 14th, 2019 at 07:30 AM.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  7. #7
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looking for advice / recommendations

    I have tried that but a message shows there is an error in the code
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  8. #8
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,351
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Looking for advice / recommendations

    Quote Originally Posted by ipbr21054 View Post
    I have tried that but a message shows there is an error in the code
    Which error and where in the code ?
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  9. #9
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looking for advice / recommendations

    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.
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  10. #10
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looking for advice / recommendations

    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
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •