Select Worksheets, Copy to New Workbook
 

I have a worksheet that is used as a processing template and then I want to
copy specified worksheets to a new file for distribution without any macros
attached. However, the code I'm using isn't working despite it being used
successfully by other posters here. Obviously, I'm doing something wrong and
would appreciate a pointer or two.

This works fine:
Sheets("NOTES").Select
Sheets("NOTES").Copy

These produce the error "Run-time error '1004' Select method of Sheets class
failed":
Attempt 1
Dim wkshts As Variant
wkshts = Array("Summary", "Deployment Profiles", "Tranche G", "Tx DSMP", _
"T4 BHFS Deployments", "Tx Non-DSMP", "DSMP Adhoc", "Other Adhoc", _
"Incident Profile", "Outstanding Incidents", "Problem Records",
"Master Incidents", _
"Deployment Cancellations", "Terminology", "DL", "Calculations")
Sheets(wkshts).Select
Sheets(wkshts).Copy

Attempt 2
'Sheets(Array("NOTES", "Summary", "Deployment Profiles", "Tranche G",
"Tx DSMP", _
"T4 BHFS Deployments", "Tx Non-DSMP", "DSMP Adhoc", "Other Adhoc", _
"Incident Profile", "Outstanding Incidents", "Problem Records",
"Master Incidents" _
, "Deployment Cancellations", "Terminology", "DL",
"Calculations")).Select
'Sheets(Array("NOTES", "Summary", "Deployment Profiles", "Tranche G",
"Tx DSMP", _
"T4 BHFS Deployments", "Tx Non-DSMP", "DSMP Adhoc", "Other Adhoc", _
"Incident Profile", "Outstanding Incidents", "Problem Records",
"Master Incidents" _
, "Deployment Cancellations", "Terminology", "DL",
"Calculations")).Copy

I'm guessing Sheets doesn't like Array very much but as the former was
posted a while ago as an accepted solution to a problem and the latter was
what the macro recorder gave me, I'm a little confused and at a loss as to
what to do.

All suggestions gratefully received!

Select Worksheets, Copy to New Workbook
 

myarray=
for each ws in myarray
ws.copy
next

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ladymuck" wrote in message
...
I have a worksheet that is used as a processing template and then I want to
copy specified worksheets to a new file for distribution without any
macros
attached. However, the code I'm using isn't working despite it being used
successfully by other posters here. Obviously, I'm doing something wrong
and
would appreciate a pointer or two.

This works fine:
Sheets("NOTES").Select
Sheets("NOTES").Copy

These produce the error "Run-time error '1004' Select method of Sheets
class
failed":
Attempt 1
Dim wkshts As Variant
wkshts = Array("Summary", "Deployment Profiles", "Tranche G", "Tx
DSMP", _
"T4 BHFS Deployments", "Tx Non-DSMP", "DSMP Adhoc", "Other Adhoc",
_
"Incident Profile", "Outstanding Incidents", "Problem Records",
"Master Incidents", _
"Deployment Cancellations", "Terminology", "DL", "Calculations")
Sheets(wkshts).Select
Sheets(wkshts).Copy

Attempt 2
'Sheets(Array("NOTES", "Summary", "Deployment Profiles", "Tranche G",
"Tx DSMP", _
"T4 BHFS Deployments", "Tx Non-DSMP", "DSMP Adhoc", "Other Adhoc",
_
"Incident Profile", "Outstanding Incidents", "Problem Records",
"Master Incidents" _
, "Deployment Cancellations", "Terminology", "DL",
"Calculations")).Select
'Sheets(Array("NOTES", "Summary", "Deployment Profiles", "Tranche G",
"Tx DSMP", _
"T4 BHFS Deployments", "Tx Non-DSMP", "DSMP Adhoc", "Other Adhoc",
_
"Incident Profile", "Outstanding Incidents", "Problem Records",
"Master Incidents" _
, "Deployment Cancellations", "Terminology", "DL",
"Calculations")).Copy

I'm guessing Sheets doesn't like Array very much but as the former was
posted a while ago as an accepted solution to a problem and the latter was
what the macro recorder gave me, I'm a little confused and at a loss as to
what to do.

All suggestions gratefully received!

Select Worksheets, Copy to New Workbook
 

Dim wkshts As Variant
wkshts = Array("Summary", "Deployment Profiles", _
"Tranche G", "Tx DSMP", "T4 BHFS Deployments", _
"Tx Non-DSMP", "DSMP Adhoc", "Other Adhoc", _
"Incident Profile", "Outstanding Incidents", _
"Problem Records", "Master Incidents", _
"Deployment Cancellations", "Terminology", "DL", "Calculations")

Set newbk = Workbooks.Add
For Each sht In wkshts
With newbk
ThisWorkbook.Sheets(sht).Copy _
Destination:=.Sheets(.Sheets.Count)
End With
Next sht
Do
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.xls), *.xls")
If fileSaveName = False Then
MsgBox ("Cannot Save file")
End If
Loop While fileSaveName = False
nexbk.SaveAs Filename:=fileSaveName


"Don Guillett" wrote:


myarray=
for each ws in myarray
ws.copy
next

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ladymuck" wrote in message
...
I have a worksheet that is used as a processing template and then I want to
copy specified worksheets to a new file for distribution without any
macros
attached. However, the code I'm using isn't working despite it being used
successfully by other posters here. Obviously, I'm doing something wrong
and
would appreciate a pointer or two.

This works fine:
Sheets("NOTES").Select
Sheets("NOTES").Copy

These produce the error "Run-time error '1004' Select method of Sheets
class
failed":
Attempt 1
Dim wkshts As Variant
wkshts = Array("Summary", "Deployment Profiles", "Tranche G", "Tx
DSMP", _
"T4 BHFS Deployments", "Tx Non-DSMP", "DSMP Adhoc", "Other Adhoc",
_
"Incident Profile", "Outstanding Incidents", "Problem Records",
"Master Incidents", _
"Deployment Cancellations", "Terminology", "DL", "Calculations")
Sheets(wkshts).Select
Sheets(wkshts).Copy

Attempt 2
'Sheets(Array("NOTES", "Summary", "Deployment Profiles", "Tranche G",
"Tx DSMP", _
"T4 BHFS Deployments", "Tx Non-DSMP", "DSMP Adhoc", "Other Adhoc",
_
"Incident Profile", "Outstanding Incidents", "Problem Records",
"Master Incidents" _
, "Deployment Cancellations", "Terminology", "DL",
"Calculations")).Select
'Sheets(Array("NOTES", "Summary", "Deployment Profiles", "Tranche G",
"Tx DSMP", _
"T4 BHFS Deployments", "Tx Non-DSMP", "DSMP Adhoc", "Other Adhoc",
_
"Incident Profile", "Outstanding Incidents", "Problem Records",
"Master Incidents" _
, "Deployment Cancellations", "Terminology", "DL",
"Calculations")).Copy

I'm guessing Sheets doesn't like Array very much but as the former was
posted a while ago as an accepted solution to a problem and the latter was
what the macro recorder gave me, I'm a little confused and at a loss as to
what to do.

All suggestions gratefully received!