Copying a sheet from a closed workbook using VBA | Excel forum

Resolved · Medium Priority · Version 2003

Wed 25 Aug 2010, 09:04 Delegate Tom said...

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

Tue 31 Aug 2010, 08:58 Edited on Tue 31 Aug 2010, 08:59 Trainer Stephen said...

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

Wed 8 Sep 2010, 09:15 Delegate Tom said...

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

Mon 6 Sep 2010: Automatically marked as resolved.

Training courses