VBA pasting macro help

[will31]

Hi All,

I have the following macro:

Sub Paste()
'
' Paste Macro
'
Application.ScreenUpdating = False
'Paste AB    Workbooks.Open Filename:= _
        "[URL="file://\\hunnt47\DataRevenInv\R"]NNA[/URL].xlsm", _
        Origin:=xlWindows
    Workbooks("data capture.xlsm").Sheets("AB").Range("A3:J2000").Copy Workbooks("NNA.xlsm").Sheets("Schedule").Range("A2")
'Paste AC
Workbooks("data capture.xlsm").Sheets("AC").Range("A3:J2000").Copy Workbooks("NNA.xlsm").Sheets("Schedule").Range[B][COLOR=red]("A150")
[/COLOR][/B]    Workbooks("NNA.xlsm").Sheets("Schedule").Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Replace What:=" - IL", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Workbooks("NNA.xlsm").Close savechanges:=True
Application.ScreenUpdating = True
End Sub

Which does what I want it to do but I want to add some more intelligence to the way it works. I'm trying to get it to clear A2 to the last fill cell in comumn J on sheet Schedule in workbook NNA. I can't figure out how to do this.

Also, for the pasting of the data from Data capture into NNA if it is possible for the cell reference in red to be found by find the first unfilled cell in column A that would be great.

Thanks in advance,

Will

 

[AlphaFrog]

Sub Paste()
'
' Paste Macro
'

    Dim wsAB As Worksheet: Set wsAB = Workbooks("data capture.xlsm").Sheets("AB")
    
    Application.ScreenUpdating = False
    
    With Workbooks("NNA.xlsm").Sheets("Schedule")
    
        .Range("A2", .Range("J" & Rows.Count).End(xlUp)).ClearContents
        
        wsAB.Range("A3", wsAB.Range("J" & Rows.Count).End(xlUp)).Copy .Range("A2")
    
        .Columns("B:B").Replace What:=" - IL", Replacement:="", _
                                LookAt:=xlPart, SearchOrder:=xlByRows, _
                                MatchCase:=False, SearchFormat:=False, _
                                ReplaceFormat:=False
    End With
    
    Workbooks("NNA.xlsm").Close SaveChanges:=True
    
    Application.ScreenUpdating = True
    
End Sub

 

[will31]

Thanks AlphaFrog works great