Results 1 to 7 of 7

Thread: Expenses code saves Income sheet ??

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

    Default Expenses code saves Income sheet ??

    Hi,
    I have an Income sheet which prints fine on a button & the code shown below.

    Code:
    Private Sub GrassSummaryIncomeSheet_Click()    Dim strFileName As String
        
        strFileName = "C:\Users\Ian\Desktop\GRASS CUTTING\CURRENT GRASS SHEETS\INCOME 2019-2020\" & Range("A3") & " " & Range("D3") & ".pdf"
        If Dir(strFileName) <> vbNullString Then
            MsgBox "INCOME GRASS SHEET " & Range("A3") & " " & Range("D3") & " WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly, "INCOME SUMMARY GRASS SHEET MESSAGE"
            Exit Sub
        End If
        
        With ActiveSheet
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True
            MsgBox "GRASS SHEET " & Range("A3") & " " & Range("D3") & " WAS SAVED SUCCESSFULLY", vbInformation + vbOKOnly, "INCOME SUMMARY GRASS SHEET MESSAGE"
            Range("A5:B30").ClearContents
            Range("A5").Select
            ActiveWorkbook.Save
        End With
    End Sub
    I then copied the Income code & altered its cell reference etc so its the same but for the Expenses button & the code is shown below.

    Code:
    Private Sub GrassSummaryExpensesSheet_Click()    Dim strFileName As String
        
        strFileName = "C:\Users\Ian\Desktop\GRASS CUTTING\CURRENT GRASS SHEETS\EXPENSES 2019-2020\" & Range("M3") & " " & Range("P3") & ".pdf"
        If Dir(strFileName) <> vbNullString Then
            MsgBox "EXPENSES GRASS SHEET " & Range("M3") & " " & Range("P3") & " WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly, "EXPENSES SUMMARY GRASS SHEET MESSAGE"
            Exit Sub
        End If
        
        With ActiveSheet
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True
            MsgBox "GRASS SHEET " & Range("M3") & " " & Range("P3") & " WAS SAVED SUCCESSFULLY", vbInformation + vbOKOnly, "EXPENSES SUMMARY GRASS SHEET MESSAGE"
            Range("M5:P35").ClearContents
            Range("A5").Select
            ActiveWorkbook.Save
        End With
    End Sub
    The Income code does all things correct without any issues.

    The Expenses code on the other hand does all things correct BUT the sheet thats saved is the Income sheet.
    I have looked through the code a few times and cant see why or where the fault is as to why Income & not the Expenses sheet ?

    Do you see the issue.
    Thanks
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

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

    Default Re: Expenses code saves Income sheet ??

    Just some more info for you.
    The one worksheet once opened has the two templates on the same page,Income on the left & Expenese on the right.
    I removed the code from the Income button,saved & open workbook.
    I pressed the expenses button & see the successfull expenese message,looking good so far.
    I go to the Income folder & not there which is good.

    I then go to the expense folder & there it is,good so far.

    I open up the pdf & i see the Income sheet ???
    As a test i even put a different date & year on the template before i saved it.

    This is fine for the naming of the file but once opened its the Income sheet.

    How am in going to solve this
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  3. #3
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,612
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Expenses code saves Income sheet ??

    You have to tell it what sheet to export if it's not the active sheet.

    Code:
    Private Sub GrassSummaryExpensesSheet_Click()
      Const sPath       As String = "C:\Users\Ian\Desktop\GRASS CUTTING\CURRENT GRASS SHEETS\EXPENSES 2019-2020\"
      Dim sFile         As String
    
      sFile = "EXPENSES GRASS SHEET " & Range("M3").Value & " " & Range("P3").Value & ".pdf"
    
      If Len(Dir(sPath & sFile)) Then
        MsgBox Prompt:=sFile & " WAS NOT SAVED AS IT ALLREADY EXISTS", _
               Buttons:=vbCritical + vbOKOnly, _
               Title:="EXPENSES SUMMARY GRASS SHEET MESSAGE"
    
      Else
        ' change sheet name as required ...
        Worksheets("Expense Sheet").ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=sPath & sFile, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True
        MsgBox Prompt:=sFile & " WAS SAVED SUCCESSFULLY", _
               Buttons:=vbInformation + vbOKOnly, _
               Title:="EXPENSES SUMMARY GRASS SHEET MESSAGE"
        Range("M5:P35").ClearContents
        Range("A5").Select
        ActiveWorkbook.Save
      End If
    End Sub

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

    Default Re: Expenses code saves Income sheet ??

    Hi,
    That gives me the same result.
    The files is saved in the correct folder.
    The file is named correctly
    The problem is the item that i am now viewing.

    As opposed to me reading EXPENSES SHEET JULY 2019 etc etc

    I am reading INCOME SHEET 2019 etc etc

    I will post a photos on next post
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

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

    Default Re: Expenses code saves Income sheet ??

    Here we go,
    The worksheet is called GRASS SUMMARY
    This is what you see on the one page


    Left side is Income & Right side expenses.

    There are also two buttons

    SAVE INCOME SHEET & CLEAR CELLS as shown below.

    Code:
    Private Sub GrassSummaryIncomeSheet_Click()    Dim strFileName As String
        
        strFileName = "C:\Users\Ian\Desktop\GRASS CUTTING\CURRENT GRASS SHEETS\INCOME 2019-2020\" & Range("A3") & " " & Range("D3") & ".pdf"
        If Dir(strFileName) <> vbNullString Then
            MsgBox "INCOME GRASS SHEET " & Range("A3") & " " & Range("D3") & " WAS NOT SAVED AS IT ALREADY EXISTS", vbCritical + vbOKOnly, "INCOME SUMMARY GRASS SHEET MESSAGE"
            Exit Sub
        End If
        
        With ActiveSheet
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True
            MsgBox "GRASS SHEET " & Range("A3") & " " & Range("D3") & " WAS SAVED SUCCESSFULLY", vbInformation + vbOKOnly, "INCOME SUMMARY GRASS SHEET MESSAGE"
            Range("A5:B30").ClearContents
            Range("A5").Select
            ActiveWorkbook.Save
        End With
    
    
    End Sub
    SAVE EXPENESE SHEET & CLEAR CELLS as shown below,

    Code:
    Private Sub GrassSummaryExpensesSheet_Click()
        Dim strFileName As String
       
        strFileName = "C:\Users\Ian\Desktop\GRASS CUTTING\CURRENT GRASS SHEETS\EXPENSES 2019-2020\" & Range("M3") & " " & Range("P3") & ".pdf"
        If Dir(strFileName) <> vbNullString Then
            MsgBox "EXPENSES GRASS SHEET " & Range("M3") & " " & Range("P3") & " WAS NOT SAVED AS IT ALREADY EXISTS", vbCritical + vbOKOnly, "EXPENSES SUMMARY GRASS SHEET MESSAGE"
            Exit Sub
        End If
       
        With ActiveSheet
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True
            MsgBox "GRASS SHEET " & Range("M3") & " " & Range("P3") & " WAS SAVED SUCCESSFULLY", vbInformation + vbOKOnly, "EXPENSES SUMMARY GRASS SHEET MESSAGE"
            Range("M5:P35").ClearContents
            Range("M5").Select
            ActiveWorkbook.Save
        End With
    End Sub
    So i assumed if i pressed the top white button the sheet on the left would be saved as i input the cell range etc.
    This is correct,file is named correct, its saved in the correct folder, when i view it i see what i had typed on the left hand template.

    So now looking at the expenses side.
    I assume if i press the lower white button the sheet on the right would be saved as i input the cell range etc.
    This is partially correct, file name is named correct, its saved in the correct folder, when i view it I SHOULD SEE WHAT I HAD TYPED ON THE RIGHT HAND TEMPLATE BUT I ACTUALLY SEE this.

    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  6. #6
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,612
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Expenses code saves Income sheet ??

    Code:
        ' change sheet name as required ...
        Worksheets("Expense Sheet").ExportAsFixedFormat _
            Type:=xlTypePDF, _

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

    Default Re: Expenses code saves Income sheet ??

    Sorry i missed that one

    Cheers
    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
  •