copy method failed

[SteveDB1]

hi all.
I've modifed an existing macro to copy renamed worksheets in to a secondary
workbook.
I've read the KB file from MS that Jim Thomlinson has posted regarding this
issue, and if I understand it correctly the problem mainly occurs when the
user seeks to copy a worksheet in to the same workbook.
My goal is to copy the worksheet in to another, existing workbook.

wks.Copy after:=Workbooks("TROABook-200-299.xlsx").Sheets.count

the response I get from VBA is a 1004 error, stating that the copy method
failed.

My intention is to copy the renamed worksheets into a different workbook
than they are originally in, and at the end.
What am I doing wrong here...?

Thank you.
Your helps are appreciated.
SteveB.

 

[RyanH]

Replace that line with this.

wks.Copy After:=Sheets(Workbooks("TROABook-200-299.xlsx").Sheets.Count)

The current line is returning a number. VBA needs to know what that number
means. So you need to tell VBA that it is a Sheet Index Number. For example,
Sheets(1) or Sheets(5).

Hope this helps! If so, let me know by clicking "YES" below.

 

[Barb Reinhardt]

Dim newWB as workbook

Set newWB = Workbooks("TROABook-200-299")

myws.Copy after:=newWB.Worksheets(newWB.Worksheets.Count)

 

[Don Guillett]

Does this help?

Sub copyshttoanotherwb()
Set dwb = Workbooks("yourdestinationsheet.xlsx")
With dwb
ActiveSheet.Copy after:=.Sheets(.Sheets.Count)
End With
End Sub

 

[SteveDB1]

Barb, and Ryan,

Thank you both for your responses.
I've tried both and get the same error.
subscript out of range.

For Ryan's, the error occurs with the
wks.Copy After:=Sheets(Workbooks("TROABook-200-299.xlsx").Sheets.Count)

......:= sheets(...) component.

the line shows 23 sheets, within the (Workbooks(...).sheets.count element,
but once it is "outside" with the sheets(....) portion it gives the subscript
out of range error.


For Barb's, the error occurs
Set newWB = Workbooks("TROABook-200-299")
workbooks(...) = <subscript out of range> component.
Barb, what if, instead of a new workbook, it just sets the TROABook as being
active?

I copied your forms and pasted each-- to make sure I didn't mis-type anything.

Again-- thank you both.

 

[SteveDB1]

Ryan,
I'm using the code you'd previously given me from this morning's post.
I thought that I could place that line of code immediately beneath the
rename element-- as follows:
-----------------------------------------------------------------------------------------
Select Case wks.Name
Case "Sum", "Summary", "SUM", "summary"
wks.Name = "Sum-" & strWbkName
wks.Copy after:=Sheets(Workbooks("TROABook-200-_
299.xlsx").Sheets.count)



Case "APN"
wks.Name = "APN-" & strWbkName
wks.Copy after:=Sheets(Workbooks("TROABook-200-_
299.xlsx").Sheets.count)

End Select

 

[Don Guillett]

Sub copyshttoanotherwb()
Dim dwb As Workbook
Dim wks As Worksheet
Set dwb = Workbooks("MENU.xls")
Set wks = Sheets("Data")
With dwb
wks.Copy after:=.Sheets(.Sheets.Count)
End With
End Sub

 

[RyanH]

Is the Workbooks("TROABook-200-299.xlsx") open? If it is not open you will
get that error.

Can you post the rest of the code? This will help me to diagnose things.

 

[SteveDB1]

Yes, the final destination workbook is open.
The entirety of the code is:
------------------------------------------------------------
Sub A1ReNmWksht()

Dim strWbkName As String

Dim wks As Worksheet

' get last 3 characters of the workbook name- minus file extension
strWbkName = Mid(ActiveWorkbook.Name, 5, InStr(ActiveWorkbook.Name, ".")
- 5)


' find worksheet name and change worksheet name if found
For Each wks In ActiveWorkbook.Worksheets
Select Case wks.Name
Case "Sum", "Summary", "SUM", "summary"
wks.Name = "Sum-" & strWbkName
wks.Copy
after:=Sheets(Workbooks("TROABook-200-299.xlsx").Sheets.count)



Case "APN"
wks.Name = "APN-" & strWbkName
wks.Copy
after:=Worksheets(Workbooks("TROABook-200-299.xlsx").Sheets.count)

End Select

Next wks

End Sub

 

[RyanH]

I was getting an error on your strWbkName line. Are you? Give this a try:

Sub A1ReNmWksht()

Dim strWbkName As String
Dim wbk As Workbook
Dim wks As Worksheet
Dim lngLastSheet As Long

' get last 3 characters of the workbook name- minus file extension
strWbkName = Mid(ActiveWorkbook.Name, 5, InStr(ActiveWorkbook.Name, ".")
- 5)

' where you want the data to go
Set wbk = Workbooks("TROABook-200-299.xlsx")
lngLastSheet = wbk.Sheets.Count

' find worksheet name and change worksheet name if found
For Each wks In ActiveWorkbook.Worksheets
Select Case wks.Name
Case "Sum", "Summary", "SUM", "summary"
With wks
.Name = "Sum-" & strWbkName
.Copy After:=wbk.Sheets(lngLastSheet)
End With


Case "APN"
With wks
.Name = "APN-" & strWbkName
.Copy After:=wbk.Sheets(lngLastSheet)
End With

End Select
Next wks

End Sub

 

[Dave Peterson]

I'd include the extension in Barb's code:

Set newWB = Workbooks("TROABook-200-299.xlsx")

 

[Barb Reinhardt]

I'd use something like this

myws.Copy after:=newWB.Worksheets(newWB.Worksheets.Count)

 

[SteveDB1]

Morning Ryan.
That was it. It works great now. THANK YOU!!!!
yes, I was getting the same error.
I did try the with, but I did with selection, not with wks.
Again-- thank you for your helps.
And to Barb, Don, Dave-- thank you all for your helps.
Best to you all........
SteveB.