VBA Error handling Loop

[fcarboni]

Hi All

I have a sheet called all data and multiple other sheets for individuals.
Based on a defined cell value the macro copies all rows from "All Data" that match the value of the individual.
This works fine when there is a sheet for each unique individual but where there is data for someone but not a sheet the macro stops on "set tws" . I have tried the below but it still fails

if it finds a cell that does not match any sheet I want it to skip the row and look at the next one
as well the below, at the same point, I have tried 'on error goto next'. this does run but copies in the row Evan if it doesn't match.


Dim sws As WorksheetDim tws As Worksheet
Dim cel As Range
For Each ws In Worksheets
Select Case ws.Range("B1")
Case Is <> "MP"
'Do nothing
Case Else
Set sws = Sheets("All Data")
For Each cel In sws.Range("H4:H" & Range("H" & Rows.Count).End(xlUp).Row)

----Here I have tried 'on error go to MP:


Set tws = Sheets(cel.Value)
cel.EntireRow.Copy tws.Range("A" & Rows.Count).End(xlUp).Offset(1)

-----MP:


Next cel
Exit For
End Select
Next ws


Thanks V much

 

[wideboydixon]

Welcome to the forum. Please use code tags around code; it makes it easier. You could try like this:

On Error Resume Next

Dim sws As Worksheet
Dim tws As Worksheet
Dim cel As Range

For Each ws In Worksheets
    Select Case ws.Range("B1")
        Case Is <> "MP"
            'Do nothing
        Case Else
            Set sws = Sheets("All Data")
            For Each cel In sws.Range("H4:H" & Range("H" & Rows.Count).End(xlUp).Row)
                Set tws = Nothing
                Set tws = Sheets(cel.Value)
                If Not (tws Is Nothing) Then
                    cel.EntireRow.Copy tws.Range("A" & Rows.Count).End(xlUp).Offset(1)
                End If
            Next cel
            Exit For
    End Select
Next ws

WBD

 

[fcarboni]

will do.

That's worked perfectly

Thanks so much

 

[Fluff]

For info on why your original code did not work, have a look here http://excelmatters.com/2015/03/17/on-error-wtf/