Copying Active Sheet stops abruptly after execution without throwing any error!

[sak1985]

Hello All,

I am trying to copy a sheet called Temp in a workbook and pasting in the same workbook multiple times.

For oj = 2 To 21
    Application.StatusBar = "Please wait... PBA's are generating"
    If ActiveWorkbook.Sheets("Temp").Visible = True Then
        ActiveWorkbook.Sheets("Temp").Select
        [B]ActiveWorkbook.Sheets("Temp").Copy After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)[/B]
        ActiveSheet.Name = Left(Sheet2.Range("A" & oj).Value, 9)
        ActiveSheet.Range("B1").Value = Sheet2.Range("A" & oj).Value
        ActiveSheet.Range("B2").Value = "Week"
        ActiveSheet.Range("B3").Value = "% Percentage"
       End If
    Next oj

Here in the code stops at the highlighted part. What would be the issue here

Regards,
Sakthi

 

[svendiamond]

It seems to work for me, but will get caught up on the next line if there are any duplicate values in your list of sheet names. I don't see a problem with the highlighted part, though. Which error message is it giving you?

 

[sak1985]

It seems to work for me, but will get caught up on the next line if there are any duplicate values in your list of sheet names. I don't see a problem with the highlighted part, though. Which error message is it giving you?

Thanks for taking time to reply... I don't get any error during the execution, apart from that the execution gets interrupt automatically without any message. There is no duplicate sheet with the same name but the code has to replicate the sheet "Temp" n number of times specified.

If ActiveWorkbook.Sheets("Temp").Visible = True Then
        ActiveWorkbook.Sheets("Temp").Select
        [B]ActiveWorkbook.Sheets("Temp").Copy After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
[/B]

From the above code the replication is done from "Temp" which is happening and I get a new sheet named
Temp(1).

But from the below code the sheet name has to be renamed but here the code gets interrupt without any error msg.

ActiveSheet.Name = Left(Sheet2.Range("A" & oj).Value, 9)

So, as per the above code I get only one sheet gets replicated in the name Temp(1) which is not the purpose of this code.

Regards,
Sakthi

 

[svendiamond]

I don't understand how your code can be interrupted without any error message... if you run the code and it stops you at a certain line that it highlights yellow, it should pop up with an error message. What values are in Sheet2.Range("A" & oj)?

 

[sak1985]

I don't understand how your code can be interrupted without any error message... if you run the code and it stops you at a certain line that it highlights yellow, it should pop up with an error message. What values are in Sheet2.Range("A" & oj)?

Even I have the same question as If any interruption will be highlighted with an error message. The Value in Sheet2.Range("A"&oj) is the no of Sheet's to be copied.

The number of copies are passed to "oj" through a for loop.

Regards,
Sakthi

 

[svendiamond]

The Value in Sheet2.Range("A"&oj) is the no of Sheet's to be copied.

ActiveSheet.Name = Left(Sheet2.Range("A" & oj).Value, 9)

Are you sure? Looks like they are values from which you are getting the first 9 characters to be the name of the active sheet...