Good afternoon,

I have set-up the following macro to do a number of tasks between two
worksheets in a workbook. It does what I need it to do, however I would like
to expand the capabilties of it and "fine tune" it abit.

Sheets("Current Unapplied").Select
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Style = "Comma"
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[-9],RC[-8],RC[-7],RC[-6],RC[-5],RC[-4],RC[-3])"
Selection.AutoFill Destination:=Range("J2:J5000"), Type:=xlFillDefault
Range("J2:J5000").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollRow = 822
ActiveWindow.ScrollRow = 685
ActiveWindow.ScrollRow = 548
ActiveWindow.ScrollRow = 275
ActiveWindow.ScrollRow = 1
Range("K2").Select
ActiveCell.FormulaR1C1 = "1"
Range("K3").Select
ActiveCell.FormulaR1C1 = "2"
Range("K4").Select
ActiveCell.FormulaR1C1 = "3"
Range("K2:K4").Select
Selection.AutoFill Destination:=Range("K2:K5000")
Range("K2:K5000").Select
Sheets("Unapplied Copy").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[-9],RC[-8],RC[-7],RC[-6],RC[-5],RC[-4],RC[-3])"
ActiveWindow.SmallScroll ToRight:=3
Selection.AutoFill Destination:=Range("J2:J5000"), Type:=xlFillDefault
Range("J2:J5000").Select
ActiveWindow.ScrollRow = 822
ActiveWindow.ScrollRow = 958
ActiveWindow.ScrollRow = 822
ActiveWindow.ScrollRow = 548
ActiveWindow.ScrollRow = 411
ActiveWindow.ScrollRow = 275
ActiveWindow.ScrollRow = 138
ActiveWindow.ScrollRow = 1
Range("K2").Select
ActiveCell.FormulaR1C1 = "1"
Range("K3").Select
ActiveCell.FormulaR1C1 = "2"
Range("K4").Select
ActiveCell.FormulaR1C1 = "3"
Range("K2:K4").Select
Selection.AutoFill Destination:=Range("K2:K5000")
Range("K2:K5000").Select
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'Current Unapplied'!R2C10:R5000C11,2,FALSE)"
Selection.AutoFill Destination:=Range("L2:L5000")
Range("L2:L5000").Select
Range("L1").Select
Sheets("Current Unapplied").Select
ActiveWindow.ScrollRow = 822
ActiveWindow.ScrollRow = 685
ActiveWindow.ScrollRow = 548
ActiveWindow.ScrollRow = 275
ActiveWindow.ScrollRow = 1
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'Unapplied Copy'!R2C10:R5000C11,2,FALSE)"
Selection.AutoFill Destination:=Range("L2:L5000")
Range("L2:L5000").Select
Range("L1").Select
Sheets("Unapplied Copy").Select
ActiveSheet.Range("$A$1:$L$64999").AutoFilter Field:=12, Criteria1:="#N/A"
Sheets("Current Unapplied").Select
ActiveSheet.Range("$A:$L").AutoFilter Field:=12, Criteria1:="#N/A"
Sheets("Unapplied Copy").Select
Range("A1").Select
End Sub

The first issue I need help with is the following part of the macro (which
will also be used on the other parts that do the smae process):

Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[-9],RC[-8],RC[-7],RC[-6],RC[-5],RC[-4],RC[-3])"
Selection.AutoFill Destination:=Range("J2:J5000"), Type:=xlFillDefault
Range("J2:J5000").Select

Is there any code I can use so instead of the range going to 5,000 the range
will stop where the data in cell A stops? I would also like to note that
every week the report varies in size (that is why I set the range to 5,000,
because I know the report will never be that large).

The next two issues deal with expanding the macro:

Sheets("Unapplied Copy").Select
ActiveSheet.Range("$A$1:$L$64999").AutoFilter Field:=12, Criteria1:="#N/A"

This part does what I need it to I would just like to expand the macro at
this point. I would like to insert code here to have the macro delete the
rows with #N/A, and then reset the filter on field 12 to ALL, then go to the
row below the row of last data in cell A.


Sheets("Current Unapplied").Select
ActiveSheet.Range("$A:$L").AutoFilter Field:=12, Criteria1:="#N/A"

Again this does what I need it to I would just like to expand it. I would
like to insert code to have the macro copy the rows with #N/A and paste them
below the last row of data on the sheet named "Unapplied Copy"

I greatly appreciate any help that can be provided.

Chad