Combining multiple workbooks into new worksheet with smiliar columns ---VBA code

ExcelGeek5038

New Member
Joined
Apr 12, 2021
Messages
13
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi
I have multiple excel workbooks which have exactly same columns (Screenshot attached) and worksheet. Only data in the rows and number of rows will be different. I need to combine these workbooks into one excel worksheet with similar column heading only combing rows.
I am attaching XLBB code for one workbook here for example.
Can anyone write VBA code for this?
thanks
2-PL-JANU-20-1045-01AX (JAN-1-PL-1).xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
1Feature Attributes
2Corrosion Circuit*Piping Tag/ Line Number*Feature ClassFeature Type*Feature ID*Feature Size, inchesDrawing NumberStatus IndicatorDesign CodeDesign Code YearMaterial TypeMaterial SpecificationMaterial GradeDesign PressureDesign Pressure UnitDesign TemperatureDesign Temperature UnitAllowable StressAllowable Stress UnitOuter DiameterOuter Diameter UnitInside Diameter Inside Diameter UnitJoint FactorCorrosion Allowance, mmInsulation StatusScheduleNWT, mmPiping FormulaMechanical AllowanceDesign FactorTemperature FactorCONC(ALARM1), mmMAWT(ALARM2), mmAccess
3JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)ReducerF016 x 30000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steelA 234Grade WPB37.113.221.19
4JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)WeldsF0230000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steel  35.491.80.63
5JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)ReducerF033 x 20000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steelA 234Grade WPB35.491.80.63
6JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)WeldsF0420000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steel  35.541.850.43
7JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)ElbowF0520000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steelA 234Grade WPB35.541.850.43
8JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)WeldsF0620000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steel  35.541.850.43
9JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)FlangeF0720000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steelA 105 35.541.850.43
10JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)ValveHV-060720000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steelA 216Grade WCB35.541.850.43
11JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)FlangeF0820000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steelA 105 35.541.850.43
12JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)WeldsF0920000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steel  35.541.850.43
13JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)PipeF1020000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steelA 106Grade B35.541.850.43
14JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)WeldsF1120000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steel  35.541.850.43
15JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)FlangeF1220000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steelA 105 35.541.850.43
16JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)FlangeF1320000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steelA 105 35.541.850.43
17JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)WeldsF1420000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steel  35.541.850.43
18JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)PipeF1520000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steelA 106Grade B35.541.850.43
19JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)WeldsF1620000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steel  35.541.850.43
20JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)FlangeF1720000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steelA 105 35.541.850.43
21JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)ValveHV-060820000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steelA 216Grade WCB3
22JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)FlangeF1820000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steelA 105 35.541.850.43
23JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)WeldsF1920000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steel  35.541.850.43
24JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)ElbowF2020000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steelA 234Grade WPB35.541.850.43
25JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)WeldsF2120000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steel  35.541.850.43
26JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)FlangeF2220000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steelA 105 35.541.850.43
27JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)FlangeF2320000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steelA 105 35.541.850.43
28JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)WeldsF2420000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steel  35.541.850.43
29JAN-1-PL-12-PL-JANU-20-1045-01AXTest point (TP)PipeF2520000RO-S-DG-DG09-PI-ISO0383-001-A01Carbon steelA 106Grade B35.541.850.43
Piping Features
Cell Formulas
RangeFormula
L3:L29L3=IF(D3="Pipe","A 106",IF(D3="Mitred Elbow","A 106",IF(D3="Sweepolet","A 105",IF(D3="Thread","",IF(D3="Reducing Tee","A 234",IF(D3="Welds","",IF(D3="Expansion Bellow","",IF(D3="Reinforcement Pad","",IF(D3="Threaded Joint","",IF(D3="Cap","A 234",IF(D3="Elbow","A 234",IF(D3="Tee","A 234",IF(D3="Reducer","A 234",IF(D3="Weldolet","A 105",IF(D3="Flange","A 105",IF(AND(D3="Valve",F3<2),"A 105",IF(AND(D3="Valve",F3>=2),"A 216","Error")))))))))))))))))
M3:M29M3=IF(D3="Pipe","Grade B",IF(D3="Mitred Elbow","Grade B",IF(D3="Sweepolet","",IF(D3="Thread","",IF(D3="Reducing Tee","Grade WPB",IF(D3="Welds","",IF(D3="Expansion Bellow","",IF(D3="Reinforcement Pad","",IF(D3="Threaded Joint","",IF(D3="Cap","Grade WPB",IF(D3="Elbow","Grade WPB",IF(D3="Tee","Grade WPB",IF(D3="Reducer","Grade WPB",IF(D3="Weldolet","",IF(D3="Flange","",IF(AND(D3="Valve",F3<2),"",IF(AND(D3="Valve",F3>=2),"Grade WCB","Error")))))))))))))))))
Cells with Data Validation
CellAllowCriteria
AI3:AI29List=access
H3:H29List=statusIndicators
S3:S29List=pressure
O3:O29List=pressure
Q3:Q29List=temp
W3:W29List=corrosionAllowance
U3:U29List=corrosionAllowance
Z3:Z29List=insulationStatus
D15:D29List=featureType
D3:D13List=featureType
A3:A808List=ccTags
B3:B808List=piping
C3:C1259List=featureClass
 

Attachments

  • Capture3.JPG
    Capture3.JPG
    47.9 KB · Views: 6

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Forum statistics

Threads
1,176,025
Messages
5,900,962
Members
434,861
Latest member
Coffee_in_a_jiffy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top