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

VISHALS

New member
Joined
Sep 24, 2011
Messages
15
Reaction score
0
Points
0
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.

:confused2:
 

Attachments

  • COPY_SHEETS.xls
    23.5 KB · Views: 46
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.

Code:
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
 
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.
 
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?
 
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/showthread.php?669-Help-in-order-to-Automate-a-planning-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.
 
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. :(
 
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.
 
Back
Top