with variable document, filter by multiple criteria across multiple columns and copy cell

Hello,

Every month, I receive an exported Excel workbook of data from an outside source, with a fixed number of columns each containing a particular type of data point. However, this external document also adds onto itself extra rows every month, to account for updates in the relevant monthly data. Using a macro, I would like to copy certain key data points onto my own workbooks, but in order to do so, I can't select a specific range from which to copy the data, for the content of a specific range will change from month from month.

In order to get around this problem, I would like to write a macro that will filter a specific row using multiple criteria across multiple columns, automatically select a specific cell within that row (using the offset function, and not specifying a particular range, because the content of a particular range will change from month to month), and copy the data within that cell onto another workbook.

This might be a bit confusingly worded -- please respond if you would like me to clarify any part of my explanation. Thanks so much for all your help guys!

Hi Charles!

Sure, I'd be glad to provide some more clarification. Here's some code that I wrote in order to solve a slightly different yet related problem:

Sub June2012ClientPortPerfPercent()
'This portion of the macro locates the total client portfolio performance for June 2012,
'expressed in percent, from the exported document, then copies and pastes that data into the "All
'Portfolios" worksheet (assuming that the "Firm Equity" worksheet is the active worksheet) of
'the "Portfolio Performance" workbook.
Windows("Exported Firm Portfolio Part 2.xlsm").Activate
Range("B1").Select
Application.Goto Reference:= _
"INDIRECT(ADDRESS(MATCH(1765,R1C2:R1048576C2),2))"
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.End(xlUp).Select
ActiveCell.Offset(-1, 7).Range("A1").Select
Selection.Copy
Windows("Portfolio Performance.xlsm").Activate
Range("FY167").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Dim x As Double
x = Range("FY167").Value
x = x / 100
Range("FY167").Value = x
Range("FY167").Value = Format(Range("FY167").Value, "0.00%")

Basically, I located the last occurrence of a cell both within a particular column and within a row that contains a particular value (a cell both within Column B and a row containing the value "1765" in this case), then selected the cell directly below the last blank cell within both that particular column and a row also containing the initial value "1765" (using the xlup selection function). From the newly selected cell, I then selected a cell one cell up and seven rows to the right using the offset function, then copied the data within this cell to another workbook, expressed as a percent.

However, now I want to search for a row which fulfills multiple criteria across multiple columns (a row that in this case both contains the value "1765," a string called "EQ", and another string called "4/31/2012" in three different columns), then automatically select a cell within that particular row. Here's my first attempt at coding these instructions:

Sub test()
'This portion of the macro locates the total client domestic equity performance for June 2012
'from the exported document, then copies and pastes that data into the "Firm Equity" worksheet
'(assuming that the worksheet is the active worksheet) of the "Portfolio Performance" workbook as
'a percentage.
Windows("Exported File.xlsm").Activate
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AP$17410").AutoFilter Field:=2, Criteria1:="287"
ActiveSheet.Range("$A$1:$AP$17410").AutoFilter Field:=3, Criteria1:="EQ"
ActiveSheet.Range("$A$1:$AP$17410").AutoFilter Field:=4, Operator:= _
xlFilterValues, Criteria2:=Array(0, "4/30/2012")
ActiveSheet.Range("$A$1:$AP$17410").AutoFilter Field:=5, Operator:= _
xlFilterValues, Criteria2:=Array(1, "5/31/2012")
Range("J2").Select
ActiveSheet.ShowAllData
Selection.Copy
Windows("Portfolio Performance.xlsm").Activate
Range("IA250").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Dim z As Double
z = Range("IA250").Value
z = z / 100
Range("IA250").Value = z
Range("IA250").Value = Format(Range("IA250").Value, "0.00%")
End Sub

However, this code fails to accomplish my objective because while I successfully filtered out the row in question, I cannot automatically select a cell within the filtered row containing a particular string or value. I don't want to select a certain range (like what I did above with the line Range("J2").Select) because the document that I am trying to copy from is an exported document from an external source of market data, and thus the values within a certain range will change from month to month independent of my doing. I would thus like to use an offset function from a particular cell selected within the filtered row in order to select my target cell, to avoid having to select a certain range.

Thank you all so much again for any help you might be able to give me!