Databases
StackOverflow2013
Postsdbo
POVBA Excel Generate Unique Pages From Template and List
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POVBA Excel Generate Unique Pages From Template and List
    primarykey
    data
    text
    <p>I have been working on this for sometime now (I am not experienced in VBA at all), but I keep getting errors so here we are.</p> <p><strong>Setup:</strong> For simplicity purposes I have two worksheets in a workbook. The first, "Daily Order", is a list of all my products, each row being a different product (approx. 1,000), each column indicating different information about the product (ie id, cost, weight, etc).<br> The second, "Template", is a pricing template that, when given product information, will generate a pricing table.</p> <p><strong>Objective:</strong> Create a VBA Macro to loop through each row of the "Daily Order" worksheet and for each row make a copy of the template sheet and append certain information to that new sheet.</p> <p><strong>What doesn't work:</strong></p> <pre><code>Sub GeneratePriceBook() Dim rw As Range Dim temp As Worksheet Dim ws As Worksheet Dim daily As Worksheet Set daily = Worksheets("Daily Order") Set temp = Worksheets("Template") temp.Activate For Each rw In daily.Rows temp.Copy After:=Sheets(Sheets.Count) Set ws = Sheets(Sheets.Count) ws.Name = rw.Value With ws .Range("A6").Formula = "='Daily Order'!B" &amp; rw.Row .Range("B6").Formula = "='Daily Order'!B" &amp; rw.Row .Range("A3").Formula = "='Daily Order'!Q" &amp; rw.Row .Range("E36").Formula = "='Daily Order'!M" &amp; rw.Row .Range("E36").Formula = "='Daily Order'!Y" &amp; rw.Row .Range("E37").Formula = "='Daily Order'!L" &amp; rw.Row End With Next rw End Sub </code></pre> <p><strong>Types of errors:</strong></p> <p>I have spent considerable time trying to isolate out certain areas that I think might be troublesome, but I keep ending up with either 424 errors or 1004 errors.</p> <p>Again, I would greatly appreciate any help. Thank you!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USpnuts
    1. USngl5000
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POVBA Excel Generate Unique Pages From Template and List
      1. USIoannis
      1. VTFavorite
    1. COWhat is your system memory usage? You are creating thousands of sheets in this operation.
      singulars
      1. POVBA Excel Generate Unique Pages From Template and List
      1. USElysian Fields
    2. CO@enderland memory usage is not the issue (yet), my code is throwing errors on the first worksheet duplication. The errors all seem to be related to the new worksheet. When I try to act on it in anyway it tells me that it is not an object (by issuing a 1004 or 424 error)
      singulars
      1. POVBA Excel Generate Unique Pages From Template and List
      1. USngl5000
    3. COtry changing `worksheets` to `sheets` when you define temp/daily.
      singulars
      1. POVBA Excel Generate Unique Pages From Template and List
      1. USElysian Fields
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload