Save range as new worksheet

[Art]

I need to save a range in a worksheet as a new worksheet. How can I define a
Saveas to only save the particular range?

 

[FSt1]

hi,
here is a save range sub i have in my personal.xls
you can use it as an example to create your own.
it requires that you select the range you wish to save to another workbook.
Sub mac1SaveRange()

'Macro written by FSt1 4/27/02

Dim cnt As Long
Dim cell As Range

'ActiveSheet.UsedRange.Select
MsgBox "You have selected range" & Selection.Address
If Selection.Cells.Count = 1 Then
If MsgBox("You have selected only one cell. Continue?????", vbYesNo,
"Warning") = vbNo Then
Exit Sub
End If
End If
cnt = 0
For Each cell In Selection
If Not IsEmpty(cell) Then
cnt = cnt + 1
End If
Next
If cnt = 0 Then
If MsgBox("There is no data in the selected range. Continue?!?!?!?!?",
vbYesNo, "Warning") = vbNo Then
Exit Sub
End If
End If
Selection.Copy
Workbooks.Add
Range("A1").PasteSpecial xlPasteAll
Application.Dialogs(xlDialogSaveAs).Show
End Sub

regards
FSt1

 

[Norman Jones]

Hi Art,

Add a new worksheet and then copy
the required range to the new sheet.

If you need to automate this, turn on the
macro recorder and perform the necessary
operations manually. This will provide you
with code which may be edited to afford
more generic application.

If you experience problems in editing the
recorder code, post back with the
problematic code.

 

[Norman Jones]

Hi Art,

I note that, in an adjacent post, FSti
has assumed that your intention is to
create a new workbook wheras my
response was predicated on the
creation of a new worksheet.

If, therefore, you wish to create a
new workbook, utilise the code
provided by FSti.