Tom has attended:
Excel VBA Intro Intermediate course
Copying a sheet from a closed workbook using VBA
Hi,
I need to use VLOOKUP in a spreadsheet I receive on a monthly basis but the array is in a separate workbook. It seems to me that the easiest solution is to use VBA to open the source workbook, copy the relevant sheet to the target workbook and then close the source workbook. I haven't been able to get this to work though (I think the problem is that if I don't close the source workbook I can't activate the target workbook and if I do close it the paste command can't find the relevant data) - can you suggest some code that I could try or is there a better way of running the VLOOKUP?
Thanks,
Tom
RE: Copying a sheet from a closed workbook using VBA
Hi Tom
Thanks for your question
I think the problem is highlighted by the fact that you refer to using the paste command. I'm not sure how you are trying to make that work, but I think the solution is to refer to the source workbook directly in your code
I would create a range object variable and set it equal to the target range in the vlookup function
[code]
dim rngSource as range
Set rngSource = Workbooks("Source Workbook").Sheets(1).Range("A1").currentregion[code]
I would then use that range object directly in the relevant argument of the VLookup function
Regards
Stephen
RE: Copying a sheet from a closed workbook using VBA
Hi Stephen, in the end I went for this code:
Cells(IntRowCount, "B").Formula = "=VLOOKUP(RC[-1],'Extensions and cost centres.xlsx'!Number,2,FALSE)"
but get an Update Values dialogue and have to browse to the source sheet the first time I run it. Would the code you specify prevent this?
Thanks for your help,
Tom
