Expenses code saves Income sheet ??

ipbr21054

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

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Here we go,
The worksheet is called GRASS SUMMARY
This is what you see on the one page
E2.jpg


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.

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

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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