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

L

leebrockney

New member
Joined
Sep 4, 2019
Messages
6
Reaction score
0
Points
0
Excel Version(s)
Office 365
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


View attachment Example.xlsx
 
In attached, button to click on sheet List To Loop Through.
Adds a new sheet each time.
 

Attachments

  • ExcelGuru10204Example.xlsm
    23.9 KB · Views: 11
Thanks!

p45cal said:
In attached, button to click on sheet List To Loop Through.
Adds a new sheet each time.
Click to expand...

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
 
leebrockney said:
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.
Click to expand...
Are the blank cells part of the pivot table? A workbook from you would help an awfult lot.

leebrockney said:
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?
Click to expand...
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
 
p45cal said:
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
Click to expand...



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.

View attachment Example - NEW.xlsm
 
Last edited:
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?
 
They are just lookup codes for a summary tab that will generate aggregated data based on the sheet that is created.
 
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
 
NewSht.usedrange.value = NewSht.usedrange.value
 
You must log in or register to reply here.
Back
Top