use formula to pull cells with certain values to another work sheet

[jwevans]

I have an inventory spreadsheet that keeps a running total of inventory. I need to add a totals page that tracks sales, for re-order info. The Inventory worksheet shows sales as a negative number in column H. I would like to capture this number, only for part numbers that actually have sales for the day on another sheet that tracks the daily sales for a week.

INVENTORY MANAGEMENT
Code	Description	PurchQty	PurchCost	AvCo	SaleQty	SaleValue	Profit	InventoryQty	InventoryValue
31P-735	Heavy Duty	5​	5.00​	1.00​	-3​	-90.00​	-87.00​	2​	2.00​
315-735	Heavy Duty	1​	1.00​	1.00​	0​	0.00​	0.00​	1​	1.00​
31P-950	Heavy Duty	2​	2.00​	1.00​	-1​	-12.00​	-11.00​	1​	1.00​
315-950	Heavy Duty	1​	1.00​	1.00​	0​	0.00​	0.00​	1​	1.00​
3EE-420	Heavy Duty	2​	2.00​	1.00​	0​	0.00​	0.00​	2​	2.00​
3ET-525	Heavy Duty	2​	2.00​	1.00​	0​	0.00​	0.00​	2​	2.00​
3EH-	Heavy Duty	1​	1.00​	1.00​	0​	0.00​	0.00​	1​	1.00​
8U1	Heavy Duty	0​	0.00​	0.00​	0​	0.00​	0.00​	0​	0.00​
1-650	Heavy Duty	1​	1.00​	1.00​	0​	0.00​	0.00​	1​	1.00​
2-800	Heavy Duty	1​	1.00​	1.00​	0​	0.00​	0.00​	1​	1.00​
3-	Heavy Duty	1​	1.00​	1.00​	0​	0.00​	0.00​	1​	1.00​
4D-1000	Heavy Duty	1​	1.00​	1.00​	0​	0.00​	0.00​	1​	1.00​
4DL+-950	Heavy Duty	1​	1.00​	1.00​	0​	0.00​	0.00​	1​	1.00​
8D-1400	Heavy Duty	1​	1.00​	1.00​	0​	0.00​	0.00​	1​	1.00​
8D WATERFLL TERMINAL	Heavy Duty	1​	1.00​	1.00​	0​	0.00​	0.00​	1​	1.00​
		0​	0.00​	0.00​	0​	0.00​	0.00​	0​	0.00​

 

[KasperC]

Hello,

I tried to make something from what I understood from your post. If this does not work as wanted, please provide a more detailed description of what you need - best done through a copy of your workbook (de-sensitized if necessary).

Try this on a copy of your workbook:

Private Sub jwevans_sub()
    Dim wb As Workbook
    Dim ws As Worksheet, nws As Worksheet
    Dim Lrow As Long
    Dim r1 As Range, r2 As Range, r3 As Range, mr As Range
    Dim sheetname As String
  
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Sheet1")        '<-----  Insert the sheetname of the sales data here "Sheetname"
  
    Application.ScreenUpdating = False
  
    sheetname = "Sales summary Week " & WorksheetFunction.WeekNum(Now, vbMonday) - 1
  
    Sheets.Add.Name = sheetname
  
    Set nws = wb.Worksheets(sheetname)
    With nws
       .Range("A1") = "Code"
       .Range("B1") = "SaleQty"
       .Range("C1") = "InventoryQty"
       .Range("D1") = "InventoryValue"
    End With
  
    ws.Range("H2").AutoFilter Field:=6, Criteria1:="-*"
  
    Lrow = ws.Cells(Rows.Count, 8).End(xlUp).Row
  
    Set r1 = ws.Range("C3:C" & Lrow)
    Set r2 = ws.Range("H3:H" & Lrow)
    Set r3 = ws.Range("K3:L" & Lrow)
    Set mr = Union(r1, r2, r3)
  
    mr.SpecialCells(xlCellTypeVisible).Copy
    nws.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
    nws.Columns.AutoFit
  
    ws.ShowAllData
  
    nws.Activate
    Application.ScreenUpdating = True
  
End Sub