Save spreadsheet/PDF to location noted in another open workbook

[rachel06]

Hi!

I've seen lots of threads about how you can save a workbook to a location noted in a specific cell. However, I'm trying to accomplish this when that cell is in another open workbook.

My macro copies a sheet called "AP35" to it's own workbook, and the save as dialog box pops up. As convenient as that is, it would be amazing if I could just get it to reference a cell in the original workbook.

I cannot have the file location in the "AP35" sheet as those are aggregated, and anything additional would cause that to bomb.

Now that I'm done rambling: how could I tell the new book that the "AP35" sheet is moved to to print from:
Cell: B39
Workbook: Manual AP35 Template
Worksheet: Cover

As that macro is running, that workbook will be open and the user is not able to close it. Tried doing some recording to get this, but I wasn't sure how to piece things together.

Secondly, I have a print to pdf in the macro as well which brings up the "save as" dialog, but is there anyway to get that to automatically save to the value in B38? This one is in the same worksheet so not as complicated at all. It's literally this simple code right now:

Sub printtopdf()

    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
        
        Call CreateAP35M2M
    
End Sub

I'm sure it's easy to just add where to save in there but I'm not figuring it out.

Thanks so much!!

 

[rachel06]

Bumping this up because I solved the first part of it.

I came up with this for printing the PDF to file and saving it based on a cell value (which contains the entire file path and name) but I feel like it's slower than when I had it as the above with the save as dialog just popping up. Is there anyway to combine the two and have it run quicker? Not like the below is slow but the faster the better!

And yes, I know having the save as dialog box is a pretty good option and I'm trying to make this so people can be as lazy as possible, but the big thing with having it automatically save as with a name using a cell value is everyone's file names will be saved with the same format because cell B38 is formula based.

Sub printtopdf()

    Dim loc As String
loc = Range("B38")
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=loc

Thank you!!

 

[Herakles]

Bumping this up because I solved the first part of it.

I came up with this for printing the PDF to file and saving it based on a cell value (which contains the entire file path and name) but I feel like it's slower than when I had it as the above with the save as dialog just popping up. Is there anyway to combine the two and have it run quicker? Not like the below is slow but the faster the better!

And yes, I know having the save as dialog box is a pretty good option and I'm trying to make this so people can be as lazy as possible, but the big thing with having it automatically save as with a name using a cell value is everyone's file names will be saved with the same format because cell B38 is formula based.

Sub printtopdf()

    Dim loc As String
loc = Range("B38")
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=loc

Thank you!!

This may be faster but probably not much in it.

Maybe a good idea to reference the worksheet and cell.

Could you have the formula which evaluates to the path and the filename in code?

Sub printtopdf()

    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Worksheets("???").Range("B38")

End Sub

 

[jolivanes]

So if you formulate your wish in a concise to the point manner, what would it be.
It sounds like that it will be a simple suggestion but you sure have confused me, which of course can be entirely my shortcoming.

 

[jolivanes]

None of these are probably what you're after but let us know which one needs changing.

Sub Just_An_Example_1()
    Sheets("Sheet1").PrintOut , , , , , True, , Workbooks("Book2.xlsm").Sheets("Sheet1").Range("A1").Value & "\" & "New Book.PDF"
End Sub

Book2.xlsm is the other workbook that is open and has the path, path only without backslash, where the pdf file should be saved into in Sheet1 Cell A1

Sub Just_An_Example_2()
    Sheets("Sheet1").PrintOut , , , , , True, , Workbooks("Book2.xlsm").Sheets("Sheet1").Range("A2").Value
End Sub

Book2.xlsm is the other workbook that is open and has the path and file name incl extension (.pdf) for the pdf file to be created in Sheet1 Cell A2

Sub Just_An_Example_3()
Dim wb1 As Workbook, wb2 As Workbook
Set wb1 = ThisWorkbook
Set wb2 = Workbooks("The Other Workbook's Name.xlsm")    '<---- Change required
wb1.Sheets("Sheet1").Copy , Sheets(Sheets.Count)
    With ActiveSheet
        .Name = .Cells(39, 2).Value    '<---- Name for the pasted sheet in cell B39
        .PrintOut , , , , , True, , wb2.Sheets("Sheet1").Range("A3").Value    '<---- Change required
    End With
End Sub

wb2 is the other workbook that is open and has the path and file name incl extension (.pdf) for the pdf file to be created in Sheet1 Cell A3

 

[rachel06]

None of these are probably what you're after but let us know which one needs changing.

Sub Just_An_Example_1()
    Sheets("Sheet1").PrintOut , , , , , True, , Workbooks("Book2.xlsm").Sheets("Sheet1").Range("A1").Value & "\" & "New Book.PDF"
End Sub

Book2.xlsm is the other workbook that is open and has the path, path only without backslash, where the pdf file should be saved into in Sheet1 Cell A1

Sub Just_An_Example_2()
    Sheets("Sheet1").PrintOut , , , , , True, , Workbooks("Book2.xlsm").Sheets("Sheet1").Range("A2").Value
End Sub

Book2.xlsm is the other workbook that is open and has the path and file name incl extension (.pdf) for the pdf file to be created in Sheet1 Cell A2

Sub Just_An_Example_3()
Dim wb1 As Workbook, wb2 As Workbook
Set wb1 = ThisWorkbook
Set wb2 = Workbooks("The Other Workbook's Name.xlsm")    '<---- Change required
wb1.Sheets("Sheet1").Copy , Sheets(Sheets.Count)
    With ActiveSheet
        .Name = .Cells(39, 2).Value    '<---- Name for the pasted sheet in cell B39
        .PrintOut , , , , , True, , wb2.Sheets("Sheet1").Range("A3").Value    '<---- Change required
    End With
End Sub

wb2 is the other workbook that is open and has the path and file name incl extension (.pdf) for the pdf file to be created in Sheet1 Cell A3

Appreciate this response!

For this part, the path is in the same sheet in cell B38. I just need it to print to PDF and save to that location There is a print area already set on that sheet.

 

[jolivanes]

same sheet meaning the sheet to be saved as PDF I assume.
Change references as required.

Sheets("Sheet1").PrintOut , , , , , True, , Sheets("Sheet1").Range("B38").Value & "\" & "New Book.PDF"]