• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Save pdf name from 3 different cell locations and not document name

JohnAB

New Member
Help please,
I currently have the following macro which works great but need it to save the .pdf name = to 3 different cell location, this is the only way I can see it will save with so I can easily identify.
What do I need to do to the below to correct this?

>>> use code - tags <<<
Code:
Sub SaveSend()
'
' SaveSend Macro
'

'
    ThisWorkbook.Worksheets("Sheet1").Unprotect ("q")
    Range("A1:L37").Select
    ActiveWindow.SmallScroll Down:=-32
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\johna\OneDrive - Coastal Computer Medics\Tool Box talk # 1 Bullying .pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
    Range("C8:D8,G8:L8").Select
    Range("G8").Activate
    ActiveWindow.SmallScroll Down:=16
    Range( _
        "C8:D8,G8:L8,B24:L29,B31:C31,D31:F31,G31:H31,I31:L31,B33:C33,D33:F33,G33:H33,I33:L33,B35:C35,D35:F35,G35:H35,I35:L35" _
        ).Select
    Range("I35").Activate
    ActiveWindow.SmallScroll Down:=4
    Range( _
        "C8:D8,G8:L8,B24:L29,B31:C31,D31:F31,G31:H31,I31:L31,B33:C33,D33:F33,G33:H33,I33:L33,B35:C35,D35:F35,G35:H35,I35:L35,B37:C37,D37:F37,G37:H37,I37:L37" _
        ).Select
    Range("I37").Activate
    ActiveWindow.SmallScroll Down:=-36
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-44
    Range("C8:D8").Select
  
    ThisWorkbook.Worksheets("Sheet1").Protect ("q")
  
End Sub
 

Attachments

Last edited by a moderator:

vletm

Excel Ninja
JohnAB
Your What do I need to do to the below to correct this?
You have given one Filename with ActiveSheet.ExportAsFixedFormat.
After that ... You're selecting, scrolling and clearing contents of sheet.
Do You have somewhere expected output - to show, what do You really would like to do?
 

Belleke

Well-Known Member
This wil do the job.
The code adds the name of the Supervisor (cell C8) and the contractor (cell G8) and a timestamp in the name of the PDF
Change to your needs.
Make sure the path is correct.
Code:
Sub SaveSend()
Sheets("Tool Box # 1 Bullying").Unprotect ("q")
c00 = "C:\Users\johna\OneDrive - Coastal Computer Medics\"
With Sheets("Tool Box # 1 Bullying")
    .ExportAsFixedFormat xlTypePDF, c00 & .Range("C8") & " " & .Range("G8") & " " & "  (" & Format(Now, "dd-mm-yyyy hh.mm") & ")" & ".pdf", , , , , , True
End With
Sheets("Tool Box # 1 Bullying").Protect ("q")
End Sub
 

JohnAB

New Member
JohnAB
Your What do I need to do to the below to correct this?
You have given one Filename with ActiveSheet.ExportAsFixedFormat.
After that ... You're selecting, scrolling and clearing contents of sheet.
Do You have somewhere expected output - to show, what do You really would like to do?
I just did a Record Macro to get this code, hence all the scrolling.
As I said I need the file name, when it saves, to be equal to 3 separate locations B6:L:6 & G8:L:8 & J10:L10. At present it saves as the
This wil do the job.
The code adds the name of the Supervisor (cell C8) and the contractor (cell G8) and a timestamp in the name of the PDF
Change to your needs.
Make sure the path is correct.
Code:
Sub SaveSend()
Sheets("Tool Box # 1 Bullying").Unprotect ("q")
c00 = "C:\Users\johna\OneDrive - Coastal Computer Medics\"
With Sheets("Tool Box # 1 Bullying")
    .ExportAsFixedFormat xlTypePDF, c00 & .Range("C8") & " " & .Range("G8") & " " & "  (" & Format(Now, "dd-mm-yyyy hh.mm") & ")" & ".pdf", , , , , , True
End With
Sheets("Tool Box # 1 Bullying").Protect ("q")
End Sub
Perfect works just changed a couple of things but working fine, thanks heaps.
document name. I want to make each one easily identifiable, Hope I am making sense.
 
Top