I am storing user entered values in tables 2, 3 and 4. The tables are fixed sizes, but tables 3 and 4 may not have any data. The user is prevented from entering any more data than the table size allows.
Table #1 Product Info AI3:AM3 (1 row)
Table #2 Labor Costs AO3:AT10 (1-8 rows)
Table #3 Materials Costs AV3:AY9 (0-7 rows)
Table #4 Inspected Product BA3:BC9 (0-7 rows)
I need to export the information in the tables into another spreadhseet. For testing purposes, I am using Sheet2. I need to loop through each table (in the order listed above) and copy the values to the appropriate columns on Sheet2, but the data in Table #1 has to be inserted into each row.
Copy Product Info, paste to first empty row in A:E. Copy first row in Labor Costs, paste to the same row in F:K
Repeat for rest of Labor Costs table
Copy Product Info, paste to first empty row in A:E. Copy first row in Materials Costs, paste to the same row in L:O
Repeat for rest of MaterialsCosts table
Copy Product Info, paste to first empty row in A:E. Copy first row in Inspected Product, paste to the same row in P:R
Repeat for rest of Inspected Product table
The results should look like this, assuming each table had two rows of data.
[TABLE="class: grid, width: 800, align: center"]
<TBODY>[TR]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[/TR]
[TR]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[TD]
[TD]
[TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[TD]
[TD]
[TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[TD]
[TD]
[/TR]
[TR]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[TD]
[TD]
[/TR]
</TBODY>[/TABLE]
I have been testing the following code for the first two tables, but it doesn't loop. I suck at looping!
Table #1 Product Info AI3:AM3 (1 row)
Table #2 Labor Costs AO3:AT10 (1-8 rows)
Table #3 Materials Costs AV3:AY9 (0-7 rows)
Table #4 Inspected Product BA3:BC9 (0-7 rows)
I need to export the information in the tables into another spreadhseet. For testing purposes, I am using Sheet2. I need to loop through each table (in the order listed above) and copy the values to the appropriate columns on Sheet2, but the data in Table #1 has to be inserted into each row.
Copy Product Info, paste to first empty row in A:E. Copy first row in Labor Costs, paste to the same row in F:K
Repeat for rest of Labor Costs table
Copy Product Info, paste to first empty row in A:E. Copy first row in Materials Costs, paste to the same row in L:O
Repeat for rest of MaterialsCosts table
Copy Product Info, paste to first empty row in A:E. Copy first row in Inspected Product, paste to the same row in P:R
Repeat for rest of Inspected Product table
The results should look like this, assuming each table had two rows of data.
[TABLE="class: grid, width: 800, align: center"]
<TBODY>[TR]
[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][TD]
J
[/TD][TD]
K
[/TD][TD]
L
[/TD][TD]
M
[/TD][TD]
N
[/TD][TD]
O
[/TD][TD]
P
[/TD][TD]
Q
[/TD][TD]
R
[/TD][/TR]
[TR]
[TD]
PI
[/TD][TD]
PI
[/TD][TD]
PI
[/TD][TD]
PI
[/TD][TD]
PI
[/TD][TD]
LC
[/TD][TD]
LC
[/TD][TD]
LC
[/TD][TD]
LC
[/TD][TD]
LC
[/TD][TD]
LC
[/TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
PI
[/TD][TD]
PI
[/TD][TD]
PI
[/TD][TD]
PI
[/TD][TD]
PI
[/TD][TD]
LC
[/TD][TD]
LC
[/TD][TD]
LC
[/TD][TD]
LC
[/TD][TD]
LC
[/TD][TD]
LC
[/TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
PI
[/TD][TD]
PI
[/TD][TD]
PI
[/TD][TD]
PI
[/TD][TD]
PI
[/TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
MC
[/TD][TD]
MC
[/TD][TD]
MC
[/TD][TD]
MC
[/TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
PI
[/TD][TD]
PI
[/TD][TD]
PI
[/TD][TD]
PI
[/TD][TD]
PI
[/TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
MC
[/TD][TD]
MC
[/TD][TD]
MC
[/TD][TD]
MC
[/TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
PI
[/TD][TD]
PI
[/TD][TD]
PI
[/TD][TD]
PI
[/TD][TD]
PI
[/TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
IP
[/TD][TD]
IP
[/TD][TD]
IP
[/TD][/TR]
[TR]
[TD]
PI
[/TD][TD]
PI
[/TD][TD]
PI
[/TD][TD]
PI
[/TD][TD]
PI
[/TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
IP
[/TD][TD]
IP
[/TD][TD]
IP
[/TD][/TR]
</TBODY>[/TABLE]
I have been testing the following code for the first two tables, but it doesn't loop. I suck at looping!
Code:
Sub Export_Product_Info()
Dim FirstBlankCell As Range
'Copy Product Info
Sheets("Sheet1").Select
'Selects data in Product Info table
Range("AI3:AM3").Select
Selection.Copy
'Pastes data in columns A thru E
Sheets("Sheet2").Select
Set FirstBlankCell = Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
FirstBlankCell.Activate
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
'#############################'
'Copy Labor Costs
Sheets("Sheet1").Select
'Selects data in Labor Costs table
Range("AO3:AT3").Select
Selection.Copy
'Pastes data in columns F thru K
Sheets("Sheet2").Select
Set FirstBlankCell = Range("F" & Rows.Count).End(xlUp).Offset(1, 0)
FirstBlankCell.Activate
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
End If
End Sub