Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: VBA: Copy different sheet in same workbook multiple times, naming according to list in cells

  1. #1
    Board Regular
    Join Date
    Dec 2013
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA: Copy different sheet in same workbook multiple times, naming according to list in cells

    Hi all,

    I've found a VBA script to duplicate the current active worksheet, but I need to copy a different worksheet in the workbook X amount of times (X = number in a cell).

    e.g.
    Active Worksheet Name: Start
    Worksheet to be copied: Master
    Copied X times where X = Start!A10
    Names of worksheets: Start!B10:B25

    Can anyone help me out? I had assumed it would need to be done in two stages - replicating the Master sheet, then renaming them - but I was going to (try to) cross that bridge when I got the first bit done.

    Thanks,

    Rick

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,833
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: VBA: Copy different sheet in same workbook multiple times, naming according to list in cells

    So you want to make several copies of your sheet named "Master"
    And the number of times you want it copied Will be found in sheet named "Start" Range("A10")

    Now how do we know what to name these sheets?

    You show:
    Names of worksheets: Start!B10:B25

    But that would mean 16 sheet

    How about B1 to last filled in cells in column B

    So if you had:
    On sheet Start
    B1 Alpha
    B2 Bravo
    B3 Charlie

    The script would know you want sheet master copied 3 times and name the sheets Alpha Bravo Charlie

    Would that work ?
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  3. #3
    Board Regular
    Join Date
    Dec 2013
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Copy different sheet in same workbook multiple times, naming according to list in cells

    Yes, that would give the same result so should work nicely, thank you ... Can you help?
    Last edited by RichCowell; Jul 22nd, 2019 at 05:58 AM.

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,833
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: VBA: Copy different sheet in same workbook multiple times, naming according to list in cells

    Try this:
    Code:
    Sub Copy_Master()
    'Modified 7/22/2019 5:55:33 AM  EDT
    On Error GoTo M
    Application.ScreenUpdating = False
    Dim i As Long
    Dim Lastrow As Long
    Lastrow = Sheets("Start").Cells(Rows.Count, "B").End(xlUp).Row
    For i = 1 To Lastrow
        Sheets("Master").Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = Sheets("Start").Cells(i, "B").Value
    Next
    Application.ScreenUpdating = True
    Exit Sub
    M:
    MsgBox "That sheet name may already be used or you made some mistake"
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  5. #5
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,833
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: VBA: Copy different sheet in same workbook multiple times, naming according to list in cells

    My script assumes:
    1. You have a sheet named: "Master"
    2.You have a sheet named "Start"
    3. You have names entered in Sheet "Start" Columns B starting in Range"B1"
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  6. #6
    Board Regular
    Join Date
    Dec 2013
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Copy different sheet in same workbook multiple times, naming according to list in cells

    That seems to work perfectly, thank you!
    There was a slight issue as I had a named range called "Picker" that it didn't like for some reason, that kept sending Excel into a loop and I had to crash it, I wasn't use it any more so just deleted it and it worked straight away...
    Thank you!

  7. #7
    Board Regular
    Join Date
    Dec 2013
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Copy different sheet in same workbook multiple times, naming according to list in cells

    Quick follow-up...
    Is there any that can work with the "Master" worksheet being hidden? The script does run, but not correctly.

  8. #8
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,833
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: VBA: Copy different sheet in same workbook multiple times, naming according to list in cells

    I never tested to see if a named Range would be a problem.
    Are you saying the sheet named Master is hidden and that is causing a problem?

    Is it hidden or very hidden?

    I never tested that but I can see how that could be a problem. Some people like hiding sheets so others cannot see them. Writing a script to copy a hidden sheet may cause a problem I have never tried that.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  9. #9
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,833
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: VBA: Copy different sheet in same workbook multiple times, naming according to list in cells

    Not sure what this means:
    The script does run, but not correctly.
    What does it do wrong?
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  10. #10
    Board Regular
    Join Date
    Dec 2013
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Copy different sheet in same workbook multiple times, naming according to list in cells

    Quote Originally Posted by My Aswer Is This View Post
    I never tested to see if a named Range would be a problem.
    Are you saying the sheet named Master is hidden and that is causing a problem?

    Is it hidden or very hidden?

    I never tested that but I can see how that could be a problem. Some people like hiding sheets so others cannot see them. Writing a script to copy a hidden sheet may cause a problem I have never tried that.
    I wasn't aware there were more ways to hide them - I'd just right clicked on the tab and clicked Hide.

    Could the VBA be adapted to unhide "Master" at the start, then hide it again at the end?

    Quote Originally Posted by My Aswer Is This View Post
    Not sure what this means:
    The script does run, but not correctly.
    What does it do wrong?
    It kept giving me a message about renaming, but when I entered a different name it just gave me the same message - don't worry about this one - deleting the range solved it.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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