Fix macro so it doesn't change sheets

[Oxidised]

Hi,
I have the below macro which works well to produce a pdf of the summary sheet. But I want to put a button on multiple sheets in the same file, so when I change things, I can hit the button where ever I am and the pdf is created. At the moment after the macro runs I end up on the summary sheet, but I want to stay on which ever sheet I am on at the time.

Sub Summary_Save()
'
' Summary_Save Macro
' Saves the summary page as a pdf
'

'
Sheets("Summary").Select
Dim fName As String
With ActiveSheet
    fName = .Range("D1").Value
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "c:\Uncontrolled\" & fName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
End Sub

 

[NARAYANK991]

Hi ,

Do you mean to say that you will have buttons on each of several worksheets , and which ever worksheet you are on , if you click the button on that sheet , that sheet should be printed as PDF ?

If so , just remove the statement :

Sheets("Summary").Select

Of course , you will have to ensure that which ever sheet you are on , cell D1 on that sheet should have a text string which will be used as a file name.

Narayan

 

[Oxidised]

Narayank, I will have buttons on several worksheets, that all link to the same macro. When I click any of these buttons, the specific worksheet "Summary" should be printed as pdf... not the sheet that I am on at the moment.
If I remove that Sheets("Summary").Select line it will print the active worksheet, not the "summary" worksheet... but I'm sure there is a way to tell it to print the Summary sheet without explicity selecting it. It should always reference D1 on the summary sheet.
(Worst case I guess I could put at the end of the macro some statement to return to the last active sheet?? If I did this, I seem to remember there is a command that stops the screen updating when the macro is running?)

 

[Oxidised]

Something like this seems to work

Sub Summary_Save()
'
' Summary_Save Macro
' Saves the summary page as a pdf
'

'
Application.ScreenUpdating = False
Set SheetToGoBackTo = ActiveSheet

Sheets("Summary").Select
Dim fName As String
With ActiveSheet
    fName = .Range("D1").Value
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "\\SERVER-NATBIO\Distribution\LOG\Uncontrolled\" & fName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
SheetToGoBackTo.Activate
Application.ScreenUpdating = True
End Sub

 

[NARAYANK991]

Hi ,

In that case , just precede the following statement :

Sheets("Summary").Select

by the following statement :

save_sheet_name = Activesheet.Name

and at the end of the processing i.e. after the statement :

End With

have the following statement :

Worksheets(save_sheet_name).Activate

Declare the variable save_sheet_name as String at the top of the macro.

Narayan

 

[Oxidised]

marvellous, thanks

 

[rumshar]

Hi,
I have the below macro which works well to produce a pdf of the summary sheet. But I want to put a button on multiple sheets in the same file, so when I change things, I can hit the button where ever I am and the pdf is created. At the moment after the macro runs I end up on the summary sheet, but I want to stay on which ever sheet I am on at the time.

Sub Summary_Save()
'
' Summary_Save Macro
' Saves the summary page as a pdf
'

'
Sheets("Summary").Select
Dim fName As String
With ActiveSheet
    fName = .Range("D1").Value
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "c:\Uncontrolled\" & fName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
End Sub

Hi,
I think you don't have select "Summary" Sheet to print it.
instead , why not use
set MySheet = Sheets("Summary")
FName = MySheet.Range("D1").value
With MySheet
.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"\\SERVER-NATBIO\Distribution\LOG\Uncontrolled\" & fName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
EndWith.
Please do let me know if it works.

With Regards
Rudra

 

[Oxidised]

Please do let me know if it works.

With Regards
Rudra

Hey Rudra, works a treat! That is what I was hoping to do in the first place. Have updated the macro and it is working great. I like how there is always many ways to do things in excel, and the challenge is to work out the most efficient!