VBA to extract certain data from multiple sheets to a master sheet

[white95gt]

Hello,

I have a workbook with more than 20 sheets, each sheet is the same, its a bill of material for certain part #'s. I would like to extract the BOM from each sheet to a single sheet.

A1 has kit part #
A2 colum qty.
B2 parts

Each sheet has 1 kit part #, and 3 parts to make the kit. Therefore A3 - A5 is quantities of parts, B3- B5 is part numbers.

Is it possible to extract all the kit part #, and BOM from each sheet to one sheet?

I would insert an example however i dont know how.

Thanks,
Jon

 

[Fluff]

How about

Sub GetBomDetails()

   Dim ws As Worksheet
   
   Sheets.Add(Sheets(1)).Name = "Details"
   For Each ws In Worksheets
      If Not ws.Name = "details" Then
         ws.Range("A1").Copy Sheets("Details").Range("A" & Rows.count).End(xlUp).Offset(1)
         ws.Range("A3:B5").Copy Sheets("Details").Range("A" & Rows.count).End(xlUp).Offset(1)
      End If
   Next ws
End Sub

 

[white95gt]

Thank you, works perfectly.

Is it possible to put the kit part # in column a, and the BOM qty' and parts in B and C?


Jon

 

[Fluff]

Try

Sub GetBomDetails()

   Dim ws As Worksheet
   
   Sheets.Add(Sheets(1)).Name = "Details"
   For Each ws In Worksheets
      If Not ws.Name = "details" Then
         ws.Range("A1").Copy Sheets("Details").Range("B" & Rows.count).End(xlUp).Offset(1, -1)
         ws.Range("A3:B5").Copy Sheets("Details").Range("B" & Rows.count).End(xlUp).Offset(1)
      End If
   Next ws
End Sub

 

[white95gt]

Wow it works perfectly, thank your for you quick responses.

Jon

 

[Fluff]

Glad to help & thanks for the feedback