Printing to file without being prompted and automatially set

[me12345]

I am a beginner in writing macros and will greatly appreciate assistance in the following:

I am Printing To File and have the name of the file to be printed contained in the Active Sheet at cell B47 in text format, every time I print, I am prompted to enter the file name, how do I set it to automatically apply
the name contained in cell B47

I also print different areas of the Active Sheet when calling the print Sub.
How do I place the Range to be printed which is specified by the content of cell B48 in text format and located in the Active Sheet, and place it into the ActiveSheet.PageSetup.PrintArea =
(i.e. Cell B48 contains A10:Y80 in text format I could also place into B48 the text $A$10:$Y$80)

Thanks in advane to anyone who could assist.

Sub PrintSA()
'
ActiveSheet.PageSetup.PrintArea = "$A$10:$Y$80"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

 

[me12345]

Correction..... The code did work but did prompt for a file name to be entered

 

[pfarmer]

Cell B49 contains the full filename (minus the .PDF extension which is added by the Virtual PDF Printer) and it also includs the path.

I tried formating cell B49 as Text and as General and used the Range as .Text and .Value with no luck

I also tried recording a macro and using the following copy and paste procedure which did not help either: For copy I placed the cursor on cell B49 and pressed F2 then SHIFT + HOME then CTRL C and when prompted to enter a file name in the Save As table I placed it with CTRL + V

The code generated did not work either when I run it !!!

Thanks again

Does the full path and filename have any spaces?

Perry

 

[me12345]

Yes there are spaces in the path

Here is an actual copy

C:\Documents and Settings\Isaac Z\My Documents\Form-Visitor

The virtual pdf printer adds the extension .pdf to Form-Visitor to get the file: Form-Visitor.pdf

Thanks your efforts

 

[pfarmer]

Yes there are spaces in the path

Here is an actual copy

C:\Documents and Settings\Isaac Z\My Documents\Form-Visitor

The virtual pdf printer adds the extension .pdf to Form-Visitor to get the file: Form-Visitor.pdf

Thanks your efforts

Note, that I haven't tried this, but you may have to resort to some tricks in getting the full path with spaces, I have had to before for a different problem.

You may try a simple test directory such as c:/test and see what occurs with that.

Perry

 

[me12345]

Since the default file name is the workbook file name, is there a way to trick it by temporarily changing the file name?

I found the command ActiveWorkbook.SaveAs Filename:="C:\Path\filename"
This will actually change the default Save As file name but I need to use the new file name for printing just one Sheet and this command saves the entire workbook under the new file name. Any suggestions?

Thanks

 

[pfarmer]

Since the default file name is the workbook file name, is there a way to trick it by temporarily changing the file name?

I found the command ActiveWorkbook.SaveAs Filename:="C:\Path\filename"
This will actually change the default Save As file name but I need to use the new file name for printing just one Sheet and this command saves the entire workbook under the new file name. Any suggestions?

Thanks

I have seen some which copys a single sheet to a new workbook and then saves.

Perry