sheet copying code halting

[Arie Bos]

The code below only copies a worksheet. Reason to use it that with manual copying, excel keeps asking for each range or cell name to accept it. With 50 or so names this means a lot of clicks. The last line in the macro avoids this.

The macro is a bit clumsy as i had to fill in the sheet to be copied ("18_04", last month) and the next month ("18_05"), but that fine. This simple code always worked well, but suddenly it halts on line 3.

(I know that clicking 50 time on the Yes botton takes less time than writing this new post, but hey, we all try to learn

Thanks,
Arie

Sub CopyMonth()

Application.DisplayAlerts = False
    ActiveWorbook.Sheets("18_04").Copy after:=Sheets("18_04")
'rename the copied sheet by putting the desired name in the next line
    ActiveSheet.Name = "18_05"
    Application.DisplayAlerts = True
End Sub

 

[Caleeco]

Hello Arie,

I think I understand what you're trying to achieve. I was a bit confused as to why you need to click 50 times, if there are only 12 months?

Try this code and let me know if it does what you want.

Sub CopyMonth()
    Dim ws As Worksheet
    Dim iMonth As Integer
    
    Set ws = ActiveSheet
    iMonth = CInt(Right(ws.Name, 2))
    ws.Copy After:=ws
    If Len(CStr(iMonth)) = 1 And iMonth <> 9 Then
       ActiveSheet.Name = "18_0" & iMonth + 1
    Else
        ActiveSheet.Name = "18_" & iMonth + 1
    End If
End Sub

Caleeco

 

[Arie Bos]

Hello Caleeco,

thank you for your help..

May be I have not been clear enough in my question:

I do have sheets with months numbering from 16_01, 16_02 etc. until 18_04. each sheet contains about 50 names. When I want to copy 18_04 to 18_05 (excel first makes 18_04(2) ) manually, a window pops up asking "The Name 'LPASSETS16_01' already exists. Click Yes to use that version of the name, or no to rename the version of LPAssets`16_01 you're moving or copying." So about 50 clicks on Yes are required before the process is done and released.

With the macro I first used, all this was handled and I just filled in the last month, and the new one (18_05) and it was done.
Your code does copy the sheet, bit the pop-ups also appear.

I hope I have been a bit clearer now... haha.

thanks again,

Arie

 

[Caleeco]

ahh i see what you mean, it's a problem with Named Ranges then....

Do you wish to keep named ranged in each instance of the copies? or just the first sheet?

Let me know
Caleeco

 

[Arie Bos]

Hi Caleeco,

There are about 100 names woth the scope 'Workbook' But each month month-sheet also contains a lot names, with a local scope.

Running your version, it halts at the iMonth= line.

Many thanks,
Arie