Apply Vlookup formula in all the available sheets in a workbook

Hello Experts,

This is my first thread here.

I am not an expert in EXCEL/VBA but, love to gain knowledge about and by learning from people like you I have gain knowledge to the basics of VBA.

Here's my query I have two workbooks having multiple sheets. I want to apply a vlookup in Column "Q" in Book2.xlsm and the vlookup should reference data from Book1.xlsx.

I have written a code that works perfectly in sheet1 on Book2.xlsm as compared to Sheet1 in Book1.xlsx but, it doesn't works in rest of the worksheets.

Code:

---

Option Explicit
Sub MakeFormulas()
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceBook As Workbook
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet
Dim C As Integer
Dim I As Integer

C = ActiveWorkbook.Worksheets.Count

For I = 1 To C


Application.ScreenUpdating = True

'Where is the source workbook?
Set sourceBook = Workbooks.Open("C:\")

'what are the names of our worksheets?
Set sourceSheet = sourceBook.Worksheets("Sheet1")
Set outputSheet = ThisWorkbook.Worksheets("Sheet1")


'Determine last row of source
With sourceSheet
    SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    With outputSheet
    'Determine last row in col P
  OutputLastRow = .Cells(.Rows.Count, "P").End(xlUp).Row
    'Apply our formula
  .Range("Q2:Q" & OutputLastRow).Formula = _
        "=VLOOKUP($A2,'[" & sourceBook.Name & "]" & sourceSheet.Name & "'!$A$2:$P$" & SourceLastRow & ",3,0)"
End With
MsgBox ActiveWorkbook.Worksheets(I).Name
       
    Next I

   
'Close the source workbook, don't save any changes
sourceBook.Close False
Application.ScreenUpdating = True
End Sub

---

The number of sheets in both the workbooks may increase or decrease. Currently there are more than 60+ worksheets in both the books. I have attached examples of how the Workbooks will look like.

Thanks
Sumit

Hello Sumit
Welcome to Excel Fox…

I am a bit confused ….

_1) You are looping from 1 To C , which is looping for all the worksheets in the active workbook. It is not clear to me what should be the active workbook? (The active workbook is that which you "see" in front of you when the macro is running)

_2) Inside that loop, you always set the source sheet and the output sheet to the same worksheet every time
Set sourceSheet = sourceBook.Worksheets("Sheet1")
Set outputSheet = ThisWorkbook.Worksheets("Sheet1")

So in each loop, you paste exactly the same formula into the same range . - You do exactly the same thing 8 times in the same worksheet, "Sheet1" .
I expect you are not intending to do that.

_3) In each loop, your message box gives the worksheet name of each worksheet in the active workbook. I am not sure what the purpose is of this message is?


I am not exactly sure what it is that you want to do.

If I assume that the active workbook that you are referring to is possibly "Book2.xlsm", then possibly this is something like you want…

Code:

---

Option Explicit
Sub MakeFormulas()
Dim SourceLastRow As Long, OutputLastRow As Long
Dim sourceBook As Workbook, sourceSheet As Worksheet, outputSheet As Worksheet
Dim C As Integer, I As Integer

'C = ActiveWorkbook.Worksheets.Count
 Let C = ThisWorkbook.Worksheets.Count
    'For I = 1 To C
'Application.ScreenUpdating = True

'Where is the source workbook?
Set sourceBook = Workbooks.Open(ThisWorkbook.Path & "\Book1.xlsx")

    For I = 1 To C  '  ......
    'what are  our worksheets?                        I  =  1        ,      2 ,      3    ..........
    Set sourceSheet = sourceBook.Worksheets.Item(I) '    ("Sheet1")  , Sheet2  , Sheet3 ........
    Set outputSheet = ThisWorkbook.Worksheets.Item(I) ' ("Sheet1")    , Sheet2  , Sheet3 ........
        'Determine last row of source
        With sourceSheet
        SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        End With
       
        With outputSheet
        'Determine last row in col P
        OutputLastRow = .Cells(.Rows.Count, "P").End(xlUp).Row
        'Apply our formula
        .Range("Q2:Q" & OutputLastRow).Formula = "=VLOOKUP($A2,'[" & sourceBook.Name & "]" & sourceSheet.Name & "'!$A$2:$P$" & SourceLastRow & ",3,0)"
        End With
    'MsgBox ActiveWorkbook.Worksheets(I).Name
    MsgBox ActiveWorkbook.Worksheets.Item(I).Name
    Next I
'Next P
   
'Close the source workbook, don't save any changes
 sourceBook.Close False
' Application.ScreenUpdating = True
End Sub
 Sub oops()

---

_._____________________________________________


Here is just some extra info that might be helpful

Active stuff
The ActiveSheet is that sheet which you are "looking" at / have up in front of you at the time at which the code line containing ActiveSheet is executed.
The ActiveWorkbook is the Workbook containing the ActiveSheet, that is to say, the ActiveWorkbook is the Workbook containing sheet which you have in front of you at the time at which the code line containing ActiveWorkbook is executed

Referring to worksheets by their string .Name or Item number
You can refer to a worksheet by its string Name, or its Item number

String Name:
Worksheets.Item("Sheet1") or Worksheets("Sheet1")
Worksheets.Item("MyWorksheet") or Worksheets("MyWorksheet")
etc…

Item Number:
Worksheets.Item(1) or Worksheets(1);
Worksheets.Item(2) or Worksheets(2)
etc…..
Worksheets Item Number.JPG :
https://imgur.com/LzXqHNM
Attachment 2556

Note:
The worksheet Item number is always the tab number counting from the left, it is not directly related to the string Name:
Worksheets Item Number.JPG : https://imgur.com/BHfYC99
Attachment 2557
Worksheets.Item(1) is always the first worksheet, regardless of its string name.
Worksheets.Item(2) is always the second worksheet, regardless of its string name
etc....

If you move a worksheet, then its Name does not change. Its Item number changes to reflect its position counting from the left
Attachment 2558Attachment 2559





Alan

Hi Alan,

Thanks a lot. Your code is working just fine.

I know I was a bit confusing when writing this thread but, you're right Book2.xlsm is the activeworkbook. I got confused on how to get the Loop work through both the source and output workbook.

The extra information provided by you is of good help I'll keep copy of that.

I have some more queries on the above code:

1. The sourceBook and ActiveWorkook changes each month. For E.g. current Activeworkbook Book2.xlsm will become the Sourcebook next month and a new Activeworkbook Book3.xlsm will come up and so on. Each month a new activeworkbook comes up and the last activeworkbook becomes sourcebook. Will the above code still work in this scenario?

2. Can we have a new sheet created in the activeWorkbook which will accumulate the Output from every sheets in the activeworkbook and give the output. Just a thought came up in my mind, not mandatory

3. Lastly, the Output Column "Q" in each worksheets of the Activeworkbook to be colored. E.g, if the value in a cell in Column Q is more than 3 or 3% cell color to be "Green" and if the value is less than Minus (-)3 or (-)3% than "Red".

I hope I am fair enough in my questions.

Thanks a lot once more.

Sumit

Hello Sumit


Regarding Question 2.
I do not completely understand exactly what you finally want to have. I am not sure exactly what results you want to accumulate.
So I will give you just some general ideas , and maybe you can adapt them to your exact needs.

This code section will add the worksheet "Records" , if it does not exist to the ActiveWorkbook. The way it works is to see if an arbitrary cell in the worksheets can be referenced to. ( Any cell will do ). If that can't be done then it most likely is because the worksheet does not exist. If that is the case, then the worksheet is Added
It is probably a good idea to have an extra sheet to keep track of things. Amongst other things you would be able to have a record of all the previous workbook names. This will help in your requirement indicated in your question 1.

Code:

---

Sub MakeWorkSheetIfNotThere()
Dim Wb As Workbook '                                  ' ' Dim:  ' Preparing a "Pointer" to an Initial "Blue Print" in Memory of the Object ( Pigeon Hole with a bit of paper or code lines on that can be filled in to refer to a specific Objec of this type ) . This also us to get easily at the Methods and Properties throught the applying of a period ( .Dot) ( intellisense )                    '
 Set Wb = ActiveWorkbook '  '                            Set now (to Active Workbook - one being "looked at"), so that we carefull allways referrence this so as not to go astray through Excel Guessing inplicitly not the one we want...        Set: Values are filled at the memory locations and the directions there are specified in the variable "Blue Print "Pointer". In this case a Filled in Blue Print is passed.      http://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it-4.html#post4387191                                '
    If Not Evaluate("=ISREF(" & "'" & "Records" & "'!Z78)") Then '  ( the '  are not important here, but iin general allow for a space in the worksheet name like  "My Records"
    Wb.Worksheets.Add After:=Wb.Worksheets.Item(Worksheets.Count) 'A sheeet is added and will be Active
    Dim wsRcds As Worksheet '
    Set wsRcds = ActiveSheet 'Rather than rely on always going to the active sheet, we referr to it Explicitly so that we carefull allways referrence this so as not to go astray through Excel Guessing implicitly not the one we want...    Set: Values are filled at the memory locations and the directions there are specified in the variable "Blue Print "Pointer". In this case a Filled in Blue Print is passed.      http://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it-4.html#post4387191            ' Values are filled at the memory locations and the directions there are specified in the variable "Blue Print "Pointer". In this case a Filled in Blue Print is passed.      http://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it-4.html#post4387191
    wsRcds.Activate: wsRcds.Cells(1, 1).Activate ' ws.Activate and activating a cell sometimes seemed to overcome a strange error
    Let wsRcds.Name = "Records"
    Else ' The worksheet is already there , so I just need to set my variable to point to it
    Set wsRcds = ThisWorkbook.Worksheets("Records")
    End If

End Sub


---

Regarding Question 1.
The basic type of coding can be adapted to suit your scenario.
The exact answer to this question depends on how you are organising yourself and your files:
It depends on how you organise
file names
and
what information you are keeping in your "Record" worksheet .
It will depend which file you have in from of you at any particular time
It will depend on where you typically store your files.
Etc… etc….

If , for example , you start the macro when you have the latest book open in from of you, then that is the ActiveWorkbook.
As I explained previously, that workbook can then initially be referred to in coding via ActiveWorkbook.
I suggest that when talking generally about your files, that you refer to you newest file as something other than AcitveWorkbook. I suggest you call it "your latest file", or "this months file" , or "your newest file" or "latest month's workbook" , etc.. If you refer to it as the ActiveWorkbook, then you are going to confuse me and yourself.
As I explained previously , in coding, ActiveWorkbook has a specific meaning: It is the workbook in front of you at any time. For example, after opening the source workbook, the source book then , at that specific time , becomes the ActiveWorkbook. That is not your choice. Excel VBA decides that.


If I assume that you start running the macro with the latest month's workbook open in front of you, then at the start of the macro doing something like this would be a good idea
Dim ThisMonthsLatestBook As Workbook
Set ThisMonthsLatestBook = ActiveWorkbook

Then, later in the coding, you would refer to your outputSheet like this
Set outputSheet = ThisMonthsLatestBook.Worksheets.Item(I)

It is very easy to get confused when using Active things such as ActiveCell, ActiveSheet, ActiveWorkbook. You must make sure you understand exactly what the definition of those things are..
As I mentioned above, it is very confusing to refer to any of your files as the "ActiveWorkbook".


Getting the name of last months workbook, the sourceBook…
This would be one way to refer to your sourcebook. Your information is rather sketchy and incomplete, so that makes it difficult to give specific help. I can only give you general ideas…

I will assume that all your workbooks are all stored in the same Folder. I will assume that your Workbook names are
Book1.xlsm
Book2.xlsm
Book3.xlsm
Etc..
So for example, if your latest workbook was Book3.xlsm , then, after setting ThisMonthsLatestBook = ActiveWorkbook , then you can get the actual name returned from
ThisMonthsLatestBook.Name
You can then manipulate that string , "Book3.xlsm" , to get the "3". You then know that your sourcebook has the name
"Book" & "3-1" & ".xlsm"
So you will know then the name of last month's file… You can then get at it via a code line like
Set sourceBook = Workbooks.Open(ThisWorkbook.Path & "\Book" & x & ".xlsx")

You previously referred to the source book with a .xlsx ending, not a .xlsm ending. So I am somewhat confused at how that file came to be an .xlsx rather than a .xlsm
Your information is rather sketchy and incomplete, so that makes it difficult to give specific help. I can only give then general ideas…

There may be a better way to get at your sourceBook. It will depend on all the factors that I mentioned, such as what information you are keeping of previous files and where. I personally would use the months name, or some other date indication in my file names.


Regarding Question 3.
I think something like that can easily be done via conditional formatting. I don't have any experience with that. It is , however, very common to do that. You should find lots of info on that on the internet , for example via Google. ( https://www.google.de/search?q=Condi...+on+cell+value ) If you can't figure out how to do that, then I could show you a coding alternative.


_.________________________________-

---

I suggest that you have a good thing about exactly how you want to organise things. Most things can be done in VBA and usually there are several ways to do it.


I am off now. But I will take another look in tomorrow.
If you need more detailed help, you may need to be a bit more specific about exactly what you want to do, what results you want to accumulate, and where those results should go… , etc… .. ,


Alan

Oops I made it confusing for you again.

Would try to explain.

1. The macro gives us results in Column "Q" of each sheets in ActiveWorkbook that is open in front of me but, I was wondering if rather than giving the results in Column "Q" in each sheets, can the macro provide all these results of Column "Q" in a new Sheet Called "Records". The new sheet "Records" should only have the name of each sheets and their respective results (that is suppose to be in Column "Q" in every sheet).

2. All the workbooks will be in same folder and will be named similarly for e.g. "Book1_Sep 2019", "Book2_Oct 2019", "Book3_Nov 2019"………….. And so on. Every month a new workbook will be added and this new workbook would be our OutputBook where we will run the macro and the previous months Workbook will be the Source. Like For November 2019 the Output book that will be opened in my front will be "Book3_Nov 2019" and "Book2_Oct 2019" will become the source and so on.

3. For coloring the Results if they are More than 3% "Green" and less than(-) 3% "Red", the easiest way is to apply Conditional formatting but, I have to apply the conditional formatting every month on each column "Q" in each sheets (since, each month the output will be in a new workbook) so, I was thinking if a code can make this process easier as well.

Thanks for all the time that you have spent on the code above.



Sumit

Hello Sumit

The next coding for you is here :
http://www.excelfox.com/forum/showth...ll=1#post11797

Download both attached files, saving them in the same folder
Open “Book4_Dec 2019.xlsm”
Run Sub MakeFormulas3()

That macro should open “Book3_Nov 2019.xlsm”
When the macro is finished, you should see a new worksheet in “Book4_Dec 2019.xlsm” :

_____ Workbook: Book4_Dez 2019.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I
1	Sheet1	Sheet2	Sheet3	Sheet4	Sheet5	Sheet6	Sheet7	Sheet8
2	#NV	#NV	#NV	#NV	#NV	#NV	#NV	#NV
3	#NV	#NV	#NV	#NV	#NV	#NV	#NV	#NV
4	#NV	#NV	#NV	#NV	#NV	#NV	#NV	#NV
5	#NV	#NV	#NV	#NV	#NV	#NV	#NV	#NV
6	#NV	#NV	#NV	#NV	#NV	#NV	#NV	#NV
7	#NV	#NV	#NV	#NV	#NV	#NV	#NV	#NV
8	#NV	#NV	#NV	#NV	#NV	#NV	#NV	#NV
9	#NV	#NV	#NV	#NV	#NV	#NV	#NV	#NV
10	23	23	23	23	23	23	23	23
11	23	23	23	23	23	23	23	23
12	23	23	23	23	23	23	23	23
13	23	23	23	23	23	23	23	23
14	23	23	23	23	23	23	23	23
15	23	23	23	23	23	23	23	23
16	23	23	23	23	23	23	23	23
17

Worksheet: Records

_._________________________________

Notes:
You must use names like
"Book1_Sep 2019", "Book2_Oct 2019", "Book3_Nov 2019" , "Book4_Dec 2019", "Book5_Jan 2020", "Book6_Feb 2020"………………

The coding will run only in the correct month. So, for example "Book4_Dec 2019" , will only run this month. ( You can easily change that by removing the Exit Sub

I do not fully understand what you mean by … if they are More than 3% "Green" and less than(-) 3% "Red" …..
It is not clear to me what % of what you are referring to.
It is not clear to me what the (-) means.
So I have applied a simple logic. You may be able to adjust that to exactly what you want.






Alan

Hi Alan,

You're a scientist the code's working great. This is what I was expecting. You've have been a great help.

Since, the output will be fetched in a separate sheet "Records" I can easily apply the conditional formatting accordingly.


Thanks Again! And

HAPPY HOLIDAYS!!!

Your welcome, thanks for the feedback.
And Happy Holidays to you too

Alan

Hi Alan,

I am using your code quite well with the previous file names eg Book4_Dec 2019 etc. but, the exact file names are as per the attached (MSCI Equity Index Constituents 20191130)and the code doesn't works would you please suggest any workaround.

the debugger shows

Code:

---

ThisMonthsLatestBook.Worksheets.Add After:=ThisMonthsLatestBook.Worksheets.Item(Worksheets.Count)

---

Also, I tried to edit the vlookup to get the output but it didn't worked

Code:

---

.Range("" & CL(I) & "2:" & CL(I) & "" & OutputLastRow).Value = "=VLOOKUP(" & outputSheet.Name & "!$A2,'" & sourceBook.Path & "\" & "[" & sourceBook.Name & "]" & sourceSheet.Name & "'!$A$2:$P$" & SourceLastRow & ",3,0)"

---

the formula should be the one in the code above minus (-) cell value in the range $P. I tried using -$P and also Range $P but, it didn't worked.


Thanks a lot agin

Hi,
In the next macro version here, http://www.excelfox.com/forum/showth...ll=1#post11828 , I have modified it so that it will try to open a file that has the name like "MSCI Equity Index Constituents " & [a date one month earlier than the date on the current file]

What it does basically is:
It looks at the date in the name of the current file, (the current file is the file from which the coding is running )
It then makes a string source book name which contains the date for one Month before the date in the name of the current file.

So if you run Sub MakeFormulas4() in the attached workbook, "MSCI Equity Index Constituents 20191231.xlsm" , then it will open a workbook saved in the same folder with the name "MSCI Equity Index Constituents 20191130.xlsm" ( "MSCI Equity Index Constituents 20191130.xlsm" is then what you refer to as your source book )



I don't understand the rest of what you are asking in your last post.


Alan