I cobbled together the following macro, which does the following:
1) parses a 20,000 record table according to some criteria,
2) filters on those records for which the criteria are met, and
3) copies and pastes the filtered records to a new workbook.

My question is this: the number of rows in the source table will change
depending on what time of the month I download it from the server. How can I
adjust this macro to be dynamic, in terms of the number of rows over which
the filter criteria are applied?

Following is the code:

Sub ParseELR()
'
' ParseELR Macro
' Macro recorded 3/19/2007 by D Friedman
'

'
Range("A3").Select
If ActiveSheet.AutoFilterMode = True Then
Selection.AutoFilter
End If

If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If

Range("T2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(ISNUMBER(MATCH(LEFT(RC[-18],3),'[ELR expense account
identification.xls]Sheet1'!R2C1:R12C1,0)),ISNUMBER(MATCH(RC[-17],'[Frank''s
expense codes--GDCS and
non-GDCS.xls]Sheet1'!R2C1:R39C1,0))),""Extract"","""")"
Range("S2").Select
Selection.End(xlDown).Select
Range("T27355").Select
ActiveCell.FormulaR1C1 = "."
Range("T27354").Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlDown)).Select
Range("T2:T27354").Select
Selection.FillDown
Selection.End(xlUp).Select
Selection.AutoFilter Field:=20, Criteria1:="Extract"
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"G:\NADC Finance\D Friedman\MONTHLY DATA SOURCES FOR
DB\ELR\2007\Mar\ELR Parsed.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub


--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

Somehow I put the wrong subject line on this post. Sorry, ignore that. The
question I have is below.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Dave F" wrote:

I cobbled together the following macro, which does the following:
1) parses a 20,000 record table according to some criteria,
2) filters on those records for which the criteria are met, and
3) copies and pastes the filtered records to a new workbook.

My question is this: the number of rows in the source table will change
depending on what time of the month I download it from the server. How can I
adjust this macro to be dynamic, in terms of the number of rows over which
the filter criteria are applied?

Following is the code:

Sub ParseELR()
'
' ParseELR Macro
' Macro recorded 3/19/2007 by D Friedman
'

'
Range("A3").Select
If ActiveSheet.AutoFilterMode = True Then
Selection.AutoFilter
End If

If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If

Range("T2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(ISNUMBER(MATCH(LEFT(RC[-18],3),'[ELR expense account
identification.xls]Sheet1'!R2C1:R12C1,0)),ISNUMBER(MATCH(RC[-17],'[Frank''s
expense codes--GDCS and
non-GDCS.xls]Sheet1'!R2C1:R39C1,0))),""Extract"","""")"
Range("S2").Select
Selection.End(xlDown).Select
Range("T27355").Select
ActiveCell.FormulaR1C1 = "."
Range("T27354").Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlDown)).Select
Range("T2:T27354").Select
Selection.FillDown
Selection.End(xlUp).Select
Selection.AutoFilter Field:=20, Criteria1:="Extract"
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"G:\NADC Finance\D Friedman\MONTHLY DATA SOURCES FOR
DB\ELR\2007\Mar\ELR Parsed.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub


--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.