+ Reply to Thread
Results 1 to 15 of 15

Creating copies of sheets based on a list

  1. 09-27-2021, 03:11 AM #1
    Peaky_Blinder
    Peaky_Blinder is offline
    Registered User
    Join Date
    01-30-2019
    Location
    sheffield, england
    MS-Off Ver
    Excel 365
    Posts
    20

    Creating copies of sheets based on a list

    Hi all

    I currently have a code that allows me to make copies of certain sheets based on a list and names them based on that list. Although this coed is of great help it is sometimes times consuming as I have to create individual list adjust the code to the sheet I wan to copy and then run the code. What I am wanting to do is have a code that looks at several lists on one sheet and copies the applicable sheets

    The example looks in column A from row 2 then makes a copy of sheet P1 I would like to add list in columns A to V then each column list to copy sheets P1 through to P22.

    I tried to simply copy repeat the code can change the column name and sheet nome but that didnt work!

    Many thanks for your help

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  2. 09-27-2021, 04:00 AM #2
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,239

    Re: Creating copies of sheets based on a list

    Perhaps... 
    Please Login or Register  to view this content.
    Is the list in A1:V1?
    Or upload a sample file depicting your actual setup required...
    Last edited by sintek; 09-27-2021 at 04:04 AM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!
    Reply With QuoteRegister To Reply
  3. 09-27-2021, 04:16 AM #3
    Peaky_Blinder
    Peaky_Blinder is offline
    Registered User
    Join Date
    01-30-2019
    Location
    sheffield, england
    MS-Off Ver
    Excel 365
    Posts
    20

    Re: Creating copies of sheets based on a list

    Hi

    There would be separate lists in each column. Each column relating to separate sheets to copy.

    The separate list appear on sheet 1 - List, row one highlights the sheet which would be copied, below from row 2 would be the name of the newly created sheet.

    Hopefully the file has attached
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 09-27-2021, 04:26 AM #4
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,239

    Re: Creating copies of sheets based on a list

    So just to understand...
    You want to copy P1 go down list naming it 25 then 121 then 6 etc etc
    same with P3 going down naming 102, then 5 then 115 etc etc
    Reply With QuoteRegister To Reply
  5. 09-27-2021, 04:35 AM #5
    Peaky_Blinder
    Peaky_Blinder is offline
    Registered User
    Join Date
    01-30-2019
    Location
    sheffield, england
    MS-Off Ver
    Excel 365
    Posts
    20

    Re: Creating copies of sheets based on a list

    Yes mate, exactly that.

    Thanks
    Reply With QuoteRegister To Reply
  6. 09-27-2021, 04:44 AM #6
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,239

    Re: Creating copies of sheets based on a list 

    Please Login or Register  to view this content.
    Last edited by sintek; 09-27-2021 at 04:50 AM.
    Reply With QuoteRegister To Reply
  7. 09-27-2021, 04:50 AM #7
    Peaky_Blinder
    Peaky_Blinder is offline
    Registered User
    Join Date
    01-30-2019
    Location
    sheffield, england
    MS-Off Ver
    Excel 365
    Posts
    20

    Re: Creating copies of sheets based on a list

    That is brilliant, many thanks for that
    Reply With QuoteRegister To Reply
  8. 09-27-2021, 04:51 AM #8
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,239

    Re: Creating copies of sheets based on a list

    Just make sure you got edited code...After was changed to Before...
    Reply With QuoteRegister To Reply
  9. 09-27-2021, 06:23 AM #9
    Peaky_Blinder
    Peaky_Blinder is offline
    Registered User
    Join Date
    01-30-2019
    Location
    sheffield, england
    MS-Off Ver
    Excel 365
    Posts
    20

    Re: Creating copies of sheets based on a list

    Just one last questions, if I was to move the rows of the sheets to be copied (ie 1) to row 5 which bit of the code would i alter?

    Thanks
    Reply With QuoteRegister To Reply
  10. 09-27-2021, 06:31 AM #10
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,239

    Re: Creating copies of sheets based on a list

    So The P1 P2 P3 is in row 5 and the new names in rows 6 down?
    Please Login or Register  to view this content.
    Last edited by sintek; 09-27-2021 at 06:34 AM.
    Reply With QuoteRegister To Reply
  11. 09-27-2021, 02:44 PM #11
    Peaky_Blinder
    Peaky_Blinder is offline
    Registered User
    Join Date
    01-30-2019
    Location
    sheffield, england
    MS-Off Ver
    Excel 365
    Posts
    20

    Re: Creating copies of sheets based on a list

    Just when I thought I'd done!

    So to get the data to copy and rename the sheet I am now using a formula which is pulling this from a pivot table, therefore there are now blanks cells that contain these formulas and once the code finds one of these blank cells it throws an error. I have attached the file.

    The code I am using is now

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Peaky_Blinder; 09-27-2021 at 03:18 PM.
    Reply With QuoteRegister To Reply
  12. 09-28-2021, 02:57 AM #12
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,239

    Re: Creating copies of sheets based on a list

    Why thousands of rows with formulas...My opinion just bad practice...I always only populate a cell if really needed...
    Please Login or Register  to view this content.
    Also, please remember to mark thread as solved once your initial query has been answered...
    Reply With QuoteRegister To Reply
  13. 09-28-2021, 03:23 AM #13
    Peaky_Blinder
    Peaky_Blinder is offline
    Registered User
    Join Date
    01-30-2019
    Location
    sheffield, england
    MS-Off Ver
    Excel 365
    Posts
    20

    Re: Creating copies of sheets based on a list

    Hi, that worked a treat. it is because the number a sheets requiring to be copied very vastly. I agree I personally would not usually populate with so many rows, and probably over did it here (which I will reduce) but I assume the principle would be the same no matter how many rows were populated with the formula
    Reply With QuoteRegister To Reply
  14. 09-28-2021, 03:25 AM #14
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,239

    Re: Creating copies of sheets based on a list

    Glad to have helped...
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED...
    Tx for rep +
    Reply With QuoteRegister To Reply
  15. 09-28-2021, 03:26 AM #15
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,916

    Re: Creating copies of sheets based on a list

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA to create multiple copies of a sheet, copies named based on list in other sheet
    By Parth007 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-27-2019, 02:40 AM
  2. [SOLVED] Creating copies of muliple rows based on numerical value in a cell.
    By Keybler in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-20-2017, 05:53 PM
  3. Creating copies of muliple rows based on numerical value in a cell.
    By Keybler in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-20-2017, 03:39 PM
  4. [SOLVED] Error handling when creating sheets based on list and hyperlink
    By gv29 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-03-2015, 07:06 PM
  5. IF between two sheets that also copies and makes a neat list
    By kerkezeto in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-07-2015, 02:16 AM
  6. Creating separate sheets based on single rows in list?
    By Taxster in forum Excel General
    Replies: 3
    Last Post: 12-16-2014, 04:24 PM
  7. Help creating a worload list for undividual people based on data in different sheets.
    By Clyde Daleton in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-13-2013, 12:31 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1