Macro button to re-name tab to a specific cell value and then print / save worksheet as pdf with the same name as the tab

[roddeo]

Hello - I'm not very experienced when it comes to Macro's. I'm hoping someone can help me out.

I have a workbook with around 20 worksheets in it. I'd like to place 3 separate macro buttons on each worksheet as follows...

1. That re-names the tab to the value of a particular cell in the worksheet
2. That saves the worksheet as a pdf of the same name as the tab to a specific network folder

Thanks!


Roddeo

 

[Fishboy]

Hello - I'm not very experienced when it comes to Macro's. I'm hoping someone can help me out.

I have a workbook with around 20 worksheets in it. I'd like to place 3 separate macro buttons on each worksheet as follows...

1. That re-names the tab to the value of a particular cell in the worksheet
2. That saves the worksheet as a pdf of the same name as the tab to a specific network folder

Thanks!


Roddeo

Hi Roddeo, welcome to the boards.

Firstly, you say you want 3 buttons but only list 2 actions.

Secondly, unless you have a specific reason not to, you can have all of this happen in the one macro.

Try out the following in a COPY of your workbook. This code is added to a standard module and applied to a button to be run.

Sub Save()
' Defines variable
Dim sFile As String
' Renames the tab to the value of cell A1
ActiveSheet.Name = ActiveSheet.Range("A1").Value
' Defines sFile as desired filename based on the value of cell A1
sFile = ActiveSheet.Range("A1").Value & ".pdf"
' Saves a copy of the active workbook as sFile name to pre-designated filepath - update this as required but leave the backslash on the end
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "L:\Data\Test Folder\Daily Reports\" & sFile _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
' Displays messagebox confirming file has been saved
MsgBox sFile & " has been exported"
End Sub

As outlined in the macro comments, you will need to change the filepath to your desired save location on your network. You may also need to change both instances of the Range("A1") part to whatever cell you want to check when renaming the tab / PDF

 

[roddeo]

Thanks Fishboy!

Apologies - I mis-typed my original post. I am only looking for two buttons rather than three. The reason I want to have two separate buttons is because I only want to save to pdf once the sheet has been moved out of a draft into a confirmed state. i.e. when the user first creates the worksheet they will want to use the rename tab function every time but it's only on occasion or later that they might use the save as pdf function. I'll have a practice suing the code you have supplied below in a copy of the worksheet as you suggest. I'm guessing I can just split it into two separate modules to separate the functions?

Hi Roddeo, welcome to the boards.

Firstly, you say you want 3 buttons but only list 2 actions.

Secondly, unless you have a specific reason not to, you can have all of this happen in the one macro.

Try out the following in a COPY of your workbook. This code is added to a standard module and applied to a button to be run.

Sub Save()
' Defines variable
Dim sFile As String
' Renames the tab to the value of cell A1
ActiveSheet.Name = ActiveSheet.Range("A1").Value
' Defines sFile as desired filename based on the value of cell A1
sFile = ActiveSheet.Range("A1").Value & ".pdf"
' Saves a copy of the active workbook as sFile name to pre-designated filepath - update this as required but leave the backslash on the end
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "L:\Data\Test Folder\Daily Reports\" & sFile _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
' Displays messagebox confirming file has been saved
MsgBox sFile & " has been exported"
End Sub

As outlined in the macro comments, you will need to change the filepath to your desired save location on your network. You may also need to change both instances of the Range("A1") part to whatever cell you want to check when renaming the tab / PDF

 

[Fishboy]

Thanks Fishboy!

Apologies - I mis-typed my original post. I am only looking for two buttons rather than three. The reason I want to have two separate buttons is because I only want to save to pdf once the sheet has been moved out of a draft into a confirmed state. i.e. when the user first creates the worksheet they will want to use the rename tab function every time but it's only on occasion or later that they might use the save as pdf function. I'll have a practice suing the code you have supplied below in a copy of the worksheet as you suggest. I'm guessing I can just split it into two separate modules to separate the functions?

Ah, that makes perfect sense.

OK sure, this can be broken down into 2 separate functions as follows. The bits in red are the bits you need to amend to suit your data:

Sub Save()
' Defines variable
Dim sFile As String
' Defines sFile as desired filename based on the value of cell A1
sFile = ActiveSheet.Range("A1").Value & ".pdf"
' Saves a copy of the active workbook as  sFile name to pre-designated filepath - update this as required but  leave the backslash on the end
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "L:\Data\Test Folder\Daily Reports\" & sFile _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
' Displays messagebox confirming file has been saved
MsgBox sFile & " has been exported"
End Sub

Sub RenameTab()
' Renames the tab to the value of cell A1
ActiveSheet.Name = ActiveSheet.Range("A1").Value
' Displays messagebox confirming tab has been renamed
MsgBox "Tab has been renamed to " & ActiveSheet.Range("A1").Value
End Sub

 

[roddeo]

Hi Fishboy. Apologies for the delay in my response. Thanks so much for your help - going to try this now. Cheers, Roddeo

 

[roddeo]

Hi Fishboy - apologies. I know it has been months since I was last in touch. I'm testing the code you provided above and running into a couple of problems. The rename tab code works great but the issue seems to be with the save file code. I'm getting the following error message when I try to run that one: "Compile Error. Member already exists in an object module from which this object module derives." Here's the code I'm using:

Sub Save()
' Defines variable
Dim sFile As String
' Defines sFile as desired filename based on the value of cell L6
sFile = ActiveSheet.Range("L6").Value & ".pdf"
' Saves a copy of the active workbook as sFile name to pre-designated filepath - update this as required but leave the backslash on the end
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"/Users/roderickmclean1/Dropbox/CuriousEgg/Finance/Sales & Purchase Invoices\" & sFile _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
' Displays messagebox confirming file has been saved
MsgBox sFile & " has been exported"
End Sub


Sub RenameTab()
' Renames the tab to the value of cell A1
ActiveSheet.Name = ActiveSheet.Range("A1").Value
' Displays messagebox confirming tab has been renamed
MsgBox "Tab has been renamed to " & ActiveSheet.Range("A1").Value
End Sub


The method I used to enter the code is as follows:

1. Opened the Developer tab
2. Opened the Editor
3. Selected "Workbook"
4. Copy/pasted the code then amended as per above
5. Created two macro buttons and assigned a macro to each

Is there something I am doing wrong in terms of the process steps I am following to set up the code or do I need to tweak the code itself?

Really appreciate any additional help you can provide.

Thanks


Roddeo

 

[roddeo]

Hi Fishboy - think I may have figured it out. When I apply the save code to a specific sheet instead of the entire workbook it works great. Just one last small issue I am having relates to the file name. When I copy and paste the folder path I want to save the file to the whole path is included in the file name and the new .pdf file saves to the same folder where the main .xlsm file resides. I'm wondering if this is because I'm on a Mac and if there is a specific way I need to express the folder path. The method I'm using is to navigate to the folder in question in Finder, Get Info and then copy/paste the path that is displayed there. Wondering if there is a different method or syntax I should use to express the filepath? Thanks for any additional help you can provide here. Roddeo

 

[Fishboy]

Hi Fishboy - think I may have figured it out. When I apply the save code to a specific sheet instead of the entire workbook it works great. Just one last small issue I am having relates to the file name. When I copy and paste the folder path I want to save the file to the whole path is included in the file name and the new .pdf file saves to the same folder where the main .xlsm file resides. I'm wondering if this is because I'm on a Mac and if there is a specific way I need to express the folder path. The method I'm using is to navigate to the folder in question in Finder, Get Info and then copy/paste the path that is displayed there. Wondering if there is a different method or syntax I should use to express the filepath? Thanks for any additional help you can provide here. Roddeo

Hi roddeo,

To be honest I am not at all familiar with the use of Macs so I cannot really comment. What I do notice however is that the file path in your code looks wrong. Firstly the file path does not start with a drive letter. Also the last backslash is a forward slash. I think as a result of this Excel / your Mac are not treating this as an actual file path but it considers it all part of the file name. This would mean the file would save in the "default" location which would be wherever the main file is located.

Could that be it maybe?

 

[roddeo]

Hi Fishboy - thanks for the quick and helpful reply. Yes - I see the error with the filepath I have used now - thanks for pointing that out. I'll do some research on my end re. how to map drive and folder locations on the Mac and see if I can figure out the right syntax. The good news is that everything else is working a treat and the save file function is saving the pdf into the same folder as the main excel file which is actually exactly where I want it at this point so everything is working as needed just now. Thanks again for all your help!

 

[Fishboy]

Hi Fishboy - thanks for the quick and helpful reply. Yes - I see the error with the filepath I have used now - thanks for pointing that out. I'll do some research on my end re. how to map drive and folder locations on the Mac and see if I can figure out the right syntax. The good news is that everything else is working a treat and the save file function is saving the pdf into the same folder as the main excel file which is actually exactly where I want it at this point so everything is working as needed just now. Thanks again for all your help!

You're most welcome