Combine Data from multiple into one master sheet

Question

I'm working on a sheet to help track the inventory of packaging supplies that we use. I want to take anything from multiple sheets that has the reorder column as "reorder" and put them into one sheet with the item number, the item name, description, vendor and reorder quantity and place them all on a single sheet. this will make it easier for us to submit an order to purchasing and hopefully streamline the process.

I am pretty novice in Excel so I'm not even sure what to try. Any help would be greatly appreciated.

Answer 1

Did you include the End Sub in the code that you copied and pasted?

Answer 2

Hi,

Follow the steps shown in this video - Power Query #08: Import Multiple Excel Sheets From Multiple Excel Workbooks.

Answer 3

So I put this in, and it gives me a Compile Error: Expected End Sub. Not sure what that means or what to do to correct it.

Answer 4

You can used advanced filter: https://www.bing.com/videos/search?q=advanced+filter&view=detail&mid=A17932C1EF0F8FDB5D43A17932C1EF0F8FDB5D43&FORM=VIRE

Answer 5

If you put the following macro in a new workbook and then run it, it will ask you to select the workbook containing the inventory and it will then populate sheet 1 of the new workbook with the required data from each of the sheets of the workbook containing the inventory where REORDER appears in column A of the sheet.

Dim targetbook As Workbook

Dim sourcebook As Workbook

Dim sourcesheet As Worksheet

Dim FD As FileDialog

Dim strSource As String

Dim i As Long, j As Long, r As Long

Sub MakeOrderBook()

Set targetbook = ActiveWorkbook

With targetbook.Sheets(1).Range("A1")

.Offset(0, 0) = "Item No." 

.Offset(0, 1) = "Item Name" 

.Offset(0, 2) = "Description" 

.Offset(0, 3) = "Vendor" 

.Offset(0, 4) = "Reorder Quantity" 

End With

r = 1

Set FD = Application.FileDialog(msoFileDialogFilePicker)

With FD

.Filters.Add "Excel Workbooks", "\*.xlsx" 

.Title = "Select the Inventory Workbook" 

.AllowMultiSelect = False 

If .Show = -1 Then 

    strSource = .SelectedItems(1) 

Else 

    MsgBox "You did not select the workbook containing the inventory" 

    Exit Sub 

End If 

End With

Set FD = Nothing

Set sourcebook = Workbooks.Open(strSource)

With sourcebook

For i = 1 To .Sheets.Count 

    With .Sheets(i).Range("A1") 

        For j = 1 To .CurrentRegion.Rows.Count - 1 

            If .Offset(0, j) = "REORDER" Then 

                targetbook.Sheets(1).Range("A1").Offset(r, 0) = .Offset(j, 1) 

                targetbook.Sheets(1).Range("A1").Offset(r, 1) = .Offset(j, 2) 

                targetbook.Sheets(1).Range("A1").Offset(r, 2) = .Offset(j, 3) 

                targetbook.Sheets(1).Range("A1").Offset(r, 3) = .Offset(j, 5) 

                targetbook.Sheets(1).Range("A1").Offset(r, 4) = .Offset(j, 9) 

                r = r + 1 

            End If 

        Next j 

    End With 

Next i 

sourcebook.Close False 

End With

End Sub