I am trying to transfer the value for a total for each row entry (rowentryname used below as eg) from a pivot table to summary sheet. The size of the pivot table (number of columns) may be different each time. I have set up finalrow and finalcolumn variables, which seem to work, but the last section of the code with the VLookup function doesn't work, bringing up error 1004 (Application-defined or object-defined error)

FinalRow = Workbooks("workbookname.xls").Worksheets("worksheetname").Cells(Application.Rows.Count, 2).End(xlUp).Row
FinalColumn = Workbooks("workbookname.xls").Worksheets("worksheetname").Cells(8, Application.Columns.Count).End(xlToLeft).Column
Worksheets("wbname").Range("C7") = WorksheetFunction.VLookup("rowentryname", (Worksheets("worksheetname").Range(Cells(2, 8), Cells(FinalRow, FinalColumn))), FinalColumn - 1, False)

Hi Jennifer

Thankyou for your question

Near as I can tell, your problem seems to be with the syntax of your Vlookup function, specifically with the way in which the range of the lookup table (Second argument) is defined.

The easiest method is to name the range of the lookup table and then use the name in the code. I have replaced that line of code in your sample with the following and it seems to work fine

Worksheets("pivot").Range("F10") = WorksheetFunction.VLookup("Jan", Range("Table"), FinalColumn, False)

I have named the range "table" and in this instance manually defined it. I appreciate that your problem is that the range needs to vary. I would suggest that you write a module that automatically determines the range of the table and then names it before you run the above code. There are a number of different possible methods using for example the "resize" or "Current region" properties.

If you have difficulties here I would be glad to help, but it would be handy to have an example of a typical workbook to base it on. If this is necessary and possible could you email it to

infoATmicrosofttrainingDOTnet

marking the subject as "for the attention of stephen"

Hope this helps

Stephen