Hi I have a problem with a loop that always stops after five times,
and presents "Copy method of Worksheet class failed"

The code is :

For Year = FirstYear To LastYear
Sht2 = "" & Year & " test"
Sheets(Sht2).Delete
Sht = "" & Year & " table sheet"
Sheets(Sht).Copy Befo=Sheets(2)
Sheets(Sht & " (2)").Name = Sht2

The loop fails at:

Sheets(Sht).Copy Befo=Sheets(2)


Sheet(2) is still part of the workbook while the loop breaks down.
Any suggestions as to why the loop breaks down?

Any help is greately appreciated!

Hi,

A couple of points. You can't use YEAR in that way it's a reserved Excel
keyword.
I don't know where you assign values to firstyear/lastyear so I assigned
arbitary values and think this now does what you were attempting

Sub stance()
firstyear = 2001
lastyear = 2008
For Y = firstyear To lastyear
Sht2 = "" & Y & " test"
Sheets(Sht2).Delete
Sht = "" & Y & " table sheet"
Sheets(Sht).Copy Befo=Sheets(2)
Sheets(Sht & " (2)").Name = Sht2
Next
End Sub


Mike

"Wesslan" wrote:

Hi I have a problem with a loop that always stops after five times,
and presents "Copy method of Worksheet class failed"

The code is :

For Year = FirstYear To LastYear
Sht2 = "" & Year & " test"
Sheets(Sht2).Delete
Sht = "" & Year & " table sheet"
Sheets(Sht).Copy Befo=Sheets(2)
Sheets(Sht & " (2)").Name = Sht2

The loop fails at:

Sheets(Sht).Copy Befo=Sheets(2)


Sheet(2) is still part of the workbook while the loop breaks down.
Any suggestions as to why the loop breaks down?

Any help is greately appreciated!

Hi
Assuming Sht exists before the copy (???) you could try inserting
Application.CutCopymode = False
before the copy

regards
Paul

On Feb 12, 12:40*pm, Wesslan wrote:
Hi I have a problem with a loop that always stops after five times,
and presents "Copy method of Worksheet class failed"

The code is :

For Year = FirstYear To LastYear
* * Sht2 = "" & Year & " test"
* * Sheets(Sht2).Delete
* * Sht = "" & Year & " table sheet"
* * Sheets(Sht).Copy Befo=Sheets(2)
* * Sheets(Sht & " (2)").Name = Sht2

The loop fails at:

Sheets(Sht).Copy Befo=Sheets(2)

Sheet(2) is still part of the workbook while the loop breaks down.
Any suggestions as to why the loop breaks down?

Any help is greately appreciated!

Dear Mike and Paul,

Thank you both for your suggestions. Unfortunately they didn't work.
The loop with the start year and the end year is not the problem (I
think) as it runs through five companies flawless, but when the sixth
company is selected (irregardless of which it is) and the loop is run
(a loop in a loop) the problem is always encountered already on the
first year (in this case 2003) with the display "Copy method of
Worksheet class failed".

Any other suggestions?

Regards,

Peder

Hi
loop within a loop? Company selected??
Can you show us the code that gives the error, as your problem seems
to have changed.
You should also check worksheet names carefully. Are the ones in the
VBA code EXACTLY the same as the ones in the VBA code, including
leading and trailing spaces (which can be hard to see).
regards
Paul


On Feb 12, 2:58*pm, Wesslan wrote:
Dear Mike and Paul,

Thank you both for your suggestions. Unfortunately they didn't work.
The loop with the start year and the end year is not the problem (I
think) as it runs through five companies flawless, but when the sixth
company is selected (irregardless of which it is) and the loop is run
(a loop in a loop) the problem is always encountered already on the
first year (in this case 2003) with the display "Copy method of
Worksheet class failed".

Any other suggestions?

Regards,

Peder

Hi,

You now have me confused but at my age that happens. There are no nested
loops here there is 1 and that loop copies a worksheet, deletes a sheet and
then renames a sheet which might not be what you intend but there you are.

If the single loop does so sucessfully 4 times and crashes on the fifth then
my guess is your computed sheet name doesn't match the actual worksheet name
so your copy statement fails.

Mike

"Wesslan" wrote:

Dear Mike and Paul,

Thank you both for your suggestions. Unfortunately they didn't work.
The loop with the start year and the end year is not the problem (I
think) as it runs through five companies flawless, but when the sixth
company is selected (irregardless of which it is) and the loop is run
(a loop in a loop) the problem is always encountered already on the
first year (in this case 2003) with the display "Copy method of
Worksheet class failed".

Any other suggestions?

Regards,

Peder

Dear Mike and Paul,

Once again thanks for the replies. Paul, I have verified that the
names are exactly the same (as they never change between the loops).
Mike, you are very right regarding that I only presented one loop but
I will now present the entire code. Bear with me, as the text with
code will be quite long.

There is another loop that changes the company to be excluded and thus
(the z and x indiate how many companies are to analyzed and where in
the sheet they are (my way of testing different set of comps to
problemsolve):

"ExcludedCompany = Sheets("Peer Group").Range("a2").Value" changes as
the A2 value changes.


The loop stops at (on the sixth company):
"Sheets(Sht).Copy Befo=Sheets(2)"

The entire code is:


"
Sub RunningThroughPGComps()
z = InputBox("What is the first row selection?", "What is the first
row selection?")
x = InputBox("What is the last row selection?", "What is the last row
selection?")

For z = z To x

Sheets("Peer Groups to go through").Select

Cells(z, 1).Copy
Worksheets("Peer Group").Select
Range("b2").Select
ActiveSheet.Paste

Condition_Industry = Range("D2").Value
Condition_CapSize = Range("E2").Value

Call CleaningPeerGroup

Next z

End Sub




Sub CleaningPeerGroup()

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlManual

'Checks whether a manual cap size is requested
size_switch = MsgBox("Do you want to make a manual cut off point
regarding MV?" & vbCrLf & "This will disregard what you chose
regarding Large/Mid Cap", vbYesNo, "Manual Cut off point regarding
MV?")
If size_switch = 6 Then
size_switch = InputBox("1 = Greater than 0 = Less than", "Do you
want to exclude greater than or less than MV?", 1, vbDefaultButton1)

Do While size_switch < (1 Or 0)
size_switch = InputBox("1 = Greater than 0 = Less than", "Please
provide a valid number for chosing positive or negative cut off?", 1,
vbDefaultButton1)
Loop

Condition_CapSize_manual = InputBox("Cut off point MV. No further
size is checked apart from large/midcap selection", "Cut off point
regarding size")
'If a manual selection is taken the CapSize selection is
disregarded
If Not Condition_CapSize_manual = 0 Then Condition_CapSize = "All
Sizes"
End If

Condition1 = Condition_SubIndustry
Condition2 = Condition_CapSize
Condition3 = Condition_CapSize_manual

Sht = "Peer Group"
Sheets(Sht).Select
If Condition2 < "All Sizes" Then
Range("A14").Value = "EMEA " & Condition2 & " " & Condition1
Else: Range("A14").Value = "EMEA " & Condition1
End If

'Verifies that the correct countries has been selected
Ans = MsgBox("Have you selected the countries to be studied?",
vbYesNo, "Have you selected the countries to be studied?")
If Ans = vbNo Then Exit Sub

'Defines years to analyze and company to compare
Calculate
ExcludedCompany = Sheets("Peer Group").Range("a2").Value
FirstYear = InputBox("Which is the first year you want to analyze?",
"Which is the first year you want to analyze?", 2003)
LastYear = InputBox("Which is the last year you want to analyze?",
"Which is the last year you want to analyze?", 2007)

For Year = FirstYear To LastYear
Sht2 = "" & Year & " test"
Sheets(Sht2).Delete
Sht = "" & Year & " table sheet"
Sheets(Sht).Copy Befo=Sheets(2)
Sheets(Sht & " (2)").Name = Sht2

SearchVariable = "SUBINDUSTRY"
Call Find

OffsetColumn = Cells(SearchRow, OffsetColumn).Address
NumberofComps = Sheets(Sht2).UsedRange.Rows.Count

'Isolates Peer Group in term of Industry


For i = 2 To NumberofComps
Position = Range(OffsetColumn).Offset(i - 1, 0).Address
If Not IsEmpty(Range(Position).Value) Then
If Range(Position).Value < Condition1 Then
Rows(i).Delete shift:=xlUp
i = i - 1
End If
End If
Next i

'Eliminates the analyzed company if it is part of the peer group

Set rng = Nothing
Set rng = Cells.Find(What:=ExcludedCompany, _
After:=Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not rng Is Nothing Then
Rows(rng.Row).Delete shift:=xlUp
End If

'Isolates Peer Group in terms of Capital Size
If Condition2 < "All Sizes" Then

SearchVariable = "Large Cap / Mid-Cap Flag"
Call Find

OffsetColumn = Cells(SearchRow, OffsetColumn).Address
NumberofComps = Sheets(Sht2).UsedRange.Rows.Count

For i = 2 To NumberofComps
Position = Range(OffsetColumn).Offset(i - 1, 0).Address
If Not IsEmpty(Range(Position).Value) Then
If Range(Position).Value < Condition2 Then
Rows(i).Delete shift:=xlUp
i = i - 1
End If
End If
Next i
End If

'Isolates Peer Group in terms of Capital Size
If Not Condition3 = 0 Then

SearchVariable = "MARKET VALUE AT END OF PERIOD"
Call Find

OffsetColumn = Cells(SearchRow, OffsetColumn).Address
NumberofComps = Sheets(Sht2).UsedRange.Rows.Count

If size_switch = 0 Then

For i = 2 To NumberofComps
Position = Range(OffsetColumn).Offset(i - 1, 0).Address
If Not IsEmpty(Range(Position).Value) Then
If (Range(Position).Value Condition3) Then
Rows(i).Delete shift:=xlUp
i = i - 1
End If
End If
Next i
End If

If size_switch = 1 Then
For i = 2 To NumberofComps
Position = Range(OffsetColumn).Offset(i - 1, 0).Address
If Not IsEmpty(Range(Position).Value) Then
If (Range(Position).Value < Condition3) Then
Rows(i).Delete shift:=xlUp
i = i - 1
End If
End If
Next i
End If
End If

'Defines ranges and names them
EndRow = 401
SearchVariable = "MARKET VALUE AT END OF PERIOD"
Call Find
cell1 = Cells(SearchRow + 1, OffsetColumn).Address
Position = Cells(EndRow, OffsetColumn).Address
Range(cell1, Position).Name = "PG_MV_" & Year

SearchVariable = "STANDARD DEVIATION OF EXCESS RETURN"
Call Find
cell1 = Cells(SearchRow + 1, OffsetColumn).Address
Position = Cells(EndRow, OffsetColumn).Address
Range(cell1, Position).Name = "PG_Risk_" & Year

SearchVariable = "Geometric Mean"
Call Find
cell1 = Cells(SearchRow + 1, OffsetColumn).Address
Position = Cells(EndRow, OffsetColumn).Address
Range(cell1, Position).Name = "PG_ER_" & Year

SearchVariable = "SPI"
Call Find
cell1 = Cells(SearchRow + 1, OffsetColumn).Address
Position = Cells(EndRow, OffsetColumn).Address
Range(cell1, Position).Name = "PG_SPI_" & Year

Next Year

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

Sheets("Peer Group").Select

End Sub

Sub Find()
Sheets(Sht2).Select
SearchRow = 1
OffsetColumn = WorksheetFunction.Match(SearchVariable, Rows(1), 0)
End Sub

"

As I said it becomes quite long. But if you have ANY suggestions as to
how to get around this it would be great. Because the macro as it is
now does everything correct for the first 5 companies. If I save and
close the file, take another five etc it all works fine. But I want to
be able to do it on 400 companies and especially over time re-run it.
And as we all know, whats the point with a macro thats not automatic?

Once again thanks for your suggestions I have received so far!

/Peder

Hi
Just searched this group and your copy problem has a history.

Copying Worksheet Programmatically Causes Run-Time Error 1004
http://support.microsoft.com/default...;210684&Produc...

regards
Paul

On Feb 12, 5:45*pm, Wesslan wrote:
Dear Mike and Paul,

Once again thanks for the replies. Paul, I have verified that the
names are exactly the same (as they never change between the loops).
Mike, you are very right regarding that I only presented one loop but
I will now present the entire code. Bear with me, as the text with
code will be quite long.

Paul,

Thanks. Its interesting there is no real fix to it, appart from
programming another VBA that opens the file and runs through it :)

Once again, both Paul and Mike, the two of you have really made my
day!

I truly hope you have a nice evening.

/Peder