Results 1 to 9 of 9

Thread: How to create a macro to duplicate a set of rows/columns based on items in a list

  1. #1
    Seeker leebrockney's Avatar
    Join Date
    Sep 2019
    Posts
    6
    Articles
    0
    Excel Version
    Office 365

    How to create a macro to duplicate a set of rows/columns based on items in a list



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

    Hello all, I have a template that I need to duplicate into a worksheet based on the entries in a list on a separate tab. the template has formulas and things that update based on the item in the list. its a bit hard to explain so I attached an example of what I need at a very simple level. Thanks!

    Lee


    Example.xlsx

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,606
    Articles
    0
    Excel Version
    365
    In attached, button to click on sheet List To Loop Through.
    Adds a new sheet each time.
    Attached Files Attached Files

  3. #3
    Seeker leebrockney's Avatar
    Join Date
    Sep 2019
    Posts
    6
    Articles
    0
    Excel Version
    Office 365

    Thanks!

    Quote Originally Posted by p45cal View Post
    In attached, button to click on sheet List To Loop Through.
    Adds a new sheet each time.
    This worked great! Thanks! having a few issues converting it to my scenario though. maybe you can assist?

    the list that is used in my actual document is a single field pivot table. when I use the code in the example it get the list but than also grabs 10 or so blank cells at the bottom and makes blank entries for them even though there is no data in the cell.

    the final issue I am having is that the template to duplicate has a few hidden rows. when I run the macro it correctly pastes the data but the hidden rows are now unhidden. anyway to have it paste the template with the rows remaining hidden?

    Thanks again for your help!

    Lee

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,606
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by leebrockney View Post
    the list that is used in my actual document is a single field pivot table. when I use the code in the example it get the list but than also grabs 10 or so blank cells at the bottom and makes blank entries for them even though there is no data in the cell.
    Are the blank cells part of the pivot table? A workbook from you would help an awfult lot.

    Quote Originally Posted by leebrockney View Post
    the final issue I am having is that the template to duplicate has a few hidden rows. when I run the macro it correctly pastes the data but the hidden rows are now unhidden. anyway to have it paste the template with the rows remaining hidden?
    In the code, add .EntireRow to Set StuffToCopy = Sheets("Row Template to Duplicate").Range("A1:C8")

    leaving:
    Code:
    Set StuffToCopy = Sheets("Row Template to Duplicate").Range("A1:C8").EntireRow

  5. #5
    Seeker leebrockney's Avatar
    Join Date
    Sep 2019
    Posts
    6
    Articles
    0
    Excel Version
    Office 365
    Quote Originally Posted by p45cal View Post
    Are the blank cells part of the pivot table? A workbook from you would help an awfult lot.


    In the code, add .EntireRow to Set StuffToCopy = Sheets("Row Template to Duplicate").Range("A1:C8")

    leaving:
    Code:
    Set StuffToCopy = Sheets("Row Template to Duplicate").Range("A1:C8").EntireRow


    I have attached a workbook. I really appreciate your help on this! Thanks! You will notice that I have hidden rows and columns in the template to copy. Column BO is hidden.

    I also added an empty column between the two date pivots on the month list tab and that seems to have fixed the blank records issue.

    Example - NEW.xlsm
    Last edited by leebrockney; 2019-09-06 at 06:51 PM.

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,606
    Articles
    0
    Excel Version
    365
    Try:
    Code:
    Sub CreateBatchTrack()
    Set LstToLpThru = Sheets("Month List").Cells(1).CurrentRegion.Columns(1)
    Set LstToLpThru = Intersect(LstToLpThru, LstToLpThru.Offset(1))
    
    Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))
    Set Destn = NewSht.Cells(1)
    
    Set StuffToCopy = Sheets("Batch Track Template").Rows("1:23")
    For Each cll In LstToLpThru.Cells
      StuffToCopy.Copy Destn
      Destn.Value = cll.Value
      Set Destn = Destn.Offset(StuffToCopy.Rows.Count + 1)
    Next cll
    NewSht.Columns.AutoFit
    NewSht.Columns("BO").Hidden = True
    End Sub
    What are the values in column BO for in the template?

  7. #7
    Seeker leebrockney's Avatar
    Join Date
    Sep 2019
    Posts
    6
    Articles
    0
    Excel Version
    Office 365
    They are just lookup codes for a summary tab that will generate aggregated data based on the sheet that is created.

  8. #8
    Seeker leebrockney's Avatar
    Join Date
    Sep 2019
    Posts
    6
    Articles
    0
    Excel Version
    Office 365
    at the end of the process I want to simply copy the new sheet and paste the whole thing as values to remove all the formulas that way I can change the filters and run another without impacting the previously generated sheet.

    I added this and it doesn't work.

    NewSht.Cells.Copy
    NewSht.Cells.PasteSpecial Paste:=xlPasteValue

  9. #9
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,606
    Articles
    0
    Excel Version
    365
    NewSht.usedrange.value = NewSht.usedrange.value

Posting Permissions

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