Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option of Print Preview or Save As
Hello,
I have the following code in my spreadsheet that does a "Find last row" and then gives me a print preview. The missing link is how can I get it to "Save as" after the preview? The preview is used to make sure the "Findlastrow" worked and for formatting. It can be either a choice to to to a message box that asks do you want to saveas or do you want to print preview? Obviously the selection would be operators choice..... Or can I put a save as dialog box into the print preview so that once it's reviewed I can save it? Sub findlastrow() Dim lastrow As Long Dim r As Range lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row Set r = ActiveSheet.Range("A1:P" & lastrow + 1) r.PrintPreview End Sub Thanks BOB |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option of Print Preview or Save As
ans = mMsgBox "Save file now?", vbYesNo
If ans = vbYes Then Activworkbook.SaveAs filename:="c:\myTest\myFile.xls" End If -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Bob Reynolds" wrote in message .. . Hello, I have the following code in my spreadsheet that does a "Find last row" and then gives me a print preview. The missing link is how can I get it to "Save as" after the preview? The preview is used to make sure the "Findlastrow" worked and for formatting. It can be either a choice to to to a message box that asks do you want to saveas or do you want to print preview? Obviously the selection would be operators choice..... Or can I put a save as dialog box into the print preview so that once it's reviewed I can save it? Sub findlastrow() Dim lastrow As Long Dim r As Range lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row Set r = ActiveSheet.Range("A1:P" & lastrow + 1) r.PrintPreview End Sub Thanks BOB |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option of Print Preview or Save As
Thanks Bob,
What about if I want a msg box when yes is selected asking for the filename? Also is there a way to save just the worksheet instead of the workbook. I'm looking at file size here where the ws is a lot smaller than the wb. Thanks BOB "Bob Phillips" wrote in message ... ans = mMsgBox "Save file now?", vbYesNo If ans = vbYes Then Activworkbook.SaveAs filename:="c:\myTest\myFile.xls" End If -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Bob Reynolds" wrote in message .. . Hello, I have the following code in my spreadsheet that does a "Find last row" and then gives me a print preview. The missing link is how can I get it to "Save as" after the preview? The preview is used to make sure the "Findlastrow" worked and for formatting. It can be either a choice to to to a message box that asks do you want to saveas or do you want to print preview? Obviously the selection would be operators choice..... Or can I put a save as dialog box into the print preview so that once it's reviewed I can save it? Sub findlastrow() Dim lastrow As Long Dim r As Range lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row Set r = ActiveSheet.Range("A1:P" & lastrow + 1) r.PrintPreview End Sub Thanks BOB |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option of Print Preview or Save As
Ok, since I'm not as familiar as I thought, I still need and answer to the
issue of the ws but I tried to incorporate the code you sent me into the macro and I don't understand where it goes, therefore I get an error. Since I'm a novice can you explain a little more?? Thanks again BOB "Bob Reynolds" wrote in message ... Thanks Bob, What about if I want a msg box when yes is selected asking for the filename? Also is there a way to save just the worksheet instead of the workbook. I'm looking at file size here where the ws is a lot smaller than the wb. Thanks BOB "Bob Phillips" wrote in message ... ans = mMsgBox "Save file now?", vbYesNo If ans = vbYes Then Activworkbook.SaveAs filename:="c:\myTest\myFile.xls" End If -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Bob Reynolds" wrote in message .. . Hello, I have the following code in my spreadsheet that does a "Find last row" and then gives me a print preview. The missing link is how can I get it to "Save as" after the preview? The preview is used to make sure the "Findlastrow" worked and for formatting. It can be either a choice to to to a message box that asks do you want to saveas or do you want to print preview? Obviously the selection would be operators choice..... Or can I put a save as dialog box into the print preview so that once it's reviewed I can save it? Sub findlastrow() Dim lastrow As Long Dim r As Range lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row Set r = ActiveSheet.Range("A1:P" & lastrow + 1) r.PrintPreview End Sub Thanks BOB |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option of Print Preview or Save As
Bob,
I wouldn't use a msgbox, I would use GetSaveAsFilename. Here is an example Sub findlastrow() Dim lastrow As Long Dim r As Range lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row Set r = ActiveSheet.Range("A1:P" & lastrow + 1) r.PrintPreview ans = MsgBox("Save file now?", vbYesNo) If ans = vbYes Then fileSaveName = Application.GetSaveAsFilename( _ fileFilter:="Microsoft Excle Files Files (*.xls), *.xls") If fileSaveName < False Then Activworkbook.SaveAs Filename:=fileSaveName End If End If End Sub As to saving a sheet against a workbook, no you can't do taht - directly. What you can do is to copy that sheet to a new single sheet workbook, like Sub findlastrow() Dim lastrow As Long Dim r As Range ActiveSheet.Copy lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row Set r = ActiveSheet.Range("A1:P" & lastrow + 1) r.PrintPreview ans = MsgBox("Save file now?", vbYesNo) If ans = vbYes Then fileSaveName = Application.GetSaveAsFilename( _ fileFilter:="Microsoft Excle Files Files (*.xls), *.xls") If fileSaveName < False Then Activworkbook.SaveAs Filename:=fileSaveName End If End If End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Bob Reynolds" wrote in message ... Ok, since I'm not as familiar as I thought, I still need and answer to the issue of the ws but I tried to incorporate the code you sent me into the macro and I don't understand where it goes, therefore I get an error. Since I'm a novice can you explain a little more?? Thanks again BOB "Bob Reynolds" wrote in message ... Thanks Bob, What about if I want a msg box when yes is selected asking for the filename? Also is there a way to save just the worksheet instead of the workbook. I'm looking at file size here where the ws is a lot smaller than the wb. Thanks BOB "Bob Phillips" wrote in message ... ans = mMsgBox "Save file now?", vbYesNo If ans = vbYes Then Activworkbook.SaveAs filename:="c:\myTest\myFile.xls" End If -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Bob Reynolds" wrote in message .. . Hello, I have the following code in my spreadsheet that does a "Find last row" and then gives me a print preview. The missing link is how can I get it to "Save as" after the preview? The preview is used to make sure the "Findlastrow" worked and for formatting. It can be either a choice to to to a message box that asks do you want to saveas or do you want to print preview? Obviously the selection would be operators choice..... Or can I put a save as dialog box into the print preview so that once it's reviewed I can save it? Sub findlastrow() Dim lastrow As Long Dim r As Range lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row Set r = ActiveSheet.Range("A1:P" & lastrow + 1) r.PrintPreview End Sub Thanks BOB |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option of Print Preview or Save As
Thanks for the help, it's greatly appreciated.
I don't think I've explained my workbook totally, the findlastrow() looks for the last entry and then stops the find at the last entry. There can be up to 208 entries, totaling 8 pages, so this shortens the printing to whatever data is there. So when the data is printed to print preview, it is the truncated form and might only be 1 of 8 pages or whatever. Once we get the print preview I need to have this truncated form to be the one to save. How about instead of print preview, after the findlastrow, we make it copy all the information that would go to the preview and make a new sheet. The print preview isn't absolutely necessary. Is this possible? Thanks again BOB "Bob Phillips" wrote in message ... Bob, I wouldn't use a msgbox, I would use GetSaveAsFilename. Here is an example Sub findlastrow() Dim lastrow As Long Dim r As Range lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row Set r = ActiveSheet.Range("A1:P" & lastrow + 1) r.PrintPreview ans = MsgBox("Save file now?", vbYesNo) If ans = vbYes Then fileSaveName = Application.GetSaveAsFilename( _ fileFilter:="Microsoft Excle Files Files (*.xls), *.xls") If fileSaveName < False Then Activworkbook.SaveAs Filename:=fileSaveName End If End If End Sub As to saving a sheet against a workbook, no you can't do taht - directly. What you can do is to copy that sheet to a new single sheet workbook, like Sub findlastrow() Dim lastrow As Long Dim r As Range ActiveSheet.Copy lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row Set r = ActiveSheet.Range("A1:P" & lastrow + 1) r.PrintPreview ans = MsgBox("Save file now?", vbYesNo) If ans = vbYes Then fileSaveName = Application.GetSaveAsFilename( _ fileFilter:="Microsoft Excle Files Files (*.xls), *.xls") If fileSaveName < False Then Activworkbook.SaveAs Filename:=fileSaveName End If End If End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Bob Reynolds" wrote in message ... Ok, since I'm not as familiar as I thought, I still need and answer to the issue of the ws but I tried to incorporate the code you sent me into the macro and I don't understand where it goes, therefore I get an error. Since I'm a novice can you explain a little more?? Thanks again BOB "Bob Reynolds" wrote in message ... Thanks Bob, What about if I want a msg box when yes is selected asking for the filename? Also is there a way to save just the worksheet instead of the workbook. I'm looking at file size here where the ws is a lot smaller than the wb. Thanks BOB "Bob Phillips" wrote in message ... ans = mMsgBox "Save file now?", vbYesNo If ans = vbYes Then Activworkbook.SaveAs filename:="c:\myTest\myFile.xls" End If -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Bob Reynolds" wrote in message .. . Hello, I have the following code in my spreadsheet that does a "Find last row" and then gives me a print preview. The missing link is how can I get it to "Save as" after the preview? The preview is used to make sure the "Findlastrow" worked and for formatting. It can be either a choice to to to a message box that asks do you want to saveas or do you want to print preview? Obviously the selection would be operators choice..... Or can I put a save as dialog box into the print preview so that once it's reviewed I can save it? Sub findlastrow() Dim lastrow As Long Dim r As Range lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row Set r = ActiveSheet.Range("A1:P" & lastrow + 1) r.PrintPreview End Sub Thanks BOB |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option of Print Preview or Save As
After running up till selecting the filesaveas it works good. Once I've
selected the name to sava as and hit save I then get this error run timne error 424: object required. Any ideas BOB "Bob Phillips" wrote in message ... Bob, I wouldn't use a msgbox, I would use GetSaveAsFilename. Here is an example Sub findlastrow() Dim lastrow As Long Dim r As Range lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row Set r = ActiveSheet.Range("A1:P" & lastrow + 1) r.PrintPreview ans = MsgBox("Save file now?", vbYesNo) If ans = vbYes Then fileSaveName = Application.GetSaveAsFilename( _ fileFilter:="Microsoft Excle Files Files (*.xls), *.xls") If fileSaveName < False Then Activworkbook.SaveAs Filename:=fileSaveName End If End If End Sub As to saving a sheet against a workbook, no you can't do taht - directly. What you can do is to copy that sheet to a new single sheet workbook, like Sub findlastrow() Dim lastrow As Long Dim r As Range ActiveSheet.Copy lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row Set r = ActiveSheet.Range("A1:P" & lastrow + 1) r.PrintPreview ans = MsgBox("Save file now?", vbYesNo) If ans = vbYes Then fileSaveName = Application.GetSaveAsFilename( _ fileFilter:="Microsoft Excle Files Files (*.xls), *.xls") If fileSaveName < False Then Activworkbook.SaveAs Filename:=fileSaveName End If End If End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Bob Reynolds" wrote in message ... Ok, since I'm not as familiar as I thought, I still need and answer to the issue of the ws but I tried to incorporate the code you sent me into the macro and I don't understand where it goes, therefore I get an error. Since I'm a novice can you explain a little more?? Thanks again BOB "Bob Reynolds" wrote in message ... Thanks Bob, What about if I want a msg box when yes is selected asking for the filename? Also is there a way to save just the worksheet instead of the workbook. I'm looking at file size here where the ws is a lot smaller than the wb. Thanks BOB "Bob Phillips" wrote in message ... ans = mMsgBox "Save file now?", vbYesNo If ans = vbYes Then Activworkbook.SaveAs filename:="c:\myTest\myFile.xls" End If -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Bob Reynolds" wrote in message .. . Hello, I have the following code in my spreadsheet that does a "Find last row" and then gives me a print preview. The missing link is how can I get it to "Save as" after the preview? The preview is used to make sure the "Findlastrow" worked and for formatting. It can be either a choice to to to a message box that asks do you want to saveas or do you want to print preview? Obviously the selection would be operators choice..... Or can I put a save as dialog box into the print preview so that once it's reviewed I can save it? Sub findlastrow() Dim lastrow As Long Dim r As Range lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row Set r = ActiveSheet.Range("A1:P" & lastrow + 1) r.PrintPreview End Sub Thanks BOB |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option of Print Preview or Save As
"Bob Reynolds" wrote in message . ..
After running up till selecting the filesaveas it works good. Once I've selected the name to sava as and hit save I then get this error run timne error 424: object required. Any ideas Bob, put the "e" back-into "Activ_" |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option of Print Preview or Save As
Thanks Mike, that took care of the save. However I need it to save the file
as it went to printpreview. When I've entered data I use the "find last row" to determine the last row data was entered into and the print preview allows me to print it whether it is one or eight pages. I need to actually save the worksheet looking just like the page printed. with the routine state above, it does a print preview and then I have to close it and then the save box comes up.. it save the entire worksheet, all eight pages of it. Any suggestions, not that my spelling was fixed..... Thanks BOB "MikeR" wrote in message om... Bob, put the "e" back-into "Activ_" |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option of Print Preview or Save As
"Bob Reynolds" wrote in message ...
Thanks Mike, that took care of the save. However I need it to save the file as it went to printpreview. When I've entered data I use the "find last row" to determine the last row data was entered into and the print preview allows me to print it whether it is one or eight pages. I need to actually save the worksheet looking just like the page printed. with the routine state above, it does a print preview and then I have to close it and then the save box comes up.. it save the entire worksheet, all eight pages of it. Any suggestions, not that my spelling was fixed..... Thanks BOB Bob, as I understand Excel... when you save you save "it all" you can't save a sheet only...theres no point. PrintPreview will show you whats on a worksheet regardless of determining the last line. You can play with the "Print_Area"... and this can be manipulated with VBA. This will limit what you see at PrintPreview. But all this has no effect on file sizes etc. when saving. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option of Print Preview or Save As
Thanks,
How about something to make a copy of what result the search comes up with and copying it? then have the copy pasted into a blank worksheet. I saw that once but can't find it anywhere now.. don't know enough about vba to get anywhere near it... Thanks, I appreciate the time you took...... BOB "MikeR" wrote in message om... "Bob Reynolds" wrote in message ... Thanks Mike, that took care of the save. However I need it to save the file as it went to printpreview. When I've entered data I use the "find last row" to determine the last row data was entered into and the print preview allows me to print it whether it is one or eight pages. I need to actually save the worksheet looking just like the page printed. with the routine state above, it does a print preview and then I have to close it and then the save box comes up.. it save the entire worksheet, all eight pages of it. Any suggestions, not that my spelling was fixed..... Thanks BOB Bob, as I understand Excel... when you save you save "it all" you can't save a sheet only...theres no point. PrintPreview will show you whats on a worksheet regardless of determining the last line. You can play with the "Print_Area"... and this can be manipulated with VBA. This will limit what you see at PrintPreview. But all this has no effect on file sizes etc. when saving. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save as PDF not same size as Print and Print Preview in Excel | Excel Discussion (Misc queries) | |||
Why is the "zoom" option grayed out in "print preview?" | Excel Discussion (Misc queries) | |||
Print and Print Preview Graphic Moving Resizing 2007/2003 | Excel Discussion (Misc queries) | |||
print preview v page break preview | Excel Discussion (Misc queries) | |||
cell borders that I create dont show on print preview or print | Excel Discussion (Misc queries) |