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?
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!
Probably a really easy fix that I can't see, but any help would be appreciated - thanks!
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?
VBA Code:
'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!
VBA Code:
If Not IsEmpty(Range("A7").Value) Then
Probably a really easy fix that I can't see, but any help would be appreciated - thanks!