Add data rows to Bill of materials

[MaGNeTiCBLue]

Dear MrExcel users,

I'm looking for a small VBA macro for my Excel BOM template (Bill of Materials). What the macro should do is:

- Run automatically when the user saves the template
- Determine the first empty row in sheet Teamcenter Report. Row 1 is header info, all rows in between can be considered data rows.
- Switch focus to sheet BOM report.
- Copy the bottom row down in order to create an equal amount of data rows as in sheet Teamcenter report. Row 3 and 4 are the first two data rows. Row 4 should be copied down.

The last programming I did was on a Commodore 64 twenty years ago, so maybe someone can help me out

Kind regards,
Robin Bloemberg.

Teamcenter report

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 44px"><COL style="WIDTH: 544px"><COL style="WIDTH: 40px"><COL style="WIDTH: 61px"><COL style="WIDTH: 33px"><COL style="WIDTH: 61px"><COL style="WIDTH: 60px"><COL style="WIDTH: 104px"><COL style="WIDTH: 33px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">Home</TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">BOM Line</TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">Level</TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">Quantity</TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">bl_ItemRevision Master_Ownership_type</TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">Item Id</TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">Revision</TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">Item Rev Status</TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">bl_ItemRevision Master_Electrical</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD></TD><TD>10-00006/A;1-Box mount assy, bottom (view)</TD><TD style="TEXT-ALIGN: right">0</TD><TD></TD><TD>VG</TD><TD style="TEXT-ALIGN: right">10-00006</TD><TD>A</TD><TD></TD><TD>no</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD></TD><TD>10-00024/A;1-Gas panel mount assy (view) x 1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD>VG</TD><TD style="TEXT-ALIGN: right">10-00024</TD><TD>A</TD><TD></TD><TD>no</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD></TD><TD>10-00034/A;1-Gas panel weld assy (view) x 1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD>VG</TD><TD style="TEXT-ALIGN: right">10-00034</TD><TD>A</TD><TD></TD><TD>no</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD></TD><TD>10-00052/A;1-Bulkhead strip x 1</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">1</TD><TD>VG</TD><TD style="TEXT-ALIGN: right">10-00052</TD><TD>A</TD><TD></TD><TD>no</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD></TD><TD>10-00193/A;1-SS Tube Fitting, Female Elbow, 1/4 in. Tube OD x 1/8 in. Female NPT x 3</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD>CA</TD><TD style="TEXT-ALIGN: right">10-00193</TD><TD>A</TD><TD></TD><TD>no</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD></TD><TD>10-00099/A;1-Gasket x 1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD>VG</TD><TD style="TEXT-ALIGN: right">10-00099</TD><TD>A</TD><TD></TD><TD>no</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD></TD><TD>10-00190/A;1-Stainless Steel Tubing Insert, 1/4in. OD x 0.17in. ID x 3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD>CA</TD><TD style="TEXT-ALIGN: right">10-00190</TD><TD>A</TD><TD></TD><TD>no</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD></TD><TD>10-00036/A;1-Back shield assy (view) x 2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD>VG</TD><TD style="TEXT-ALIGN: right">10-00036</TD><TD>A</TD><TD></TD><TD>no</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD></TD><TD>10-00170/A;1-Lead, back shield x 1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD>CA</TD><TD style="TEXT-ALIGN: right">10-00170</TD><TD>A</TD><TD></TD><TD>no</TD></TR></TBODY></TABLE>


BOM report

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 80px"><COL style="WIDTH: 66px"><COL style="WIDTH: 80px"><COL style="WIDTH: 52px"><COL style="WIDTH: 122px"><COL style="WIDTH: 66px"><COL style="WIDTH: 94px"><COL style="WIDTH: 80px"><COL style="WIDTH: 108px"><COL style="WIDTH: 108px"><COL style="WIDTH: 94px"><COL style="WIDTH: 570px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>R</TD><TD>S</TD><TD>T</TD><TD>U</TD><TD>V</TD><TD>W</TD></TR><TR style="HEIGHT: 29px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; FONT-SIZE: 9pt; FONT-WEIGHT: bold">Level</TD><TD style="BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; FONT-SIZE: 9pt; FONT-WEIGHT: bold">Qty.</TD><TD style="BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; FONT-SIZE: 9pt; FONT-WEIGHT: bold">Total qty.</TD><TD style="BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; FONT-SIZE: 9pt; FONT-WEIGHT: bold">OT</TD><TD style="BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; FONT-SIZE: 9pt; FONT-WEIGHT: bold">Item ID</TD><TD style="BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; FONT-SIZE: 9pt; FONT-WEIGHT: bold">Rev.</TD><TD style="BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; FONT-SIZE: 9pt; FONT-WEIGHT: bold">Status</TD><TD style="BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; FONT-SIZE: 9pt; FONT-WEIGHT: bold">Elec.</TD><TD style="BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; FONT-SIZE: 9pt; FONT-WEIGHT: bold">Made of</TD><TD style="BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; FONT-SIZE: 9pt; FONT-WEIGHT: bold">Based on</TD><TD style="BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; FONT-SIZE: 9pt; FONT-WEIGHT: bold">Grote Beer
no.



</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; FONT-SIZE: 9pt; FONT-WEIGHT: bold">Title</TD></TR><TR style="HEIGHT: 29px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 12pt; FONT-WEIGHT: bold"></TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 12pt; FONT-WEIGHT: bold"></TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 12pt; FONT-WEIGHT: bold">Bill of materials</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 12pt; FONT-WEIGHT: bold"></TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 12pt; FONT-WEIGHT: bold"></TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 12pt; FONT-WEIGHT: bold"></TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 12pt; FONT-WEIGHT: bold"></TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 12pt; FONT-WEIGHT: bold"></TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 12pt; FONT-WEIGHT: bold"></TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 12pt; FONT-WEIGHT: bold"></TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 12pt; FONT-WEIGHT: bold"></TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 12pt; FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #f2f2f2; FONT-FAMILY: Arial; FONT-SIZE: 9pt; FONT-WEIGHT: bold">0</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #f2f2f2; FONT-FAMILY: Arial; FONT-SIZE: 9pt; FONT-WEIGHT: bold">=--'Teamcenter report'!D2</TD><TD style="BACKGROUND-COLOR: #f2f2f2; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f2f2f2; FONT-FAMILY: Arial; FONT-SIZE: 9pt; FONT-WEIGHT: bold">='Teamcenter report'!E2</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #f2f2f2; FONT-FAMILY: Arial; FONT-SIZE: 9pt; FONT-WEIGHT: bold">='Teamcenter report'!F2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #f2f2f2; FONT-FAMILY: Arial; FONT-SIZE: 9pt; FONT-WEIGHT: bold">='Teamcenter report'!G2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #f2f2f2; FONT-FAMILY: Arial; FONT-SIZE: 9pt; FONT-WEIGHT: bold">='Teamcenter report'!H2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #f2f2f2; FONT-FAMILY: Arial; FONT-SIZE: 9pt; FONT-WEIGHT: bold">='Teamcenter report'!I2</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #f2f2f2; FONT-FAMILY: Arial; FONT-SIZE: 9pt; FONT-WEIGHT: bold">='Teamcenter report'!J2</TD><TD style="BACKGROUND-COLOR: #f2f2f2; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f2f2f2; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #f2f2f2; FONT-FAMILY: Arial; FONT-SIZE: 9pt; FONT-WEIGHT: bold">='Teamcenter report'!K2</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #f2f2f2; FONT-FAMILY: Arial; FONT-SIZE: 9pt">=--'Teamcenter report'!C3</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt">=--'Teamcenter report'!D3</TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt">='Teamcenter report'!E3</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt">='Teamcenter report'!F3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt">='Teamcenter report'!G3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt">='Teamcenter report'!H3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt">='Teamcenter report'!I3</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt">='Teamcenter report'!J3</TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt">='Teamcenter report'!K3</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="BACKGROUND-COLOR: #f2f2f2; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="BACKGROUND-COLOR: #f2f2f2; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD><TD style="BACKGROUND-COLOR: #f0f0f0; FONT-FAMILY: Arial; FONT-SIZE: 9pt"></TD></TR></TBODY></TABLE>

 

[borgem]

Assuming that you want the data from the BOM Line column in the BOM report column K, this should do the trick.

Sub ExportToBOM()

Sheets("Teamcenter report").Select
LastRow = Range("B1").End(xlDown).Row
Range("B2:D" & LastRow).Copy

Sheets("BOM report").Select
Range("K3").Select
ActiveSheet.Paste

Sheets("Teamcenter report").Select
Range("E2:J" & LastRow).Copy

Sheets("BOM report").Select
Range("O3").Select
ActiveSheet.Paste

Sheets("Teamcenter report").Select
Range("K2:K" & LastRow).Copy

Sheets("BOM report").Select
Range("W3").Select
ActiveSheet.Paste

End Sub