Copying to Next Empty Row

[Pasha81]

Hi guys

Worksheet 'Raw' has a few rows of Data that get send to 3 different sheets based on the Account# in Column A. The Date Column always shows 1 date, as the values are for different products on the same day.

The Code starts of by checking if any value in Column AG has a '#N/A' value, If it does then the macro will give a message and exit. However I'm getting a row of 'N/A' values in Sheets 101,201 and 301. If there is an N/A value in Column AG i dont want the macro to do anything.

2nd issue - I need to adjust the macro so that it copies these values into the Next Empty Row in sheets 101,201 and 301, not to overwrite the previous row there.

I think somthing like the following code may be required but I couldnt make it work

Dim NextRw as Long
NextRw = .Cells(.Rows.Count, 1).End(xlUp).Row+1

Here's the code I currently have

Sub Allocation()
 
Dim ws As Worksheet, rData As Range, n As Long

 
Application.ScreenUpdating = False

Dim cell        As Range
    
    For Each cell In Intersect(ActiveSheet.UsedRange, Columns("AG"))
        If cell.Text = "#N/A" Then
            cell.Select
            MsgBox "New contract detected. Please identify asset class and update IMAP sheet before continuing."
            Exit For
        End If
    Next cell
    
With Sheets("Raw")
    Set rData = .Range("A2", .Range("AG" & Rows.Count).End(xlUp))
End With



For Each ws In Worksheets



    If ws.Name Like "###" Then
        ws.Cells(3, 1).Value = Sheets("Raw").Cells(2, 3)
        ws.Cells(3, 2).Resize(, 10).Formula = _
            "=SUMPRODUCT((Raw!" & rData.Columns(1).Address & "=" & ws.Name & ")*(Raw!" & rData.Columns(33).Address & "=B2)*(Raw!" & rData.Columns(8).Address & "))"
    End If
Next ws

Application.ScreenUpdating = True
   
End Sub

any suggestions?

Thanks