Merge Data If Criteria Is Met

[Nichawla]

Hello!

I have built an ordering workbook in which 12 different markets have a designated sheet to place hardware orders. The sheets are titled:

• Netherlands
• Portugal
• Austria
• Italy
• Belgium
• Russia
• Ukraine
• Poland
• Spain
• Switzerland
• Czech Republic
• Slovakia

Each sheet has the exact same columns (I have data up to col AE/col 31.

I need to create a Master sheet which shows me a consolidated view of all countries orders info on 1 sheet. To have an order, there must be a value in col I (col9). Basically, if there is a value in column I of ANY sheet, I need that entire row returned to my Master sheet. I need this formula/macro to look at every sheet, see if there is a value in col I, and return the row to the Master sheet. The master sheet should show me all rows for every sheet where col I is populated. Any ideas?? Let me know if you need more info.

 

[kweaver]

Welcome to Mr Excel

It might help if you posted some illustrative data.

 

[kweaver]

If you have a sheet called Markets with each of the 12 markets listed in A1 to A12, then a sheet called Orders with row 1 having headers (and each market sheet has row 1 with headers),
try this:

Sub MarketOrders()
Dim LR As Long, LR2 As Long, i As Integer, c As Integer, mkt As String
For c = 1 To 12
mkt = Sheets("Markets").Range("A" & c)
LR = Sheets(mkt).Range("A" & Rows.Count).End(xlUp).Row
If LR <> 1 Then
For i = 2 To LR
LR2 = 1 + Sheets("Orders").Range("A" & Rows.Count).End(xlUp).Row
If Not IsEmpty(Sheets(mkt).Range("I" & i)) Then
  Sheets("Orders").Range("A" & LR2) = mkt
  Sheets(mkt).Range("A" & i & ":J" & i).Copy Sheets("Orders").Range("B" & LR2 & ":K" & LR2)
End If
Next i
End If
Next c
End
End
End Sub

There's probably a better method to use to grab all of the rows with data in the J column, but I don't know how to do that in VBA.
Someone will offer a better approach, but at least I think this works.

 

[kweaver]

I believe this is much improved:

Sub MarketOrders2()
Dim LR As Long, LR2 As Long, i As Integer, c As Integer, mkt As String, fr As Long
For c = 1 To 12
 mkt = Sheets("Markets").Range("A" & c)
 LR2 = 1 + Sheets("Orders").Range("A" & Rows.Count).End(xlUp).Row ' next row in Orders
 LR = Sheets(mkt).Range("A" & Rows.Count).End(xlUp).Row  ' last row of market
If LR <> 1 Then
    Sheets(mkt).Activate
    Sheets(mkt).Range("A1:J" & LR).Select
    Selection.AutoFilter
    Sheets(mkt).Range("$A$1:$J$" & LR).AutoFilter Field:=9, Criteria1:="<>"
    Sheets(mkt).AutoFilter.Range.Offset(1, 0).Copy Sheets("Orders").Range("B" & LR2)
    fr = Sheets(mkt).AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1
    Sheets("Orders").Range("A" & LR2 & ":A" & LR2 + fr - 1) = mkt
    Sheets(mkt).AutoFilterMode = False
 Else
 End If
Next c
End Sub

 

[Nichawla]

Thank you so much!! I actually figured out how to write an append query using Power Query Editor which worked for me! I am just trying to figure out how I can add data to the new master table (where the other tables append to) and have that data be added to the source tables. Does anyone know how to do this?