Make excel macro refer to other sheet than itself

[Jyggalag]

Hi all,

I currently have this code, which saves my sheet (entitled "Sheet3") as a PDF:

Option Explicit

Sub SaveFileWithMacro()

Dim Path As String
Dim fn As String
Path = "S:\Path\PDF files\"
fn = Range("A63")
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & fn & ".pdf"
End Sub

The code is attached to a macro button located in Sheet3 as well.

However, I want to collect all my macro's in one sheet, and I would like to move this particular macro to a new sheet, which I will entitle "Macros". Once I do that however, I do not believe that this code will work, seen as it is using the current sheet I am in and the value A63 in "Sheet3".

Does anybody know of a smart way for me to fix this code? Would be highly appreciated

Kind regards,
Jyggalag

 

[6StringJazzer]

There is no particular reason to move macros into one "sheet". (Do you really mean "module"?) If you have code that you want to use to export Sheet3 to a PDF, the best place to put that code is in the module for Sheet3.

Nevertheless, here is what you can do. If this code is in the module Sheet3 (is that the tab name or the codename?), then it will save the active sheet as a PDF file using the name from A63 in Sheet3. Since this is associated with a button in Sheet3, the active sheet will also be Sheet3. If you want this code to always deal with Sheet3, no matter what module it is in, then you will need to do this:

Sub SaveFileWithMacro()

   Dim Path As String
   Dim fn As String
   Path = "S:\Path\PDF files\"
   fn = Worksheets("Sheet3").Range("A63")
   Worksheets("Sheet3").ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & fn & ".pdf"

End Sub

 

[Jyggalag]

There is no particular reason to move macros into one "sheet". (Do you really mean "module"?) If you have code that you want to use to export Sheet3 to a PDF, the best place to put that code is in the module for Sheet3.

Nevertheless, here is what you can do. If this code is in the module Sheet3 (is that the tab name or the codename?), then it will save the active sheet as a PDF file using the name from A63 in Sheet3. Since this is associated with a button in Sheet3, the active sheet will also be Sheet3. If you want this code to always deal with Sheet3, no matter what module it is in, then you will need to do this:

Sub SaveFileWithMacro()

   Dim Path As String
   Dim fn As String
   Path = "S:\Path\PDF files\"
   fn = Worksheets("Sheet3").Range("A63")
   Worksheets("Sheet3").ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & fn & ".pdf"

End Sub

This is amazing! Thank you so much

Have a splended day sir