Automatically rename copied sheet -VBA

[PATSYS]

Hi all,

I have the following code:

Sub Copysheet()
    
Dim i As Integer

i = 1
    
For i = 1 To 8
    
    Sheets("P&L-OPS").Copy Before:=Sheets(5)

Next i

End Sub

How can I include a line/lines that will rename the copied 8 sheets as follows:

MAN
MAK
PAK
MAP
PAR
KAR
RAK
KAN

In that exact order?

Thanks

 

[AlphaFrog]

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Copysheet()<br><br>    <SPAN style="color:#00007F">Dim</SPAN> i      <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 8<br>        Sheets("P&L-OPS").Copy Before:=Sheets(5)<br>        Sheets(5).Name = Array("MAN", "MAK", "PAK", "MAP", "PAR", "KAR", "RAK", "KAN")(8 - i)<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[PATSYS]

Thanks again Alphafrog.

I hope this will be the last question I will be asking for this weekend

 

[wigi]

Or slightly different:

Sub Copysheet()
    Dim i As Integer
    Application.ScreenUpdating = False
    For i = 1 To 8
        Sheets("P&L-OPS").Copy Sheets(5)
        ActiveSheet.Name = Choose(i, "MAN", "MAK", "PAK", "MAP", "PAR", "KAR", "RAK", "KAN")
    Next
End Sub

 

[AlphaFrog]

Hello wigi,

Your solution results in the sheets named in reverse order. You would have to reverse sheet names in the choose function or use something like this...

<font face=Courier New>ActiveSheet.Name = Choose(9 - i, "MAN", "MAK", "PAK", "MAP", "PAR", "KAR", "RAK", "KAN")</FONT>

Or use
<font face=Courier New>    <SPAN style="color:#00007F">For</SPAN> i = 8 <SPAN style="color:#00007F">To</SPAN> 1 <SPAN style="color:#00007F">Step</SPAN> -1</FONT>

 

[wigi]

True, good catch Alphafrog