Using IF statement > 0 to copy data from separate worksheet - Need help - Brain Fried

[jclegg42002]

So I am stuck and I know this has to be an easy fix... I have always used this forum (for years) because it actually has smart people running it! Could anyone help?

Sheet1 contains the following data starting at row 9 (to row 80):

Bid Unit	Short Description	Unit	Qty	Unit Rate (1)	Total	Long Description	Supplier Response Notes/Comments
WNI-100	Install / Mount Antenna or Integrated Antenna	Per Node		$304.62​
WNI-105	Install / Mount Additional Antenna or Integrated Antenna	Per Antenna		$257.75​
WNI-110	Install / Mount Radio	Per Node		$275.33​

if QTY is > 0 (aka 1 or whatever numeric character)

I want to copy that whole row to Sheet2 - I am trying to capture the Bid Unit / Short Description / Unit / Qty / Total / Supplier Comments (this is for change order requests, and I figure copying the whole row would just be easiest)

Can someone make me feel stupid real fast on how I would reach this goal?
Thanks ahead of time!!!!

 

[mumps]

Try this macro:

Sub CopyRows()
    Application.ScreenUpdating = False
    With Cells(9, 1).CurrentRegion
        .AutoFilter 4, ">0"
        ActiveSheet.AutoFilter.Range.Offset(1).Copy Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Offset(1)
    End With
    Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[jclegg42002]

5_MATRIX_COR.xlsb
	A	B	C	D	E	F	G
9	Bid Unit	Short Description	Unit	Qty	Unit Rate (1)	Total	Supplier Response Notes/Comments
10	WNI-100	Install / Mount Antenna or Integrated Antenna	Per Node		$304.62
11	WNI-105	Install / Mount Additional Antenna or Integrated Antenna	Per Antenna		$257.75
12	WNI-110	Install / Mount Radio	Per Node		$275.33
13	WNI-115	Install / Mount Additional Radio	Per Radio		$222.60
14	WNI-120	Install Fiber Jumper (Fiber Demarc to Radio)	Foot	$1.00	$4.69	$4.69	testies
15	WNI-125	Install Power Circuit Load Side (Electrical Panel to Radio)	Foot		$4.69
16	WNI-130	Install Power Circuit Line Side (Power Co. to Electrical Panel)	Foot		$5.86
17	WNI-135	Install RF Jumper Cable (Antenna to Radio)	Foot		$5.86
18	WNI-140	Install / Mount Outdoor Rectifier	Per Node		$152.31
19	WNI-145	Install / Mount Outdoor Additional Rectifier	Per Rect.		$128.88
20	WNI-150	Install / Mount Fiber Mux or Box	Per Mux/Box		$152.31
21	WNI-155	Install Integrated Antenna/Antenna/Radio Stealth or Shroud up to 3 Radios	Per Node		$468.64
22	WNI-160	Install / Mount Meter Socket & or Load Center	Per Node		$205.03
23	WNI-165	Install Utility Pedestal	Per Node		$351.48
24	WNI-170	Install Ground Rod	Per Rod		$58.58
25	WNI-175	Install H-Frame	Per Node		$503.79
26	WNI-180	Install Splitter/Di-Tri-Quad plexer/filter	Per Device		$70.30
27	WNI-185	Install Ground Mounted Cabinet or Shroud	Per Node		$339.76
28	WNI-190	Mobilization - New Build when New/Replaced Pole by Supplier	Per Node		$1,591.85
29	WNI-195	Mobilization - New Build when using Existing Pole	Per Node		$1,082.46
30	WNI-200	Mobilization - Overlay/Modification	Per Node		$668.58
31	WNI-205	Mobilization - Repair/Troubleshoot	Per Node	$1.00	$318.37	$318.37	tesies2
32	WNI-210	Mobilization - Adder Install Above Power	Per Node		$573.07
33	WNI-215	Mobilization - Adder Directional Bore	Per Node		$2,027.59
34	WNI-220	Mobilization - Adder Mechanical Trencher	Per Node		$636.74
35	WNI-225	Mobilization - Adder Reinstate Asphalt or Concrete If Using Machinery	Per Node		$795.92
36	WNI-230	Mobilization - Adder Long Distance >100 mile Radius VZW Warehouse to Site	Per Node		$732.25
37	WOS-100	General Labor	Hr		$61.27
38	WOS-105	Electrician or Tower Tech	Hr		$95.60
39	WOS-110	General Operative & Bucket Truck	Hr		$110.48
40	PT-100	Permit Supplier Submit/Pickup All	Per Node		$527.22
41	PT-105	Permit Supplier Pickup All	Per Node		$205.03
42	PT-110	Permit Supplier Submit/Pickup Electrical or Trenching	Per Node		$292.90
43	P-100	Install Direct Bury Pole (Wood, Concrete, Steel, Composite) new location	Per Node		$966.57
44	P-105	Replace Direct Bury Pole (Wood, Concrete, Steel, Composite) existing location	Per Node		$1,318.05
45	P-110	Install Anchor Base Pole (Concrete, Steel, Composite) on new foundation	Per Node	$1.00	$790.83	$790.83
46	P-115	Remove Anchor Base Pole (Concrete, Steel, Composite)	Per Node		$556.51
47	P-120	Replace Anchor Base Pole (Concrete, Steel, Composite) reuse foundation	Per Node		$1,171.60
48	P-125	Install Pole Anchor Base Concrete Foundation	Per Node		$4,686.41
49	P-130	Install Pole Anchor Base Precast Concrete Foundation	Per Node		$2,929.00
50	P-135	Replace Pole Foundation	Per Node		$6,150.91
51	P-140	Install Helical Anchor Foundation	Per Node		$1,113.02
52	P-145	Adder Rock Excavation for direct bury pole or foundation	Foot		$76.15
53	P-150	Install or Replace Photocell	Per Node	$1.00	$70.30	$70.30
54	P-155	Install New Light Fixture	Per Node		$199.17
55	P-160	Replace Light Fixture	Per Node		$246.04
56	WDC-100	Construction – New Underground 1x2" HDPE (Mechanical) - 6" to 12" cover	Foot		$5.86
57	WDC-101	Construction – New Underground 1x2" HDPE (Mechanical) - 13" to 24" cover	Foot		$7.03
58	WDC-102	Construction – New Underground 1x2" HDPE (Mechanical) - 25" to 36" cover	Foot		$8.20
59	WDC-103	Remove and Reinstate Asphalt (2" Increments)	Sq Ft		$15.23
60	WDC-104	Remove and Reinstate Concrete (2" Increments)	Sq Ft		$15.23
61	WDC-105	Construction – New Underground 1x2" HDPE (Bore)	Foot		$11.21
62	WDC-106	Place Handhole (up to 3' x 3')	Each		$527.22
63	WDC-120	Construction – Direct Buried	Foot		$7.03
64	WDC-121	Construction - New Underground MicroTrench	Foot		$21.09
65	WDC-130	Construction Hand Dig - Flex Pipe or Conduit - up to 18" cover	Foot		$8.79
66	WDC-131	Construction Hand Dig - Flexible Pipe or Conduit - 19" to 24" cover	Foot		$9.67
67	WDC-132	Construction Hand Dig - Flexible Pipe or Conduit - 25" to 36" cover	Foot		$10.54
68	WDC-133	Construction Hand Dig Adder - Flex Pipe or Conduit - add'l depth in 6" increments	Foot		$2.34
69	WDC-200	Construction - Existing Underground (VZ owned/3rd party) Include Microduct	Foot		$4.10
70	WDC-300	Construction - Install Aerial Fiber Cable (New/Overlash)	Foot		$2.34
71	WDC-500	Fiber Cable Prep/Splicing/Testing (Loose Tube)	Fiber		$36.32
72	WDC-510	Fiber Cable Prep/Splicing/Testing (Ribbon)	Fiber		$30.46
73	WDMISC-1	New Underground (Mechanical) through Rock in 6" increments (Adder WDC-1xx)	Foot		$11.72
74	WDMISC-2	New Underground (Bore) through Rock (Adder WDC-105)	Foot		$36.98
75	WDMISC-3	Fiber Cable & Tracer Wire Installation (Adder WDC-1xx/WDC-200)	Foot		$1.76
76	WDMISC-4	Microduct Installation (Adder WDC-1xx)	Foot		$2.64
77	WDMISC-5	Microcable Installation within Microduct (Adder WDC-120/WDMISC-4)	Foot		$2.05
78	WDE-100	Fiber Drop Design Underground	Per Drop		$2,226.04
79	WDE-105	Fiber Drop Design Aerial	Per Drop		$2,226.04
Sheet1

Cell Formulas
Range	Formula
F10:F79	F10	=D10*E10

 

[jclegg42002]

5_MATRIX_COR.xlsb
	A	B	C	D	E	F	G	H
2	MATRIX CHANGE ORDER FORM
3
4	Project Name :	GN INDY ####		Purchase Order#	NMIK123456
5
6	Date:	3/21/2020		Change Order Amount (CO)	$ 1,184.18
7
8	Original Contract Amount (OCA)	$11,111.00
9
10	Total Change Order to Date (COTD)	$0.00
11	(Total for all CO's to date, but not including this one)
12
13	THIS CHANGE ORDER AMOUNT (CO)	$1,184.18
14
15	Revised Contract Amount	$12,295.18
16
17	This Change Order is Necessary Due to:
18	If the rows are > 0 I want them to display here.
19	and here
20	and here
21	and here
Sheet2

Cell Formulas
Range	Formula
B4	B4	=Sheet1!C3
E4	E4	=Sheet1!C5
B6	B6	=Sheet1!C7
E6	E6	=Sheet1!F81
B8	B8	=Sheet1!C6
B13	B13	=E6
B15	B15	=SUM(B8+B10+B13)

 

[jclegg42002]

does this help any?

 

[Fluff]

What version of Xl are you using?

 

[jclegg42002]

2016

 

[Fluff]

Thanks for that, if you modify you account seetings you can display that under your avatar, so people don't have to ask every time.

How about

+Fluff.xlsm
	A	B	C	D	E	F	G	H
1
2	MATRIX CHANGE ORDER FORM
3
4	Project Name :	0		Purchase Order#	0
5
6	Date:	0		Change Order Amount (CO)	0
7
8	Original Contract Amount (OCA)	0
9
10	Total Change Order to Date (COTD)	0
11	(Total for all CO's to date, but not including this one)
12
13	THIS CHANGE ORDER AMOUNT (CO)	0
14
15	Revised Contract Amount	0
16
17	This Change Order is Necessary Due to:
18	WNI-120	Install Fiber Jumper (Fiber Demarc to Radio)	Foot	1	4.7	4.686405	testies
19	WNI-205	Mobilization - Repair/Troubleshoot	Per Node	1	318	318.3699	tesies2
20	P-110	Install Anchor Base Pole (Concrete, Steel, Composite) on new foundation	Per Node	1	791	790.8309	0
21	P-150	Install or Replace Photocell	Per Node	1	70	70.29608	0
22
23
24
Sheet2

Cell Formulas
Range	Formula
B4	B4	=Sheet1!C3
E4	E4	=Sheet1!C5
B6	B6	=Sheet1!C7
E6	E6	=Sheet1!F81
B8	B8	=Sheet1!C6
B13	B13	=E6
B15	B15	=SUM(B8+B10+B13)
A18:G23	A18	=IFERROR(INDEX(Sheet1!A$10:A$100,AGGREGATE(15,6,(ROW(Sheet1!A$10:A$100)-ROW(Sheet1!A$10)+1)/(Sheet1!$D$10:$D$100>0),ROWS(A$18:A18))),"")

 

[jclegg42002]

works flawlessly!!! thank you so much!

 

[jclegg42002]

Updated profile, my apologies for that... also is there any way to force the return values to come in as currency? When I use your formula it changes the cell formatting?