Hello,

I recorded a macro and additionally used the DG help to construct the
following macro; however, I'm not certain how to copy the filtered range
(which will vary every time the worksheet is used) or how to find the last
row of the workbook to which the data will be pasted. Any help would be
great! And thanks in advance.

Sub Macro1()
Dim wbname As String
Dim copyrange As Range
Dim LastRow As Range
Dim rng As Range

' Macro1 Macro
' Macro recorded 12/8/2009 by asagay
'
wbname = ActiveSheet.Range("g1").Value & ActiveSheet.Range("j1").Value
Columns("A:F").Select
Range("F1").Activate
Selection.EntireColumn.Hidden = False
Rows("113:113").Select
Selection.AutoFilter Field:=1, Criteria1:=ActiveSheet.Range("j1").Value
Set rng = ActiveSheet.AutoFilter.Range
If rng.Columns(1).SpecialCells(xlVisible).Count 1 Then
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set copyrange = Rows("2:" & LastRow)
copyrange.Copy
End If
Workbooks.Open ("C:\Documents and
Settings\asagay\Desktop\Assays\BCA\BCASummary.xls" )
Windows("BCASummary.xls").Activate
ActiveSheet.Columns(1).SpecialCells (xlCellTypeLastCell)
ActiveSheet.Paste
Selection.SpecialCells(xlCellTypeBlanks).Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Selection.SpecialCells(xlCellTypeBlanks).Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A17").Select
ActiveWorkbook.Save
ActiveWindow.Close
Columns("A:E").Select
Range("E1").Activate
Selection.EntireColumn.Hidden = True
ActiveSheet.ShowAllData
ChDir "C:\Documents and Settings\asagay\Desktop\Assays\BCA"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\asagay\Desktop\Assays\BCA\" & wbname,
FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
Workbooks.Open ("C:\Documents and
Settings\asagay\Desktop\Assays\BCA\BCASummary.xls" )
ActiveWorkbook.Save
ActiveWindow.Close
End Sub