vba to make file save in different locations

[jondavis1987]

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?

 

[BQardi]

Try this:

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

 

[jondavis1987]

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

 

[jondavis1987]

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

 

[BQardi]

ADVERTISEMENT

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...):

LocationName = Range("[COLOR=#ff0000]'Ag base'![/COLOR]B4").Value

Although I would prefer using:

LocationName = Worksheets("Ag base").Range("B4").Value

 

[jondavis1987]

It had the same result. The active sheet is pretty much always Ag base. I think I've accessed the other sheets maybe twice

 

[BQardi]

ADVERTISEMENT

I'm a little stumped here...
What happens if you put in a MsgBox after LocationName, and then run the code?:

fName = Range("'Ag base'!C22").Value
LocationName = Range("B4").Value
[COLOR=#ff0000]MsgBox LocationName[/COLOR]
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

 

[jondavis1987]

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

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

 

[BQardi]

Glad you got it working...