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

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

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

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

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

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

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