Prev: Why are my Add-In formulas recalculating everytime I open a workbo
Next: Locking / Unlocking Cell with out Protection/ Unprotection of Shee
From: Ladymuck on 18 Jul 2008 08:46
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!

From: Don Guillett on 18 Jul 2008 08:59

myarray=
for each ws in myarray
ws.copy
next

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1(a)austin.rr.com
"Ladymuck" <Ladymuck(a)discussions.microsoft.com> wrote in message
news:1F6D510D-6B18-42EE-B3E9-77214A0AC974(a)microsoft.com...
>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!
>

From: Joel on 18 Jul 2008 10:30
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
> dguillett1(a)austin.rr.com
> "Ladymuck" <Ladymuck(a)discussions.microsoft.com> wrote in message
> news:1F6D510D-6B18-42EE-B3E9-77214A0AC974(a)microsoft.com...
> >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!
> >
>
>
 | 
Pages: 1
Prev: Why are my Add-In formulas recalculating everytime I open a workbo
Next: Locking / Unlocking Cell with out Protection/ Unprotection of Shee