VBA Code to Ignore Skip Copy/Paste Command on Cells Containing #N/A

I have an Excel workbook with a macro to copy/paste specific cells from 'Sheet1' worksheet into specific cells to 'XML' worksheet.  The code works great but for cells in 'Sheet1' which the formula is returning #N/A (as expected), I would like the VBA script that I currently have to skip or ignore those cell ranges (however many it may be).  Any assistance would be great!

VBA Code
Sub Copy()
Dim LR As Long, i As Long, j As Long
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LR
        j = j + 1
        .Range("M" & i & ":M" & i).Copy
        Sheets("XML").Range("A" & j).PasteSpecial Paste:=xlPasteValues
        .Range("A" & i & ":A" & i).Copy
        Sheets("XML").Range("C" & j).PasteSpecial Paste:=xlPasteValues
        .Range("N" & i & ":P" & i).Copy
        Sheets("XML").Range("D" & j).PasteSpecial Paste:=xlPasteValues
        .Range("Q" & i & ":Q" & i).Copy
        Sheets("XML").Range("I" & j).PasteSpecial Paste:=xlPasteValues
    
        j = j + 1
        .Range("D" & i & ":D" & i).Copy
        Sheets("XML").Range("B" & j).PasteSpecial Paste:=xlPasteValues
        .Range("R" & i & ":R" & i).Copy
        Sheets("XML").Range("D" & j).PasteSpecial Paste:=xlPasteValues
        .Range("C" & i & ":C" & i).Copy
        Sheets("XML").Range("I" & j).PasteSpecial Paste:=xlPasteValues
        .Range("B" & i & ":B" & i).Copy
        Sheets("XML").Range("J" & j).PasteSpecial Paste:=xlPasteValues

    Next i
End With
With Application
    .CutCopyMode = False
    .ScreenUpdating = True
End With

End Sub

Open in new window


Example of Sheet1 tab
excel_sheet1.png
Example of XML tab
excel_xml.png
KANEDA 0149Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
So if column A on Sheet1 is #N/A then don't copy anything to XML?

If so, try this.
Sub CopyToXML()
Dim LR As Long, i As Long, j As Long

    With Sheets("Sheet1")
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        For i = 2 To LR
        
            If Not IsError(.Range("A" & i)) Then
                j = j + 1
                .Range("M" & i & ":M" & i).Copy
                Sheets("XML").Range("A" & j).PasteSpecial Paste:=xlPasteValues
                .Range("A" & i & ":A" & i).Copy
                Sheets("XML").Range("C" & j).PasteSpecial Paste:=xlPasteValues
                .Range("N" & i & ":P" & i).Copy
                Sheets("XML").Range("D" & j).PasteSpecial Paste:=xlPasteValues
                .Range("Q" & i & ":Q" & i).Copy
                Sheets("XML").Range("I" & j).PasteSpecial Paste:=xlPasteValues
            
                j = j + 1
                .Range("D" & i & ":D" & i).Copy
                Sheets("XML").Range("B" & j).PasteSpecial Paste:=xlPasteValues
                .Range("R" & i & ":R" & i).Copy
                Sheets("XML").Range("D" & j).PasteSpecial Paste:=xlPasteValues
                .Range("C" & i & ":C" & i).Copy
                Sheets("XML").Range("I" & j).PasteSpecial Paste:=xlPasteValues
                .Range("B" & i & ":B" & i).Copy
                Sheets("XML").Range("J" & j).PasteSpecial Paste:=xlPasteValues
            End If
            
        Next i
    End With
    
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KoenChange and Transition ManagerCommented:
or alternatively you could add  an iferror to the formula on sheet1, turning the cell into a blank, hence ending the range...
=iferror(someformula,"")

you wouldn't need to change your code then...
however will not work if you have rows of #N/A in the midst of your data...
0
KANEDA 0149Author Commented:
@Norie it would be columns A thru columns R, in addition to any rows where #N/A exist.   The rows can go down up to 8,000 for example.

@Koen, I did try that already but the code still copied the empty cells in columns A thru columns R treating them as "active" cells creating blank active rows and columns.

Column A contains an array formula, like so =INDEX('Fee Recon Detail'!$A$2:$A$4,MATCH(0,COUNTIF($A$5:A8,'Fee Recon Detail'!$A$2:$A$4),0))

Columns B thru R contains a formula like so =IF(A9="","",SUMIF('Fee Recon Detail'!A:A,A9,'Fee Recon Detail'!B:B))
0
KANEDA 0149Author Commented:
Thanks Norie, that additional line item in your code worked exactly as I needed it!
0
KoenChange and Transition ManagerCommented:
of course, my bad... you do a row count, and the formulas are counted.

you could loop with a Do While AXX <>"", but that would also mean changing the code, would still not deal with #N/A in the middle (if existing), so then I'd follow Norie
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.