If column value = 0 then copy cell in column A,B and C to another sheet below existing columns

[radeon187]

Good Day!

I would like to see if its possible to do the following via VBA:

1. Define a column header (header = result) in sheet1 to look at values
2. If value in result header = 0 copy contents from row for header "Make", "Model" and "ID"
3. Paste contents from above to sheet2 in rows with column headers "Make","Model" and "ID" below existing data in sheet2

Example sheet1:

RVTools_export_dfw5infvcn001_2020-03-24_15.09.19 (1).xlsx
	A	B	C	D
1	Make	Model	Result	ID
2	Ford	F150	1	9255
3	Honda	Accord	0	9265
4	Chevy	Bolt	1	8652
5	Lexus	IS	0	1927
6	Ford	Bronco	0	1025
7	Ford	F250	1	3952
8	Honda	Pilot	1	1257
9	Chevy	Blazer	1	2217
10	Lexus	ES	0	6364
Sheet1

Example sheet2:

RVTools_export_dfw5infvcn001_2020-03-24_15.09.19 (1).xlsx
	A	B	C
1	Make	Model	ID
2	Ford	F150	5467
3	Honda	Accord	5487
4	Chevy	Bolt	2125
5	Lexus	IS	5688
6	Ford	Bronco	6214
7	Ford	F250	3347
8	Honda	Pilot	4496
9	Chevy	Blazer	5568
10	Lexus	ES	2148
Sheet2

Example of desired result:

RVTools_export_dfw5infvcn001_2020-03-24_15.09.19 (1).xlsx
	A	B	C
1	Make	Model	ID
2	Ford	F150	5467
3	Honda	Accord	5487
4	Chevy	Bolt	2125
5	Lexus	IS	5688
6	Ford	Bronco	6214
7	Ford	F250	3347
8	Honda	Pilot	4496
9	Chevy	Blazer	5568
10	Lexus	ES	2148
11	Honda	Accord	9265
12	Lexus	IS	1927
13	Ford	Bronco	1025
14	Lexus	ES	6364
Sheet2

Keep in mind, I would like to designate everything by column header name. Thanks in advance!

 

[denzo36]

Hey, if I'm understanding this correctly you basically want to find the rows on sheet 1 that have a 0 in the header column and copy them to the bottom of your list on sheet 2?

If so, it's a simple case of applying a filter to sheet 1 on column 'header' where the filter value is 0. Then copying the result of the filter to a new area, remove the header and paste the result to the bottom of your list on sheet 2

 

[radeon187]

Hey, if I'm understanding this correctly you basically want to find the rows on sheet 1 that have a 0 in the header column and copy them to the bottom of your list on sheet 2?

If so, it's a simple case of applying a filter to sheet 1 on column 'header' where the filter value is 0. Then copying the result of the filter to a new area, remove the header and paste the result to the bottom of your list on sheet 2

Thats a great idea @denzo36! Is there vba code that I could use to accomplish this? Also, for the copy and paste from sheet1 to sheet2, I would need to specify the coumn headers where the data needs to be placed in sheet2 since the columns will vary.

 

[jolivanes]

If your pictures are a true reference of your actual sheets, try this.
Change referenced where required.

Sub Maybe()
Application.ScreenUpdating = False
With Sheets("Sheet1").Range("A1:D" & Range("A" & Rows.Count).End(xlUp).Row)
    .AutoFilter 3, 0
    .Columns(3).EntireColumn.Hidden = True
    .Offset(1).SpecialCells(12).Copy Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1)
End With
With ActiveSheet
    .AutoFilterMode = False
    .Columns(3).EntireColumn.Hidden = False
End With
Application.ScreenUpdating = True
End Sub

 

[jolivanes]

If the Headers are not in the same order, maybe this will do.
Change references if and where required.

Sub Maybe_2()
Dim mArr, i As Long, sh1 As Worksheet, sh2 As Worksheet
mArr = Array("Make", "Model", "ID")
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
Application.ScreenUpdating = False
    With sh1.Range("A1:D" & Range("A" & Rows.Count).End(xlUp).Row)
        .AutoFilter 3, 0
            For i = LBound(mArr) To UBound(mArr)
                Cells(1, sh1.Rows(1).Find(mArr(i), , , 1).Column).Offset(1).Resize(sh1.UsedRange.Rows.Count).SpecialCells(12).Copy _
                sh2.Cells(Rows.Count, sh2.Rows(1).Find(mArr(i), , , 1).Column).End(xlUp).Offset(1)
            Next i
    End With
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub

 

[jolivanes]

With sh1.Range("A1:D" & Range("A" & Rows.Count).End(xlUp).Row)

should be

With sh1.Range("A1:D" & sh1.Range("A" & Rows.Count).End(xlUp).Row)

I have to check yet but I don't think it needs to be

With sh1.Range("A1:D" & sh1.Range("A" & sh1.Rows.Count).End(xlUp).Row)