Vba Comp from multiple worksheets based on cell Value

[Demer]

Hello, Sorry im not very good with VBA but I need a VBA to search through multiple worksheets and search column c for "Yes" based on c value copy adjacent data in column A from all worksheets and paste those values to a new sheet called "Archive" Note that I have over 40 sheets.

 

[My Aswer Is This]

Try this:

Sub Filter_Me_Please()
'Modified  10/24/2021  7:57:22 PM  EDT
Application.ScreenUpdating = False
Dim lastrow As Long
Dim C As Long
Dim s As Variant
Dim i As Long
C = 3 ' Column Number Modify this to your need
s = "Yes" 'Search Value Modify to your need
lastrow = Cells(Rows.Count, C).End(xlUp).Row
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Archive"

For i = 1 To Sheets.Count - 1
With Sheets(i).Cells(1, C).Resize(lastrow)
    .AutoFilter 1, s
    counter = .Columns(C).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
    lastrowa = Sheets("Archive").Cells(Rows.Count, 1).End(xlUp).Row + 1
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Offset(, -2).Copy Sheets("Archive").Cells(lastrowa, 1)
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
Next
Application.ScreenUpdating = True
End Sub

 

[Demer]

Try this:

Sub Filter_Me_Please()
'Modified  10/24/2021  7:57:22 PM  EDT
Application.ScreenUpdating = False
Dim lastrow As Long
Dim C As Long
Dim s As Variant
Dim i As Long
C = 3 ' Column Number Modify this to your need
s = "Yes" 'Search Value Modify to your need
lastrow = Cells(Rows.Count, C).End(xlUp).Row
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Archive"

For i = 1 To Sheets.Count - 1
With Sheets(i).Cells(1, C).Resize(lastrow)
    .AutoFilter 1, s
    counter = .Columns(C).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
    lastrowa = Sheets("Archive").Cells(Rows.Count, 1).End(xlUp).Row + 1
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Offset(, -2).Copy Sheets("Archive").Cells(lastrowa, 1)
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
Next
Application.ScreenUpdating = True
End Sub

It keeps throwing an error on the Auto filter 1,s and the .AutoFilter when I reset it then it throws the No Values Found and an error. Can this also over ride the Archive Sheet when a new yes is produced and clicked to update without having to delete the Archive sheet first ?

 

[My Aswer Is This]

Well s represents the value "Yes" which is what we are searching for in column C of all sheets

Each time you run the script before hand you must delete the sheet named Archive

I guess with more code we can have the script delete the sheet named "Archive"
If there is one and then make a new one but you never asked for that. Not sure whty we need to do that.

 

[Demer]

Well s represents the value "Yes" which is what we are searching for in column C of all sheets

Each time you run the script before hand you must delete the sheet named.

I guess with more code we can have the script delete the sheet named "Archive"
If there is one and then make a new one but you never asked for that. Not sure whty we need to do that.

The reason I mention the Archive sheet it first creates it, however, if i try to run again it also throws this error:

 

[My Aswer Is This]

Sure if you created it once the next time it will create it again.
You never said you planned to run the script more then once.
Why do we need to create the sheet in the script?
Why not just create it yourself. If not I will have to have the script look each time to see if there is a sheet named Archive and if so delete it or if not create it.

 

[Demer]

Sure if you created it once the next time it will create it again.
You never said you planned to run the script more then once.
Why do we need to create the sheet in the script?
Why not just create it yourself. If not I will have to have the script look each time to see if there is a sheet named Archive and if so delete it or if not create it.

I can add the delete the sheet into the code prior to creating the new one. However, It still throws the error on the Autofilter 1,s

 

[My Aswer Is This]

Change this:
Dim s As Variant

To:
Dim s As string

 

[Demer]

Change this:
Dim s As Variant

To:
Dim s As string

Sorry but it still throws the same error

 

[My Aswer Is This]

So the script does nothing?
Except create the new sheet?