Macro or Formula? Sorry for my lack of knowledge, in over my head and need help.

[Skyman014j]

All,

Required to work with an Excel and need a solution. Worked with formulas in the past but not sure if they will work for this.

I have 2 sheets and need to auto populate the second sheet with rows and columns from sheet 1 (A2:A100000 to D2:D100000) when column "H" is "Yes" (from a drop down menu). Is there a formula for this? Or is a macro better suited? Any help would be greatly appreciated.

 

[dreid1011]

Welcome to the forum.

It may be as simple as this:

=IF(Sheet1!$H2="Yes",Sheet1!A2,"")

Place this in A2 on your second sheet, copy across to D2, then copy down to 100000.

If not, please post a few rows of data and the expected results for those rows.

 

[Skyman014j]

I really appreciate the above help!!! So this worked nearly perfectly except that when it copies the cells to the second sheet, it leaves blank rows from those rows that did not meet the "Yes" requirement. Any way to modify the above so the data copied is clean and not bothered with blank rows?

 

[Skyman014j]

Any suggestions to edit the formula so it does not leave blank rows?

 

[NoSparks]

Could try filter and copy using VBA, something along the lines of

Sub Skyman()
With Sheets("Sheet1").Cells(1).CurrentRegion
    .AutoFilter field:=8, Criteria1:="Yes"
    .Columns("A:D").Offset(1).Copy Sheets("Sheet2").Cells(2, 1)
    .AutoFilter
End With
End Sub

 

[Skyman014j]

That worked once. Now its gives me a debug 1004 message and highlights the .AutoFilter field:=8, Criteria1:="Yes" line.

 

[NoSparks]

Interpret a 1004 message as Excel saying "I can't find what I'm being asked to find"
which is usually caused by something not existing or being asked for the wrong way.
It worked the first time... asked correctly and existing
but not the second... asked same way, so not found... whats changed on the sheet ?

I can't re-create the error on my test setup without inserting some rows or columns.

 

[Skyman014j]

I genuinely appreciate the help. I understand what you are explaining. I wish had answer for you, but I can't recall changing anything with the sheet. I only went to run the macro again and it didn't work. I even created a new test execl document and received the same error.

 

[Skyman014j]

Opened new blank workbooks and attempted to use this macro with bare bones data. Still receiving the error message.

 

[NoSparks]

My original suggestion was based on your original description of data starting in A2.
I made the assumption that row 1 would be headers and that there would be no blank columns ahead of H in order to (over?) simplify with the use of currentregion. Although, if it worked once without error it should have worked a second time.

Try this, you can manually adjust the header row in the code if need be.
I suspect the 100000 bottom row is to make sure all used rows are included.
This uses the last used cell in column H as the bottom row, change it to some other column if you know it will be longer, or hard code the number.
This specifically includes all columns up to and including H in the filter range.

Good luck.

Sub Skyman_v2()

Dim lastRow As Long
Dim headerRow As Long
Dim filtRng As Range

With Sheets("Sheet1")
    headerRow = 1      '<~~~ CHANGE TO SUIT
    lastRow = .Cells(Rows.Count, "H").End(xlUp).Row
    Set filtRng = .Range("A" & headerRow & ":H" & lastRow)
End With

With filtRng
    .AutoFilter Field:=8, Criteria1:="Yes"
    .Columns("A:D").Offset(1).Copy Sheets("Sheet2").Cells(2, 1)
    .AutoFilter
End With

End Sub

If you still have issues share a copy of the workbook you are testing with.