Referencing A Range in Multiple Closed Workbooks


New Member
Mar 28, 2012
Hello All

I am a VBA virgin (pretty much) but I think that I have a requirement that can only be solved using VBA so any help you can offer would be great.

I receive a spreadsheet (.xls extension) every workday and the filename is made up of the date in yyyymmdd format.

I have the individual excel spreadsheets for each day going back years.

The format of these spreadsheets changes every year or so!!

I would like to do something like a MATCH() formula to find out what row a specific value is located.

I have the following details in my new and separate spreadsheet where I would like to record the row numbers.

Date (eg 02/01/2008 ), Filepath (eg 'C:\Documents and Settings\My Documents\2008\[20080102.xls]Sheet1'!B:B ), Lookup value (eg Apple )then a Blank column for the row number to be posted.

I then have 1085 more rows (one for each working day since Jan 2008) where the filepaths are identical apart from the year (folder) and the filename.

I don't mind if the automated procedure when started takes a while as I intend to only do this once then copy and paste special values once I have them, but I don't want to have to open over 1000 spreadsheets (and growing day by day).

In my own head, the VBA would call up the range (column B in the desired woorkbook) then look for the relevant cell with the value in it without actually opening the workbook (if this is even possible). Then return the row number that it found that value on in the target workbook. As I say VBA virgin.

Any help will be grand and it would be easy if I could use VLOOKUP with INDIRECT on closed workbooks :)

Last edited:

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...