Hi,

I have a list of sheet names and when a new sheet is created it is added in
sequetial order on the list. When a new sheet is created, I'm trying to have
it placed after the item on the list before it. I error out at the copy
statement. It tells me it failed. I am unable to determine why. In debug,
the correct sheetname and Sheetnumber appear in the variables, so I'm at a
loss.

Does anyone have any suggestions?

If MainPagepg.Range("BC" & NewRow - 1) = "" Then
ShNumber = Firstpg.Index
Else
AfterShName = MainPagepg.Range("BC" & NewRow - 1).Value
ShNumber = Sheets(AfterShName).Index
End If

CAMMaster.Activate

Call NewSheetName(SheetName)
If SheetName = "" Then Exit Sub

'copy the sheet
ActiveSheet.Copy After:=Sheets(ShNumber)


Thanks for your help.
--

Karen

Karen,

What's the error message?

Doug

"Karen53" wrote in message
...
Hi,

I have a list of sheet names and when a new sheet is created it is added
in
sequetial order on the list. When a new sheet is created, I'm trying to
have
it placed after the item on the list before it. I error out at the copy
statement. It tells me it failed. I am unable to determine why. In
debug,
the correct sheetname and Sheetnumber appear in the variables, so I'm at a
loss.

Does anyone have any suggestions?

If MainPagepg.Range("BC" & NewRow - 1) = "" Then
ShNumber = Firstpg.Index
Else
AfterShName = MainPagepg.Range("BC" & NewRow - 1).Value
ShNumber = Sheets(AfterShName).Index
End If

CAMMaster.Activate

Call NewSheetName(SheetName)
If SheetName = "" Then Exit Sub

'copy the sheet
ActiveSheet.Copy After:=Sheets(ShNumber)


Thanks for your help.
--

Karen

Hi Doug,

I've had the workbook closed for a while. I just opened it again to tell
you exactly what it siays and it worked just fine. I spent hours trying to
figure out what was going on. When you are working on macros, does Excel
become unbalanced or something, or is it something I am doing?

As I recall it said something to the effect of the copy class failed.

I would appreciate any insights.

Thanks.
--

Karen


"Doug Glancy" wrote:

Karen,

What's the error message?

Doug

"Karen53" wrote in message
...
Hi,

I have a list of sheet names and when a new sheet is created it is added
in
sequetial order on the list. When a new sheet is created, I'm trying to
have
it placed after the item on the list before it. I error out at the copy
statement. It tells me it failed. I am unable to determine why. In
debug,
the correct sheetname and Sheetnumber appear in the variables, so I'm at a
loss.

Does anyone have any suggestions?

If MainPagepg.Range("BC" & NewRow - 1) = "" Then
ShNumber = Firstpg.Index
Else
AfterShName = MainPagepg.Range("BC" & NewRow - 1).Value
ShNumber = Sheets(AfterShName).Index
End If

CAMMaster.Activate

Call NewSheetName(SheetName)
If SheetName = "" Then Exit Sub

'copy the sheet
ActiveSheet.Copy After:=Sheets(ShNumber)


Thanks for your help.
--

Karen

Karen,

I'm glad it's working! I was wondering if your code involves more than one
workbook, or if somehow the activesheet got changed in the NewSheetName
subroutine. But I don't have any great insights. If it happens again, I'd
confirm that the Activesheet is what you expect.

Doug

"Karen53" wrote in message
...
Hi,

I have a list of sheet names and when a new sheet is created it is added
in
sequetial order on the list. When a new sheet is created, I'm trying to
have
it placed after the item on the list before it. I error out at the copy
statement. It tells me it failed. I am unable to determine why. In
debug,
the correct sheetname and Sheetnumber appear in the variables, so I'm at a
loss.

Does anyone have any suggestions?

If MainPagepg.Range("BC" & NewRow - 1) = "" Then
ShNumber = Firstpg.Index
Else
AfterShName = MainPagepg.Range("BC" & NewRow - 1).Value
ShNumber = Sheets(AfterShName).Index
End If

CAMMaster.Activate

Call NewSheetName(SheetName)
If SheetName = "" Then Exit Sub

'copy the sheet
ActiveSheet.Copy After:=Sheets(ShNumber)


Thanks for your help.
--

Karen

Me again,

I googled "method copy of worksheet failed" which is probably the error you
got. There were a few interesting Google Group threads. A couple of them
refer to a problem when copying multiple sheets and this KB article:

http://support.microsoft.com/default...84&Product=xlw

hth,

Doug

"Karen53" wrote in message
...
Hi Doug,

I've had the workbook closed for a while. I just opened it again to tell
you exactly what it siays and it worked just fine. I spent hours trying
to
figure out what was going on. When you are working on macros, does Excel
become unbalanced or something, or is it something I am doing?

As I recall it said something to the effect of the copy class failed.

I would appreciate any insights.

Thanks.
--

Karen


"Doug Glancy" wrote:

Karen,

What's the error message?

Doug

"Karen53" wrote in message
...
Hi,

I have a list of sheet names and when a new sheet is created it is
added
in
sequetial order on the list. When a new sheet is created, I'm trying
to
have
it placed after the item on the list before it. I error out at the
copy
statement. It tells me it failed. I am unable to determine why. In
debug,
the correct sheetname and Sheetnumber appear in the variables, so I'm
at a
loss.

Does anyone have any suggestions?

If MainPagepg.Range("BC" & NewRow - 1) = "" Then
ShNumber = Firstpg.Index
Else
AfterShName = MainPagepg.Range("BC" & NewRow - 1).Value
ShNumber = Sheets(AfterShName).Index
End If

CAMMaster.Activate

Call NewSheetName(SheetName)
If SheetName = "" Then Exit Sub

'copy the sheet
ActiveSheet.Copy After:=Sheets(ShNumber)


Thanks for your help.
--

Karen