VBA need help- Copy Rows if Cell contains - expanded

[jdorfma]

Hello all!

I am building out a workbook where every sheet is for a different stage of a software installation. I am trying to aggregate the steps that fail by copying my fail rows into a summary sheet. I finally got them to pull, but they are pulling into the new sheet on the same row # as they are located in the original sheet.

Here is what I am using now:

Option Explicit

Sub Test()

Dim Cell As Range

With Sheets(15)
' loop column H untill last cell with value (not entire column)
For Each Cell In .Range("D1:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
If Cell.Value = "Fail" Then
' Copy>>Paste in 1-line (no need to use Select)
.Rows(Cell.Row).Copy Destination:=Sheets(2).Rows(Cell.Row)
End If
Next Cell
End With

End Sub




I need=
How can I modify to:
Pull row that has cell containing "Fail"
Copy row into master starting at Row 2 and consecutively down without overwriting
Run across all sheets- they are named per step of install - do i need to rename to "sheet1, sheet2, etc"

 

[Joe4]

Welcome to the Board!

Let's start here:

Copy row into master starting at Row 2 and consecutively down without overwriting

Does the data always have an entry in column A?
If so, try changing this line:

.Rows(Cell.Row).Copy Destination:=Sheets(2).Rows(Cell.Row)

to this:

.Rows(Cell.Row).Copy Destination:=Sheets(2).Cells(Rows.Count,"A").End(xlUp).Offset(1,0)

This should paste it to the next available row on Sheet2.

 

[jdorfma]

Welcome to the Board!

Let's start here:


Does the data always have an entry in column A?
If so, try changing this line:

.Rows(Cell.Row).Copy Destination:=Sheets(2).Rows(Cell.Row)

to this:

.Rows(Cell.Row).Copy Destination:=Sheets(2).Cells(Rows.Count,"A").End(xlUp).Offset(1,0)

This should paste it to the next available row on Sheet2.

A great start, thank you! I'm self teaching VBA to myself, so it's a lot of trial and error but now I have a project applying pressure.

 

[Joe4]

A great start, thank you! I'm self teaching VBA to myself, so it's a lot of trial and error but now I have a project applying pressure.

You are welcome.

So, did that fix that issue?

 

[vcoolio]

Hello JDorfma,

Further to Joe's suggestion, why don't you automate the AutoFilter on each worksheet(excluding the Master sheet):-

Sub Test()

Dim ws As Worksheet, sh As Worksheet
Set sh = Sheets("Master")

Application.ScreenUpdating = False
        
        'sh.UsedRange.Offset(1).Clear  'If required, this line will clear the Master sheet with each transfer of data.
        
        For Each ws In Worksheets
                If ws.Name <> "Master" Then
                        With ws.[A1].CurrentRegion
                                .AutoFilter 4, "Fail"
                                .Offset(1).EntireRow.Copy sh.Range("A" & Rows.Count).End(3)(2)
                                .AutoFilter
                        End With
                End If
        Next ws

Application.ScreenUpdating = True

End Sub

It filters on Column D in each source sheet for "Fail" (not Column H as mentioned in your code) then transfers the relevant rows of data to the Master sheet starting in ColumnA (without overwriting).

I hope that this helps.

Cheerio,
vcoolio.

 

[jdorfma]

You are welcome.

So, did that fix that issue?

How can i get it to run across a range of worksheets(or whole workbook) now? I have about 10 I would like the macro to run across synchronously. I feel like I should know how to modify to do that but I can't get there

 

[Joe4]

How can i get it to run across a range of worksheets(or whole workbook) now? I have about 10 I would like the macro to run across synchronously. I feel like I should know how to modify to do that but I can't get there

See if vcoolio's response answers that question for you.

 

[jdorfma]

See if vcoolio's response answers that question for you.

Uh, that didnt pan out for me. I really just need to expand upon yours - which is to run against all the sheets 'except' maybe the first 4

 

[Joe4]

Uh, that didnt pan out for me. I really just need to expand upon yours - which is to run against all the sheets 'except' maybe the first 4

That is not correct.
vcoolio's code will run against ALL sheets, except the one named "Master".
Just changed "Master" to the name of the sheet you do not want to run it against (the sheet where you are copying everything to).

 

[jdorfma]

That is not correct.
vcoolio's code will run against ALL sheets, except the one named "Master".
Just changed "Master" to the name of the sheet you do not want to run it against (the sheet where you are copying everything to).

Right - the code is failing because of this line - but im not sure why

.AutoFilter 4, "Fail"