Save Sheet to new WorkBook.

[Chris Watts]

I am attempting to copy a single sheet from one workbook into a new workbook
and save it - keeping the original unchanged.
When I use the code below it does indeed create a new workbook and copy the
required sheet into it. But the active cell from the original now appears
in the new workbook .contaiing a few lines of vba (not mine!). Also the
original workbook seems to have been renamed. Suggestions welcomed!

TIA
Chris

Sub SaveReport()
Dim strFilename As String

If strActivePieceNo = "" Then Call WhatPieceNumber
strFilename = "Report on Piece No " & strActivePieceNo

Sheets("Consistancy Report").Paste
ActiveWorkbook.SaveAs (strFilename)

End Sub

[Note strActivePieceNumber is a global defined and set elsewhere]

 

[Dave Peterson]

If you're copying the "consistancy report" worksheet to a new workbook, then the
line should be:

Sheets("Consistancy Report").Copy

My guess is that you copied some text (from a newsgroup post) and that was what
was in the clipboard when you pasted.

I am attempting to copy a single sheet from one workbook into a new workbook
and save it - keeping the original unchanged.
When I use the code below it does indeed create a new workbook and copy the
required sheet into it. But the active cell from the original now appears
in the new workbook .contaiing a few lines of vba (not mine!). Also the
original workbook seems to have been renamed. Suggestions welcomed!

TIA
Chris

Sub SaveReport()
Dim strFilename As String

If strActivePieceNo = "" Then Call WhatPieceNumber
strFilename = "Report on Piece No " & strActivePieceNo

Sheets("Consistancy Report").Paste
ActiveWorkbook.SaveAs (strFilename)

End Sub

[Note strActivePieceNumber is a global defined and set elsewhere]