Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!


Free Excel Forum

Specifying File Name In V Lookup

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have 400 excel files with different names and only one worksheet, all the
filenames are of the form "yyyymmdd-closing_prices.xls", where yyymmdd
represent a date, the worksheets have the name "yyyymmdd-closing_prices"
corresponding to the name.

I also have a master Excel file in which I have a vlookup function looking
at these sheets "vlookup(B$1,'XXXXXXXX'!$1:$65000,3,0)" where XXXXXXX refers
to each of the 400files. as you can imagine, the array has to change with
each file name for the 400 entries, I have developed a lookup table in the
master file that automatically gives the file name and array (in text format)
next to any date I enter, but I cannot use it at the moment. How can I get
Vlookup() to use the lookup table in my master file instead of retyping the
filename and range manually.


View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat

Similar Topics

I'm very new to access - so please bare with me here (I'm more of an excel guy)

I have a database with a form. When you click the button on the form, it opens an input box which asks you to "enter the date in YYYYMMDD format"

Once you enter 07012009 it looks up a text file in a specific folder with that same end trail, then imports it with specific delimited specs I have pre-set.

It then imports all the records into it new individual table (using the end date trail) but also dumps it into a master table as well.

Okay - so knowing this - heres my quesiton:

How do can I get the input box "YYYYMMDD" into the column called "Date imported" within the master table and formated as "MM/DD/YYYY". Also, how can I get it to automatically put the text "IMPORT FILE" in the column called "Media" for each record?

It's VERY important that it only does this for the imported records. Is this possible?

Here is the code I have so far:


Private Sub Command1_Click()
Dim Importdate As String
On Error GoTo Errormsg
Importdate = InputBox("Please date of text file (YYYYMMDD):")
DoCmd.TransferText acImportDelim, "textfile", "textfile_" & Importdate, "S:\Brochure_" & Importdate & ".txt", N
DoCmd.TransferText acImportDelim, "Textfile", "MasterTable", "S:\Brochure_" & Importdate & ".txt", N

MsgBox ("Complete!")
If Err.Number = 3011 Or Err.Number = 13 Then
MsgBox ("Please try again!")
End If
End Sub

Also - everything I have works so far, I just need to add the features I said above.


I hope someone can help me here. I am extremely new to Excel and I'm using the 2007 version.

I have data that I pull out of another database into an Excel format. Within this data I need to change all the written out state names to their two letter abbreviations. (Ex. Tennessee to TN).
I can create the VLOOKUP to work to look at another worksheet within the same file to get these abbreviations.

My question is, if I have a master file with all the states and abbreviations, I then pull the data out in another file, can the VLOOKUP be used to look at a completely different file to get these abbreviations or do I have to copy and paste the master list into my current file and then create the vlookup.

I really just wonder if there is an easier way so I don't have to copy this master list each time I want to do this. I prefer just creating the VLOOKUP formula in my downloaded sheet to run against my master file.

Ex. Tennessee is in file "SF Contacts"

the states and abbreviations are in file "Master States"

I really appreciate any help I can get on this.

I have a folder in my HDD. The folder name is DC.
this DC folder contains lots (more than 100 files) of xls files. The names of the files are 1dc.xls, 2dc.xls, 3dc.xls.....155dc.xls etc.
Inside of those files there are some data, but I need to work with data in cell only from A2 till N2.

From the other side I have master xls file, called DC Master.xls. In the first column A I enter the name of the hundrets of my files, like column name is "File Name from folder DC" and it will be 1dc.xls, 2dc.xls etc.
But in in master file in second column B I want data from the second column of *dc.xls file.
Let's say in master file in column A3 I enter "3dc.xls" and in master file in column B3 I want the data from file 3dc.xls from it's column A2. The thing is I don't even want to open file 3dc.xls and just put simple reference to it's cell A2. What I want to copy all other/future files 156dc.xls to my folder DC and put in my master fild DC Master.xls in the first column "156.xls" and the data from 156dc.xls file appeared in column from left.

My idea was in master file Master.xls in the second column B get the data from the file which name in the same row but from the left.

Imagine this is Excel cells and I want to see this:

1 |File name|First data from file|
2 |156dc.xls |Alex G |

But in formula I did as follows:

1 |File name|First data from file|
2 |156dc.xls |='C:\Users\Eugene\Desktop\DC\['&'156dc.xls'&]Sheet1'!$A$2|

But it didn't work.
Using combining of the cells value ( I always know that the dc files will be stored in one folder and the name of the files will be very similar) I was able to generate correct URL adddress of the file but as text form not as as link.

My question is - how may I convert text value of the cell to LINK?

Ouch...guess I was specific enough...
Thanks fellows!

I have a large register of information being used for Risk Management. I have a few sheets besides the register on my Wookbook however am trying to use a vlookup to calculate what a overall risk rating is against likihood and severity / score.

here is the formula I am trying to use:

=IF(AND(VLOOKUP(D2,'Master Register'!A4:X2000,14)=1,VLOOKUP(D2,'Master Register'!A4:X2000,15)=3),"Low",IF(AND(VLOOKUP(D2,'Master Register'!A4:V100,14)=1),VLOOKUP(D2,'Master Register'!A4:X2000,15)=4),"Mod",IF(AND(VLOOKUP(D2,'Master Register'!A4:V100,14)=2,VLOOKUP(D2,'Master Register'!A4:X2000,15)=2),"Low",IF(AND(VLOOKUP(D2,'Master Register'!A4:V100,14)=2,VLOOKUP(D2,'Master Register'!A4:X2000,15)=5),"High",IF(AND(VLOOKUP(D2,'Master Register'!A4:V100,14)=3,VLOOKUP(D2,'Master Register'!A4:X2000,15)=4),"High",VLOOKUP(VLOOKUP(D2,'Master Register'!A4:V100,14)*VLOOKUP(D2,'Master Register'!A4:X2000,14),'Risk Rating Table'!A1:B142,2))))))

Due to the nature of our risk calculator a overall score (ie 7) be either moderate or high depending on the likelihood. so for these scores that can duplicate i have created the IF statements for else if go to the Risk Rating Table. so the number in the 14th cell along in the sheet master register is 1 and the liklihood in the cell 15th along is the liklihood.

It is quiet a mess i Know.

=IF(AND(VLOOKUP(D2,'Master Register'!A5:X2000,14)=1,B1=3),"Low",IF(AND(VLOOKUP(D2,'Master Register'!A5:X2000,14)=1,B1=4),"Mod",IF(AND(VLOOKUP(D2,'Master Register'!A5:X2000,14)=2,B1=2),"Low",IF(AND(VLOOKUP(D2,'Master Register'!A5:X2000,14)=2,B1=5),"High",IF(AND(VLOOKUP(D2,'Master Register'!A5:X2000,14)=3,B1=4),"High",VLOOKUP(VLOOKUP(D2,'Master Register'!A5:X2000,14)*B1,'Risk Rating Table'!A1:B142,2))))))

This is the code I am using on another template that works fine, I used the lookup in b2 to simplify this formula however cant afford the space on the new template. can any1 see what I am doing wrong?

Hope this makes some sence.




Trying to lookup a number value that is populated by a formula:

=IF($B7="","",INDEX(Master!K:K,MATCH($B$3,Master!$E:$E,0)+'Site F2R Sheet'!$B7-1))
returns a value of "2875453" in cell E7

My vlookup calc uses this value to bring back an associated model type for this value.
This only returns #NA.

My table is sorted in the 1st column but there are blank columns in the array. I have tried removing the empty columns but still get the error.

Any ideas?


Dear All

I am currently working to on piece where I am receiving various files from different sources they are all in same format. I need to compile those together in one master sheet but the master sheet's column's are'nt same as the source file plus the master sheet requires me to pull data from another file which contains individual's business area. I have put togeher a script below which as long as I copy data from first file is fine but when I try to run this script on another file it doesn't work. I tried to use .End(xlDown) but it starts copying data from last row meaning the last row gets overrided by the new set of data.

Sub ComplileData()

Dim FileName As String

FileName = InputBox("Please enter exact file name")

With Workbooks(FileName).Sheets("Cntry_Data")
.Range(.Range("A5"), .Range("A65536").End(xlUp)).Copy Workbooks("Template").Worksheets("Main").Range("A5")
End With

With Workbooks(FileName).Sheets("Cntry_Data")
.Range(.Range("B4"), .Range("B65536").End(xlUp)).Copy Workbooks("Template").Worksheets("Main").Range("B4")
End With

End Sub

Can anyone please help

I have a "cat_no" file and a "master inventory" file.
I need to do a vlookup to match the catalog #'s and mfr name in the cat_no file and the master inventory file. I also need to pull the pricing and packing string from the master inventory file and add it to the cat_no file where item numbers match.

Could someone please help on the vlookup formula?

Please let me know if I did not provide enough info

I work at a property managmement company and am creating a workbook to track apartment renovations. I have created 13 spserate worksheets in the workbook. I have a master for the properties and their management team to refer to and 12 monthly sheets. I am attempting to have the managers enter the data and then would like to use an "IF" function to send all of Februaries respevtive entries to the February worksheet. I am atta/ching a file with a blank January and a master worksheet. How is a range of dates set in an "IF" function? I have a attached the file and would appreciate any input

Example) Here are just a couple of attempted variations of the function to get info from worksheet, but it is not working for me. They are located on the January worksheet in cells B4 thru B10.




I'm trying to create a custom function that will basically do a vlookup and return a result from the same table everytime, but that is not included in the workbook.

For example today someone sends me a file, in column B is a product code, I open up a workbook that contains a master list of product codes and prices, and use the vlookup function in the file I received to lookup the price from my workbook that contains the prices.

in received workbook cell B6 I type:
=VLOOKUP(B5,'[Master File.xls]Sheet1'!$K$2:$L$19751,2,FALSE)

I'm looking for a way to store the lookup table that is currently in [Master File.xls]Sheet1'!$K$2:$L$19751 somehow in VBA so that I could write a custom function such as:


that would return the lookup value from the second column of my prices table, but I don't want to have include the table in the workbook that I'm working on. Is there a way to store a worksheet associated with a custom function in the personal macro workbook, or as part of an add-in ? Or any other way that anyone knows of to do something like this?

If anyone can get me pointed in the right direction on this, it would be very much appreciated.


I have around 700 workbooks in a folder on my PC.

My master file(master database.xlsx) is kept open from which i have to Vlookup the values into these 700 workbooks.

The "agent Id" column is common in both the master database.xlsx as well as those other workbooks also.

The thing i want to do is i need the data from the "reporting supervisor" which is in the master database.xlsx file to be available in the G column of the other 700 books ( i have attached only 4 sample books). The G column can be then named as "Reporting Supervisor". Since "Agent ID" is available in both files we can vlookup taking this as a base. I have attached the 4 sample files out of 700 as well as the master database file in a zip file.

Hope my question is clear to you. Please give a nice solution to this or else i have to open all the 700 files and do vlookup one by one.

I have a list of files/or folders in a folder called "C:\Files".

They are currently named in the format "dd-mm-yyyy". Or it can be named "TheReport dd-mm-yyyy"
I would like to rename them in the format "yyyymmdd". Or to the name "TheReport yyyymmdd"

how can I do this?

It should get the date value from the date which is filename on the file i am trying to replace.

The files can be, .xls, .zip etc.. So i hope this can be made dynamic accordingly?

I would also like to execute this via Excel VBA,hence why I am asking this question in this forum.


Good day,

I hope that I am not asking too much with this one.

I have a workbook with 3 worksheets, 2 containing data and 1 used as a search and display sheet. I enter information into a cell, press enter and the relevant data is displayed in various cells.

I would like to create a userform to do the job of the search/display sheet, this is mainly to make it look more professional!

What do I need to add to the userform? Textboxes, Labels, Command Buttons etc, etc.

How would I go about transferring/copying the following data into a userform?



A1 B1 C1 D1 E1 P1 =TODAY() =NOW() A3 =COUNT(Master!A4:A301) Enter Product code below P N M E F S W S S G R A5 =VLOOKUP(C5,Master,7,FALSE) =VLOOKUP(C5,Master,8,FALSE) =VLOOKUP(C5,Master,9,FALSE) =VLOOKUP(C5,Master,10,FALSE) =VLOOKUP(C5,Master,11,FALSE) =VLOOKUP(C5,Master,12,FALSE) =VLOOKUP(C5,Master,13,FALSE) =VLOOKUP(C5,Master,14,FALSE) =VLOOKUP(C5,Master,15,FALSE) =VLOOKUP(C5,Master,16,FALSE) =VLOOKUP(C5,Master,17,FALSE) B M O M C M G H L S P A7 Product details =VLOOKUP(C5,Master,18,FALSE) =VLOOKUP(C5,Master,19,FALSE) =VLOOKUP(C5,Master,20,FALSE) =VLOOKUP(C5,Master,21,FALSE) =VLOOKUP(C5,Master,22,FALSE) =VLOOKUP(C5,Master,23,FALSE) =VLOOKUP(C5,Master,24,FALSE) =VLOOKUP(C5,Master,25,FALSE) =VLOOKUP(C5,Master,26,FALSE) =VLOOKUP(C5,Master,27,FALSE) =VLOOKUP(C5,Master,28,FALSE) Des A9 =IF(ISNA(VLOOKUP(C5,Master,2,FALSE)),"Incorrect Product code",VLOOKUP(C5,Master,2,FALSE)) =VLOOKUP(C5,Master,29,FALSE) =VLOOKUP(C5,Master,30,FALSE) =VLOOKUP(C5,Master,31,FALSE) =VLOOKUP(C5,Master,32,FALSE) =VLOOKUP(C5,Master,33,FALSE) =VLOOKUP(C5,Master,34,FALSE) =VLOOKUP(C5,Master,35,FALSE) =VLOOKUP(C5,Master,36,FALSE) =VLOOKUP(C5,Master,37,FALSE) =VLOOKUP(C5,Master,38,FALSE) AL =VLOOKUP(C5,Master,39,FALSE) =VLOOKUP(C5,Master,40,FALSE) =VLOOKUP(C5,Master,41,FALSE) =VLOOKUP(C5,Master,42,FALSE) =VLOOKUP(C5,Master,43,FALSE) =VLOOKUP(C5,Master,44,FALSE) =VLOOKUP(C5,Master,45,FALSE) =VLOOKUP(C5,Master,46,FALSE) =VLOOKUP(C5,Master,47,FALSE) =VLOOKUP(C5,Master,48,FALSE) A11 =VLOOKUP(C5,Master,3,FALSE) =VLOOKUP(C5,Master,49,FALSE) =VLOOKUP(C5,Master,50,FALSE) =VLOOKUP(C5,Master,51,FALSE) =VLOOKUP(C5,Master,52,FALSE) =VLOOKUP(C5,Master,53,FALSE) =VLOOKUP(C5,Master,54,FALSE) =VLOOKUP(C5,Master,55,FALSE) =VLOOKUP(C5,Master,56,FALSE) =VLOOKUP(C5,Master,57,FALSE) =VLOOKUP(C5,Master,58,FALSE) Wa =VLOOKUP(C5,Master,59,FALSE) =VLOOKUP(C5,Master,60,FALSE) =VLOOKUP(C5,Master,61,FALSE) =VLOOKUP(C5,Master,62,FALSE) =VLOOKUP(C5,Master,63,FALSE) =VLOOKUP(C5,Master,64,FALSE) =VLOOKUP(C5,Master,65,FALSE) =VLOOKUP(C5,Master,66,FALSE) =VLOOKUP(C5,Master,67,FALSE) =VLOOKUP(C5,Master,68,FALSE) A13 =VLOOKUP(C5,Master,4,FALSE) =VLOOKUP(C5,Master,69,FALSE) =VLOOKUP(C5,Master,70,FALSE) =VLOOKUP(C5,Master,71,FALSE) =VLOOKUP(C5,Master,72,FALSE) =VLOOKUP(C5,Master,73,FALSE) =VLOOKUP(C5,Master,74,FALSE) =VLOOKUP(C5,Master,75,FALSE) =VLOOKUP(C5,Master,76,FALSE) =VLOOKUP(C5,Master,77,FALSE) =VLOOKUP(C5,Master,78,FALSE) Ar =VLOOKUP(C5,Master,79,FALSE) =VLOOKUP(C5,Master,80,FALSE) =VLOOKUP(C5,Master,81,FALSE) =VLOOKUP(C5,Master,82,FALSE) =VLOOKUP(C5,Master,83,FALSE) =VLOOKUP(C5,Master,84,FALSE) =VLOOKUP(C5,Master,85,FALSE) =VLOOKUP(C5,Master,86,FALSE) =VLOOKUP(C5,Master,87,FALSE) =VLOOKUP(C5,Master,88,FALSE) A15 =VLOOKUP(C5,Master,5,FALSE) =VLOOKUP(C5,Master,89,FALSE) =VLOOKUP(C5,Master,90,FALSE) =VLOOKUP(C5,Master,91,FALSE) =VLOOKUP(C5,Master,92,FALSE) =VLOOKUP(C5,Master,93,FALSE) =VLOOKUP(C5,Master,94,FALSE) =VLOOKUP(C5,Master,95,FALSE) A17 =COUNTA(Ingredients!A4:A200) =COUNTIF(Master!C4:C302,"Yes") Enter Part code below =(C19) Comprises A19 =VLOOKUP(C19,ing,3,FALSE) =VLOOKUP(C19,ing,4,FALSE) =VLOOKUP(C19,ing,5,FALSE) =VLOOKUP(C19,ing,6,FALSE) =VLOOKUP(C19,ing,7,FALSE) Des =VLOOKUP(C19,ing,8,FALSE) =VLOOKUP(C19,ing,9,FALSE) =VLOOKUP(C19,ing,10,FALSE) =VLOOKUP(C19,ing,11,FALSE) =VLOOKUP(C19,ing,12,FALSE) A21 =IF(ISNA(VLOOKUP(C19,ing,2,FALSE)),"Incorrect Part code",VLOOKUP(C19,ing,2,FALSE)) =VLOOKUP(C19,ing,13,FALSE) =VLOOKUP(C19,ing,14,FALSE) =VLOOKUP(C19,ing,15,FALSE) =VLOOKUP(C19,ing,16,FALSE) =VLOOKUP(C19,ing,17,FALSE) A23 E23 P23

Hello Experts

I have 2 workbooks Mater and Update.
I have a VBA code that compare between the two files column by column and update the Master file according to the Update file ( the code below).

Now my user want to see in a separate worksheet ("changes") only the changes - Date and time of changes at column A , old value at column B and new value at column C
Can someone help combine the new changes at the code below
I want that the new worksheets will show all the changes without deleting them.
I hope that I made myself clear

Shark Man


workbooks.Open ("Update.xls")
LR = Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To LR
        Rw = 0
        On Error Resume Next
        Rw = WorksheetFunction.Match(Range("A" & i), Workbooks("Master.xls").Sheets("Sheet1").Range("A:A"), 0)
        If Rw > 0 Then
            With Workbooks("Master.xls").Sheets("Sheet1").Cells(Rw, "B")
                If Not .Value = Cells(i, "B") Then
                    .Value = Cells(i, "B")
                    .Interior.ColorIndex = 6
                End If
            End With
            With Workbooks("Master.xls").Sheets("Sheet1").Cells(Rw, "C")
                If Not .Value = Cells(i, "C") Then
                    .Value = Cells(i, "C")
                    .Interior.ColorIndex = 6
                End If
            End With
            With Workbooks("Master.xls").Sheets("Sheet1").Cells(Rw, "D")
                If Not .Value = Cells(i, "D") Then
                    .Value = Cells(i, "D")
                    .Interior.ColorIndex = 6
                End If
            End With
            With Workbooks("Master.xls").Sheets("Sheet1").Cells(Rw, "E")
                If Not .Value = Cells(i, "E") Then
                    .Value = Cells(i, "E")
                    .Interior.ColorIndex = 6
                End If
            End With
            With Workbooks("Master.xls").Sheets("Sheet1").Cells(Rw, "F")
                If Not .Value = Cells(i, "F") Then
                    .Value = Cells(i, "F")
                    .Interior.ColorIndex = 6
                End If
            End With
            With Workbooks("Master.xls").Sheets("Sheet1").Cells(Rw, "G")
                If Not .Value = Cells(i, "G") Then
                    .Value = Cells(i, "G")
                    .Interior.ColorIndex = 6
                End If
            End With
            With Workbooks("Master.xls").Sheets("Sheet1").Cells(Rw, "H")
                If Not .Value = Cells(i, "H") Then
                    .Value = Cells(i, "H")
                    .Interior.ColorIndex = 6
                End If
            End With
            With Workbooks("Master.xls").Sheets("Sheet1").Cells(Rw, "I")
                If Not .Value = Cells(i, "I") Then
                    .Value = Cells(i, "I")
                    .Interior.ColorIndex = 6
                End If
            End With
            With Workbooks("Master.xls").Sheets("Sheet1").Cells(Rw, "J")
                If Not .Value = Cells(i, "J") Then
                    .Value = Cells(i, "J")
                    .Interior.ColorIndex = 6
                End If
            End With
            With Workbooks("Master.xls").Sheets("Sheet1").Cells(Rw, "K")
                If Not .Value = Cells(i, "K") Then
                    .Value = Cells(i, "K")
                    .Interior.ColorIndex = 6
                End If
            End With
            With Workbooks("Master.xls").Sheets("Sheet1").Cells(Rw, "L")
                If Not .Value = Cells(i, "L") Then
                    .Value = Cells(i, "L")
                    .Interior.ColorIndex = 6
                End If
            End With
        End If
    Next i

Workbooks("Update.xls").Close False

In Excel2000, I have the following function:

Function WC_Name(ByVal WC)

WC_Name = Application.WorksheetFunction.VLookup(WC,
Workbooks("master.xls").Worksheets("fall06").Range("b4:n500"), 13,

End Function

The problem is that it does not work if the "master" file is not open.
How can I have the function open the file if it isn't already?


I have the following code in a macro and when it is run I get a Run Time Error 438 Object doesn't support this property or method. This occurs at the first occurrance of the destination/source. Any suggestions?


Sub TestDatabase()
' TestDatabase Macro
' Macro recorded 11/12/2008 by JLZ
' Keyboard Shortcut: Ctrl+t
' syntax is [destination].Value = [source].Value         
    Windows("File 2.xls").Range("B3").Value = Windows("File 1.xls").Range("E2").Value
    Windows("File 2.xls").Range("C3").Value = Windows("File 1.xls").Range("E3").Value
    Windows("File 2.xls").Range("D3").Value = Windows("File 1.xls").Range("E4").Value
    Windows("File 2.xls").Range("E3").Value = Windows("File 1.xls").Range("C6").Value
    Windows("File 2.xls").Range("F3").Value = Windows("File 1.xls").Range("C7").Value
    Windows("File 2.xls").Range("G3").Value = Windows("File 1.xls").Range("C8").Value
    Windows("File 2.xls").Range("H3").Value = Windows("File 1.xls").Range("E8").Value
    Windows("File 2.xls").Range("I3").Value = Windows("File 1.xls").Range("E9").Value
    Windows("File 2.xls").Range("J3").Value = Windows("File 1.xls").Range("E28").Value
    Windows("File 2.xls").Range("K3").Value = Windows("File 1.xls").Range("E29").Value
    Windows("File 2.xls").Range("L3").Value = Windows("File 1.xls").Range("E30").Value
    Windows("File 2.xls").Range("M3").Value = Windows("File 1.xls").Range("E31").Value
    Windows("File 2.xls").Range("N3").Value = Windows("File 1.xls").Range("E33").Value
    Windows("File 2.xls").Range("O3").Value = Windows("File 1.xls").Range("B29").Value
    Windows("File 2.xls").Range("P3").Value = Windows("File 1.xls").Range("B31").Value
    Windows("File 2.xls").Range("Q3").Value = Windows("File 1.xls").Range("B33").Value
    Windows("File 2.xls").Range("R3:V3").Value = Windows("File 1.xls").Range("A12:E12").Value
    Windows("File 2.xls").Range("W3:AA3").Value = Windows("File 1.xls").Range("A13:E13").Value
    Windows("File 2.xls").Range("AB3:AF3").Value = Windows("File 1.xls").Range("A14:E14").Value
    Windows("File 2.xls").Range("AG3:AK3").Value = Windows("File 1.xls").Range("A15:E15").Value
    Windows("File 2.xls").Range("AL3:AP3").Value = Windows("File 1.xls").Range("A16:E16").Value
    Windows("File 2.xls").Range("AQ3:AU3").Value = Windows("File 1.xls").Range("A17:E17").Value
    Windows("File 2.xls").Range("AV3:AZ3").Value = Windows("File 1.xls").Range("A18:E18").Value
    Windows("File 2.xls").Range("BA3:BE3").Value = Windows("File 1.xls").Range("A19:E19").Value
    Windows("File 2.xls").Range("BF3:BJ3").Value = Windows("File 1.xls").Range("A20:E20").Value
    Windows("File 2.xls").Range("BK3:BO3").Value = Windows("File 1.xls").Range("A21:E21").Value
    Windows("File 2.xls").Range("BP3:BT3").Value = Windows("File 1.xls").Range("A22:E22").Value
    Windows("File 2.xls").Range("BU3:BY3").Value = Windows("File 1.xls").Range("A23:E23").Value
    Windows("File 2.xls").Range("BZ3:CD3").Value = Windows("File 1.xls").Range("A24:E24").Value
    Windows("File 2.xls").Range("CE3:CI3").Value = Windows("File 1.xls").Range("A25:E25").Value
    Windows("File 2.xls").Range("CJ3:CN3").Value = Windows("File 1.xls").Range("A26:E26").Value
    Windows("File 2.xls").Range("CO3:CS3").Value = Windows("File 1.xls").Range("A27:E27").Value
    Windows("File 2.xls").Range("B3").Value = Windows("File 1.xls").Range("E2").Value

    Windows("File 2.xls").Activate
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown
    Windows("File 1.xls").Activate

End Sub

Hello All,

I have 22 separate files that I am attempting to pull information from using vlookup (back to a master file). All files are stored on a shared drive and each file is accessed by a different user. I am attempting to direct vlookup to a specific file (i.e. doe.xls) based upon the user name in the master file. If user = John Doe lookup data in doe.xls. Therefore, instead of sifting through 22 files it would go directly to the source file.

I am familiar with vlookup, but I am not sure if this particular command is possible.

Any assistance would be greatly appreciated.

Thanks in advance!


Hi. I need to take several files within a signle folder, and copy every
sheet with each file and paste it as it's own sheet into a master file.

So for instance:
File 1 has 3 sheets
File 2 has 5 sheets
File 3 has 1 sheet

The master file will then have 9 sheets. Basically, move each sheet from
each file into 1 file.
I have a start to this, but it only copied sheet1 of each file in a
directory. I don't know how to make it copy every sheet:

Sub OpenAllExcelFiles()
'based on a Tom Ogilvy example
With Application.FileSearch

.LookIn = "C:\Data" '<== set the directory
.SearchSubFolders = False
.Filename = "*.xls"
.MatchTextExactly = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
Set wkbk = Workbooks.Open(Filename:=.FoundFiles(i))
wkbk.Worksheets("sheet1").Copy _
after:=ThisWorkbook.Sheets(1) 'Change sheet name
Next i
MsgBox "There were no files found."
End If
End With
End Sub

I'm a frequent user of vlookup. Recently I'm trying to use vlookup in an exported excel spreadsheet and the vlookup formula comes up as N/A.

I've checked all the data and they are all set as "General" not "Text", checked the Tool option "Formulas" tab is all default setting.

I know the formula is vlookup(lookup value, table array, column, true or false)

I've even copied the "lookup value" from the table array to ensure they match.

What else could i do?

I opened a new workbook to test the formula and it works but it won't work in the original workbook.


Thank you all for your help,
I need your help once again for consolidating various files into my master file , I found similar codes from site but my requirement is bit different from those.

I have a master file in my C:/Master folder with Heading in row 2( Same as all other files)
and I have some 100 files in C:/working_Files folder( Heading in 301th row and datas from there on)

I need a macro in my master file to consolidate the data from all those 100 files ( starts from 302 row in all the files) and put in my master file from row 3 onwards one afterthe other)
however this should happen based on a condition
If I enter the Date in Cell A1 in my master file( Say 05-Dec-2010) Then look in Column AC(Processed Date) in all those 100 files, filter the data by the above date and get the data to my master file
Next time if I change the date and run again, then get data only for this date and paste below. But by mistake if I keep the same date and run the macro then it should tell me that "I am trying to import duplicate data"

Cross post :

Hello all

I have 2 spreadsheets.
1 master and 1 slave. The slave file contains all raw data and called RAW.xls
The master file has various formuls whichn looks for certain data within RAW.xls.

the key formula is below.


The issue I now have, is that I need to send this file around to other collegues.
However whenever they open this, all the cells are #REF. I beleive this is because it needs the RAW.xls open as well to grab the data.

2 questions.

1: is there a way to run the formulas and have the data recorded saved.
2: is there a way to have a button to re-run the formulas based on updated data from RAW.xls. This would only be something someone does when they have the RAW file open as well.

I do not know much about VBA or marcos..just to be difficult!

thank you one and all!

Hi all,

Sorry for my first post to be asking for help. I don't have any experience with macros, but I'm trying to create one which will allow me to copy rows from one workbook to another based on whether a row with a unique ID already exists in the destination file.

I have two files, a master file called "master transfer file.xls" and then will get report files called "new data.xls." Both files have the same columns (but the master file has some additional manually entered columns at the far right. The new data files will have some records that are already in the master file and I want to ignore those (if column E in the row in the new data file equals any row column E in the master file. If the the value for column E doesn't already exist in the master file, I want to copy that row.

Can anyone provide a suggestion or point me in the right direction?



I have an issue reading in dates from a text file. The text files are output from a GIS program and if the record IS a date then it is out put in YYYYMMDD format e.g. 20070828. This is fine and my code (which I will attach later) handles this circumstance fine. However things go awry when MapInfo outputs a date that is actually a string e.g "27/08/2008". My code tries to interpret this as a date but ends up reverting to MDY and it is uncertain the result I will get in Excel.

Basically I need a way to parse the text file first to check if the record is a string or a date. Any one have any suggestions?

Below is the Code to open the text file and as you can see it is expecting a date in YMD format.


Workbooks.OpenText FileName:=vrtSelectedItem _
                     , Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _
                      :=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
                       False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array _
                       (1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8 _
                       , 1), Array(9, 1), Array(10, 1), Array(11, 5), Array(12, 5), Array(13, 5), Array(14, 1), _
                       Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 5)), TrailingMinusNumbers:=True



I have created a macro that combines all tab delimited text files within a specified folder into one master file (Excel). I would like to name this file "Master" & "doff" where "doff" is the date that the first file to be imported was created or last modified as viewed by the operating system. Is this possible in Excel VBA? Thanks!

I have had some help previouslly from some great people on this board regarding a file search issue I was having. I would now like to refine that process a little. Currently I use an array for my file names. This file is saved approximately every 30 mins, it is saved under the file name date/time/file.csv (Ex: 20100531 0730campdata.csv). With my array I do a wild card search to find the criteria I need and then save it to our local server under a different name. This causes my array to be very long as I have to list out all the possible time combinations.


Sub IntraDayUpdate()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Application.StatusBar = ("Please wait ... calculations in progress!!")

Dim c00         As String
Dim c01         As String
Dim MyArr       As Variant
Dim MyArrCnt    As Long

c00 = "\\wpccp001if\Apps\C3PO\PMG - Ben's\Network MPT Inclusion\ECMReports_Daily\"
c01 = c00 & Format(Date, "yyyymmdd")
MyArr = Array(" 220", " 215", " 214", " 213", "212", "211", " 210", " 205", " 204", " 203", "202", "201", " 160", " 155", " 154", " 153", "152", "151", " 120", " 115", " 114", " 113", "112", "111", " 080", " 075", " 074", " 073", "072", "071")
On Error Resume Next
        Workbooks.Add c00 & Dir(c01 & MyArr(MyArrCnt) & "*campdata.csv")
        If Err.Number > 0 Then
            Err.Number = 0
            MyArrCnt = MyArrCnt + 1
            Exit Do
        End If
        If MyArrCnt > UBound(MyArr) Then
            MsgBox "File not found.  Please ensure file is available."
            Exit Sub
        End If

On Error GoTo 0
    ActiveWorkbook.SaveAs Filename:= _
        "\\\Groups47\Fraud\Ops Desk\Penetration Database\campdata.xls", FileFormat:= _
        xlExcel9795, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
        False, CreateBackup:=False

Is there a way to refine this process so that when I run the macro it looks for the two files I need but for whichever was saved most recently. So if I run it at 800 It will pull the files that were saved at 730. Right now it searches for an 800 file then a 750, 740 until it finds one? I would like to clean up this code a little bit. Any help would be greatly appreciated.

I have two workbooks. One is a no-nonsense form interface that my bosses will use to enter safety information. I'll call this workbook "Form". This file is stored locally on each of their computers. The other workbook is stored on a common drive. I'll call it "Master".

When my bosses fill out the Form and click "Submit", the Master file is opened, and certain cells are populated based on information entered in the Form.

This is the code I am using to make this happen:


    Workbooks.Open FileName:="V:\Field Check-Ins\MASTER.xls"
    Sheets("Check Ins").Activate

This opens the Master and activates the appropriate sheet, but I want it to do mo

What I want it to do is check to see if Master is open first. If it is, I want it to go ahead and activate the appropriate sheet. If Master is not open, then I want it to open Master.

Is this possible? Thanks in advance!!