Copy data on specific sheets if row contains a value

pook_666

Board Regular
Joined
Aug 16, 2018
Messages
80
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?

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!
 

pook_666

Board Regular
Joined
Aug 16, 2018
Messages
80
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!
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
All right
Don't blame your self, things happens
The most important thing solving the issue, right?
Regards
Be happy
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,508
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
Case "Sheet1", "Sheet2", "Sheet3", "Sheet4"

check Sheet4 tab matches above & does not include a space

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,129,386
Messages
5,636,012
Members
416,892
Latest member
Bensch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top