Excel help needed with IF statements/ IF Macro?

[AbigailF]

I have several worksheets set up in one workbook (more will be added in the future too), and I need to produce a macro/ if statement across ALL sheets except one.

So if column C in any worksheet (except one named 'Campaigns') contains the text 'CA' that ROW is then copied into a worksheet called 'Campaigns'.

Any ideas on how to do this? I'm a newbie when it comes to macros so an idiots guide would be much appreciated!

Thanks!

 

[VoG]

Try

Sub test()
Dim ws As Worksheet, LR As Long, i As Long
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Campaigns" Then
        With ws
            LR = .Range("C" & Rows.Count).End(xlUp).Row
            For i = 1 To LR
                If .Range("C" & i).Value = "CA" Then .Rows(i).Copy Destination:=Sheets("Campaigns").Range("A" & Rows.Count).End(xlUp).Offset(1)
            Next i
        End With
    End If
Next ws
End Sub

 

[AbigailF]

Thank you for the quick reply!
I hit 'view code' on the first worksheet and pasted in the above, then I hit save, but nothing has happened. Do I need to do anything to trigger the action?
Will I need to paste the same code into all sheets?

Thanks,
Abigail

 

[Andrew Poulsom]

Using AutoFilter:

Sub Test()
    Dim ws As Worksheet
    Dim NextRow As Long
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Campaigns" Then
            With Worksheets("Campaigns")
                NextRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
            End With
            With ws
                With .Range("A1").CurrentRegion
                    .AutoFilter
                    .AutoFilter Field:=3, Criteria1:="CA"
                End With
                With .AutoFilter.Range
                    .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy Worksheets("Campaigns").Range("A" & NextRow)
                End With
                .AutoFilterMode = False
            End With
        End If
    Next ws
End Sub

You may want to clear the contents of the cells below the headers on sheet Campaigns before you start.

 

[VoG]

Thank you for the quick reply!
I hit 'view code' on the first worksheet and pasted in the above, then I hit save, but nothing has happened. Do I need to do anything to trigger the action?
Will I need to paste the same code into all sheets?

Thanks,
Abigail

Press ALT + F11 to open the Visual Basic editor. Select Module from the Insert menu and paste in the code. Press ALT + Q to close the code window.

Press ALT + F8, double click test.

 

[AbigailF]

It is working, but it is only picking up one of the rows that contains CA in column C not all rows. And it's not working across all worksheets.

Thanks for your help,
Abigail

 

[VoG]

Do the cells contain just CA or other text like CA1.

Is there data in column A?

 

[AbigailF]

Column A does have data in it, yes.
Column B would ideally have other characters in it too, but CA would be at the start of the cell.

 

[VoG]

Try

Sub test()
Dim ws As Worksheet, LR As Long, i As Long
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Campaigns" Then
        With ws
            LR = .Range("C" & Rows.Count).End(xlUp).Row
            For i = 1 To LR
                If .Range("C" & i).Value Like "CA*" Then .Rows(i).Copy Destination:=Sheets("Campaigns").Range("A" & Rows.Count).End(xlUp).Offset(1)
            Next i
        End With
    End If
Next ws
End Sub