Results 1 to 7 of 7

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,209
    Articles
    57
    Blog Entries
    14
    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
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    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.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,209
    Articles
    57
    Blog Entries
    14
    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?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    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.

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,209
    Articles
    57
    Blog Entries
    14
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •