Copy specific cells (from multiple sheets) to another sheet only when a specific cell value is greater than zero?

[mjaeger1]

Hi All,

Thanks for you help so far! Sorry if this is confusing. Doing my bets to express my challenge.

I am working on a master quoting tool for a customer. I have multiple sheets for multiple product sets, where I have vlookups pulling data in for columns R, S, T, respectively "part number", "Description", and "Quantity" on each of the 3 separate sheets.

I would like to have these 3 specific cells automatically copied to another 'master sheet' that will combine any row from each sheet that has a 'quantity' entered greater than zero, along with part number, description and quantity. If there is no quantity entered, it should be skipped and not copied.

How can this be done?

 

[DanteAmor]

Change the name of the sheets containing the products in the macro. Also change the name of your master sheet in the macro.
The macro assumes that you have titles in row 1 and the data starts in row 2.

Try this:

Sub Copy_specific_cells()
  Dim arr As Variant
  Dim sh As Worksheet, shM As Worksheet
  Dim i As Long
  
  Set shM = Sheets("Master")                  'Master sheet name
  arr = Array("Sheet1", "Sheet2", "Sheet3")   'Sheet names

  For i = 0 To UBound(arr)
    Set sh = Sheets(arr(i))
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    sh.Range("R1", sh.Range("T" & Rows.Count).End(3)).AutoFilter 3, ">0"
    sh.AutoFilter.Range.Offset(1).Copy shM.Range("A" & Rows.Count).End(3)(2)
    sh.ShowAllData
  Next
End Sub