Copy data on specific sheets if row contains a value

[pook_666]

Hi magicians!

I have a two part query I need help with please...

1 - I want to copy data from sheets with ranges A7:A, B7:B & M7:M (always will start from row 7 in these columns downwards) that aren't sheet names included in the array below (have put Sheet1, Sheet2 & Sheet3 for simplicity & privacy issues)....the issue that I'm facing with the below code is that it is still copying data from sheets in the array? What have I missed here to prevent this from happening?

'select specified sheets
    For Each ws In ActiveWorkbook.Worksheets
        If IsError(Application.Match(ws.Name, Array("Sheet1", "Sheet2", "Sheet3"), 0)) Then
      
'copy & paste data
            With ws
                lrow2 = .Range("A" & Rows.Count).End(xlUp).Row
                .Range("A7:A" & lrow2).Copy Destination:=Sheets("Sheet4").Range("B" & Rows.Count).End(xlUp).Offset(1)
                .Range("B7:B" & lrow2).Copy Destination:=Sheets("Sheet4").Range("C" & Rows.Count).End(xlUp).Offset(1)
                .Range("M7:M" & lrow2).Copy Destination:=Sheets("Sheet4").Range("E" & Rows.Count).End(xlUp).Offset(1)
            End With
        End If
    Next

2 - in the sheets that aren't Sheet1, Sheet2 & Sheet3 I only want to copy the data in A7:A, B7:B and M7:M if there is data there (there might not be). I've tried putting the below in just above the "With ws" in the above code, but nothing happens when I run the code - not even the copy & paste issue I have in part 1 above!

If Not IsEmpty(Range("A7").Value) Then

Probably a really easy fix that I can't see, but any help would be appreciated - thanks!

 

[pook_666]

You're not going to believe this.....I spelt the sheet name incorrectly and that's why it was still being picked up!!!!!

I'm so sorry about this and being a complete idiot - but thank you in helping me solve it!

 

[mohadin]

All right
Don't blame your self, things happens
The most important thing solving the issue, right?
Regards
Be happy

 

[dmt32]

what I've found is that both of your codes copies the Sheet4 information as well as all other sheets that aren't Sheet1-3.

So A7:A, B7:B & M7:M from Sheet4 are being copied and pasted...is it because that Sheet4 is the destination?

Any ideas how to not include Sheet4 copying?

The names listed in the Case statement must match your sheet names

Case "Sheet1", "Sheet2", "Sheet3", "Sheet4"

check Sheet4 tab matches above & does not include a space

Dave