Expenses code saves Income sheet ??

[ipbr21054]

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

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.

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

 

[ipbr21054]

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

 

[shg]

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

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

 

[ipbr21054]

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

 

[ipbr21054]

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.

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,

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.

 

[shg]

    [COLOR="#FF0000"]' change sheet name as required ...[/COLOR]
    Worksheets("[COLOR="#FF0000"]Expense Sheet[/COLOR]").ExportAsFixedFormat _
        Type:=xlTypePDF, _

 

[ipbr21054]

Sorry i missed that one

Cheers