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
Worksheets Item Number.JPG
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
Worksheets Item Number.JPG
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
Worksheets Item Number.JPGWorksheets Item Number.JPG
Alan
Apply Vlookup formula in all the available sheets in a workbook
Reply With Quote
Bookmarks