vba to make file save in different locations

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
443
Office Version
  1. 2019
Platform
  1. Windows
Code:
Sub SaveActiveWorksheetAsPDF()
'Saves active worksheet as pdf using concatenation
'of A22,B22,F5
 
Dim fName As String
With activeWorksheet
     fName = Range("'Ag base'!C22").Value
     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
             "C:\Users\jdavis\Dropbox\Quality Control\Aggregates\Recycled Concrete\" & fName, Quality:=xlQualityStandard, _
             includeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End With
End Sub


This is the code that I'm currently using to save my file as a pdf when I'm done. Cell B4 in my file has a location's name in it. The location name will be either

Waterloo
Hefner
Stillwater
West
Sunnylane
Norman

For example If it's waterloo I have to move it to from the recycled concrete folder to inside the waterloo folder
C:\Users\jdavis\Dropbox\Quality Control\Aggregates\Recycled Concrete\
C:\Users\jdavis\Dropbox\Quality Control\Aggregates\Recycled Concrete\Waterloo\


I would like it to automatically save to the right folder for all possible outcomes
is there a way to make this happen?
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Try this:
Code:
Sub SaveActiveWorksheetAsPDF()
'Saves active worksheet as pdf using concatenation
'of A22,B22,F5
 
Dim fName As String[COLOR=#ff0000], LocationName As String[/COLOR]
With activeWorksheet
     fName = Range("'Ag base'!C22").Value
     [COLOR=#ff0000]LocationName = Range("B4").Value[/COLOR]
     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
             "C:\Users\jdavis\Dropbox\Quality Control\Aggregates\Recycled Concrete\"[COLOR=#ff0000] & LocationName & "\"[/COLOR] & fName, Quality:=xlQualityStandard, _
             includeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End With
End Sub
 
Upvote 0
It saved it in just the recycled concrete folder instead of the location folder. I think it's missing the "" at the end to complete the file path but I can't seem to find a way to include it
 
Upvote 0
I guess that can't be it, I didn't notice it was already in the code you provided. I still can't get it to work though
 
Upvote 0
Try changing to (You didn't mention on which sheet you had cell B4 with the location names, so I assumed it was the active sheet...):
Code:
LocationName = Range("[COLOR=#ff0000]'Ag base'![/COLOR]B4").Value
Although I would prefer using:
Code:
LocationName = Worksheets("Ag base").Range("B4").Value
 
Upvote 0
It had the same result. The active sheet is pretty much always Ag base. I think I've accessed the other sheets maybe twice
 
Upvote 0
I'm a little stumped here...
What happens if you put in a MsgBox after LocationName, and then run the code?:
Code:
fName = Range("'Ag base'!C22").Value
LocationName = Range("B4").Value
[COLOR=#ff0000]MsgBox LocationName[/COLOR]
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
 
Upvote 0
I'm sorry, you had it. I had two modules entered. One for me and one for another. I accidentally entered your code onto module two and I was trying the shortcut for module 1

Code:
Sub SaveActiveWorksheetAsPDF()
'Saves active worksheet as pdf using concatenation
'of A22,B22,F5
 
Dim fName As String, LocationName As String
With activeWorksheet
     fName = Range("'Ag base'!C22").Value
     LocationName = Range("'Ag base'!B4").Value
     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
             "C:\Users\jdavis\Dropbox\Quality Control\Aggregates\Recycled Concrete\" & LocationName & "\" & fName, Quality:=xlQualityStandard, _
             includeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

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