save sheet as pdf to subfolders based on cell value

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
977
Office Version
  1. 2010
hi all
i search code save sheet as pdf to subfolder the directory is "C:\Users\OSE\Downloads\client \" i have main folder calls client and contains many subfolders customer1,customer2,customer3,customer4 when i fill in cell h2 = customer1 then save as pdf to subfolder customer1 and if i fill customer2 in h2 then save as pdf to subfolder customer2 and so on
i appreciate if anybody help
 

jefflab1

New Member
Joined
Jul 9, 2015
Messages
14
Click here to download your file. There was something about your sheet that kept generating an error when the macro tried to save the file as a PDF. I created a new blank sheet and copied/pasted the cells from the READING sheet to the new blank sheet. I then deleted the original READING sheet and renamed the new one as "READING". The macro now seems to work. Click the button.


The button doesn't do anything. When I click it, it doesn't pull up anything. I did move it to that S drive folder, not the Reading data folder.

Here is the code that I copied:

Private Sub CommandButton1_Click()
ChDir "S:\Fire Dispatchers\Circuits Program\Reading_Data\"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Replace(Range("C2"), "/", "-") & "-" & Format(Range("C3"), "hhmm") & ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End Sub
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
I tested the macro and it worked properly. Did you test the file I attached or did you use the macro on a different file?
 

jefflab1

New Member
Joined
Jul 9, 2015
Messages
14
I tested the macro and it worked properly. Did you test the file I attached or did you use the macro on a different file?

I downloaded the file and copy and pasted it to the main folder, same drive.

Does anything in this photo look abnormal?

1601671802675.png
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
It doesn't matter where you save the the file. The PDF will be saved in the folder path that is hard coded in the macro. It should do that when you open the file and click the command button. Try saving the file to your C:\ Drive. Create a test folder in the C:\ Drive. Call the folder "Test" and replace this line of code:
VBA Code:
ChDir "S:\Fire Dispatchers\Circuits Program\Reading_Data\"
with this one:
VBA Code:
ChDir "C:\Test\"
Click the command button and see what happens.
 

jefflab1

New Member
Joined
Jul 9, 2015
Messages
14
I can't thank everyone enough for their help. It finally worked. Their was one issue. The original folder on the S drive (aka network shared drive) needed to be given permission from within the Excel doc to save or alter that location. I had to goto Macro Security and under Trusted Locations, I had to add the location. I pressed OK and that was it!

Now onto figuring out how to add another button to save as PDF and email that PDF.

Thank you again!
 

Watch MrExcel Video

Forum statistics

Threads
1,126,928
Messages
5,621,642
Members
415,849
Latest member
PhoenixRising2015

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
Top