How to copy sheet according to a cell value and rename.

[VISHALS]

Hi Everybody,

I needed help on the below :

I want to copy a sheet Example (" Main_1") and paste the sheet according to number entered in a cell example in Cell "C3". and rename every copy sheet as Main_2, Main_3 , etc.

like :

if in cell C3 value entered is 4 therefore the Sheet "Main_1" will be copy and paste 4 times and will be rename Main_2, Main_3, Main_4.

Can someone please guide me on that . i try to record a macro to do it but it copies it on fix time that is if recorded to copy 2 times everytime it run it copy only on 2 times.

I have attached my sample file here.
Many thanks in advance.

[Ken Puls]

Try this out. It assumes that the sheet you're starting with is the first in the file, and that the name will always end with _x. If that's not the case, let me know.

Sub COPY_SHEETS()
    Dim lIterations As Long
    Dim lIterated As Long
    Dim wsActive As Worksheet
    Dim sPrefix As String
    Set wsActive = ActiveSheet
    sPrefix = Left(wsActive.Name, InStr(1, wsActive.Name, "_"))
    With wsActive
        lIterations = .Range("C3").Value - 1
        For lIterated = 1 To lIterations
            wsActive.Copy after:=Sheets(lIterated)
            Worksheets(lIterated + 1).Name = sPrefix & lIterated + 1
        Next lIterated
    End With
End Sub

[VISHALS]

Hi Ken,

Many thanks for the reply its working good but its adding the sheets before the MAin_1 . it shall add all the new sheet after the Sheet MAin_1 that is in this way MAin_1, Main_2, Main_3, etc.

can you please see that for me.\

thanks again.

[Ken Puls]

Sorry Vishals, just getting back to this.

In your example file this works fine. Is this code not run from the first worksheet in the file?

[VISHALS]

Hi Ken,

Thanks you very very much for your kind help.

Yes i was my mistake its ok .

have you been able to check this post on the formula forum for me:

http://www.excelguru.ca/forums/showt...lanning-system.

I know its complicated but if you can just give me some hints of how this can be done it will be of great help for me as right now i using approx 3 hours by doing the planning manually for all the group order.

also is there a possibility to do it in VBA as with formula it is coming much bigger in size.

Your guidance will be very very appreciated Sir.

Regards,

Vishal.

[Ken Puls]

Hi Vishals,

To be honest, I haven't had time. It's a lot to think through, and I haven't had time to sit and really fully digest it.

[VISHALS]

Hi Ken,

Many thanks for your knid reply ok when you have time please please just give me some hint on that. Moreover i will try to simplify it more a little bit .

Thanks again.