Hi, I've looked for this but can't find it exactly.

I need a macro to :
copy 7 specific sheets from one workbook (not the whole book) to a new book
ask the user for a name
save the new book as that name

Thanks in advance
--
Linda M

Worksheets(Array("Sheet1", "Sheet2","Sheet3",
"Sheet4","Sheet5","Sheet6","Sheet7")).Copy
ans = inputbox("Name the new workbook"
activeworkbook.SaveAs Filename:= "C:\myDir\" & ans & ".xls"

--
HTH

Bob Phillips

"LindaM" wrote in message
...
Hi, I've looked for this but can't find it exactly.

I need a macro to :
copy 7 specific sheets from one workbook (not the whole book) to a new
book
ask the user for a name
save the new book as that name

Thanks in advance
--
Linda M

Thank you very much, Bob. Just what I wanted
--
Linda M


"Bob Phillips" wrote:


Worksheets(Array("Sheet1", "Sheet2","Sheet3",
"Sheet4","Sheet5","Sheet6","Sheet7")).Copy
ans = inputbox("Name the new workbook"
activeworkbook.SaveAs Filename:= "C:\myDir\" & ans & ".xls"

--
HTH

Bob Phillips

"LindaM" wrote in message
...
Hi, I've looked for this but can't find it exactly.

I need a macro to :
copy 7 specific sheets from one workbook (not the whole book) to a new
book
ask the user for a name
save the new book as that name

Thanks in advance
--
Linda M

Bob:

Is there a Isgrouped property, or the like that you could
"Group the sheets (using either shift or control keys) to Copy?
My google search didn't turn up anything.

TIA,
Jim


"Bob Phillips" wrote in message
...

Worksheets(Array("Sheet1", "Sheet2","Sheet3",
"Sheet4","Sheet5","Sheet6","Sheet7")).Copy
ans = inputbox("Name the new workbook"
activeworkbook.SaveAs Filename:= "C:\myDir\" & ans & ".xls"

--
HTH

Bob Phillips

"LindaM" wrote in message
...
Hi, I've looked for this but can't find it exactly.

I need a macro to :
copy 7 specific sheets from one workbook (not the whole book) to a new
book
ask the user for a name
save the new book as that name

Thanks in advance
--
Linda M

"Bob Phillips" wrote in message
...

Worksheets(Array("Sheet1", "Sheet2","Sheet3",
"Sheet4","Sheet5","Sheet6","Sheet7")).Copy
ans = inputbox("Name the new workbook"
activeworkbook.SaveAs Filename:= "C:\myDir\" & ans & ".xls"

--
HTH

Bob Phillips

"LindaM" wrote in message
...
Hi, I've looked for this but can't find it exactly.

I need a macro to :
copy 7 specific sheets from one workbook (not the whole book) to a new
book
ask the user for a name
save the new book as that name

Thanks in advance
--
Linda M



Thanks!!
Had resorted to repeatedly calling a sub-subroutine to process a subgroup of
sheets until encountering your much more elegant, logical, and faster "With
Worksheets(array(...))...End With" solution.