Code:
Sub MakeFormulas3() ' http://www.excelfox.com/forum/showthread.php/2390-Apply-Vlookup-formula-in-all-the-available-sheets-in-a-workbook?p=11794&viewfull=1#post11794
Rem 1 ' Workbooks info
' 1a This months book, this workbook. It is the outout book for the current month
Dim ThisMonthsLatestBook As Workbook, LisWbName As String
Set ThisMonthsLatestBook = ThisWorkbook ' ActiveWorkbook
Let LisWbName = ThisMonthsLatestBook.Name
If InStr(7, LisWbName, Format(Now(), "MMM"), vbTextCompare) = 0 Then MsgBox Prompt:="This workbook is not for " & Format(Now(), "MMMM"): Exit Sub
Dim BookN As Long
Let BookN = Mid(LisWbName, 5, InStr(5, LisWbName, "_", vbBinaryCompare) - 5)
' 1b Last months book
Dim sourceBookName As String
Let sourceBookName = "Book" & BookN - 1 & "_" & Format(DateAdd("m", -1, Now()), "MMM YYYY") & ".xlsm"
Dim sourceBook As Workbook
Set sourceBook = Workbooks.Open(ThisWorkbook.Path & "\" & sourceBookName)
Rem 2 Make records worksheet 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"
ThisMonthsLatestBook.Worksheets.Add After:=ThisMonthsLatestBook.Worksheets.Item(Worksheets.Count) 'A sheeet is added and will be Active
Dim wsRcds As Worksheet '
Set wsRcds = ThisMonthsLatestBook.Worksheets.Item(ThisMonthsLatestBook.Worksheets.Count) '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
Rem 3 looping through worksheets
Dim C As Long, I As Long
'C = ActiveWorkbook.Worksheets.Count
'Let C = ThisWorkbook.Worksheets.Count
Let C = ThisMonthsLatestBook.Worksheets.Count - 1 ' -1 since last worksheet is records worksheet
'For I = 1 To C
'Application.ScreenUpdating = True
For I = 1 To C ' Sheet1 , Sheet2 , Sheet3 .......
'what are our worksheets? I = 1 , 2 , 3 ..........
Dim sourceSheet As Worksheet
Set sourceSheet = sourceBook.Worksheets.Item(I) ' ("Sheet1") , Sheet2 , Sheet3 ........
Dim outputSheet As Worksheet
Set outputSheet = ThisWorkbook.Worksheets.Item(I) ' ("Sheet1") , Sheet2 , Sheet3 ........
'Determine last row of source
With sourceSheet
Dim SourceLastRow As Long
SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With outputSheet
'Determine last row in col P
Dim OutputLastRow As Long
OutputLastRow = .Cells(.Rows.Count, "P").End(xlUp).Row
End With
'Apply our formula in records worksheet
With Worksheets("Records")
Let .Cells.Item(1, I).Value = sourceSheet.Name ' Header in column as worksheet name
'.Range("Q2:Q" & OutputLastRow).Formula = "=VLOOKUP($A2,'[" & sourceBook.Name & "]" & sourceSheet.Name & "'!$A$2:$P$" & SourceLastRow & ",3,0)"
.Range("" & CL(I) & "2:" & CL(I) & "" & OutputLastRow).Value = "=VLOOKUP(" & outputSheet.Name & "!$A2,'" & sourceBook.Path & "\" & "[" & sourceBook.Name & "]" & sourceSheet.Name & "'!$A$2:$P$" & SourceLastRow & ",3,0)"
' .Range("" & CL(I) & "2:" & CL(I) & "" & OutputLastRow).Value = .Range("" & CL(I) & "2:" & CL(I) & "" & OutputLastRow).Value
End With
'MsgBox ActiveWorkbook.Worksheets(I).Name
MsgBox ActiveWorkbook.Worksheets.Item(I).Name
Next I
'Next P
Rem 4
Dim cel As Range
With Worksheets("Records").UsedRange
For Each cel In .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
If IsError(cel.Value) Then
'
Else
If cel.Value < 3 Then
cel.Font.Color = vbRed
Else
cel.Font.Color = vbGreen
End If
End If
Next cel
End With
'Close the source workbook, don't save any changes
sourceBook.Close False
' Application.ScreenUpdating = True
End Sub
' https://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
Function CL(ByVal lclm As Long) As String 'Using chr function and Do while loop For example http://www.excelforum.com/excel-programming-vba-macros/796472-how-to-go-from-column-number-to-column-letter.html
Dim rest As Long 'Variable for what is "left over" after subtracting as many full 26's as possible
Do
' Let rest = ((lclm - 1) Mod 26) 'Gives 0 to 25 for Column Number "Left over" 1 to 26. Better than ( lclm Mod 26 ) which gives 1 to 25 for clm 1 to 25 then 0 for 26
' Let FukOutChrWithDoWhile = Chr(65 + rest) & FukOutChrWithDoWhile 'Convert rest to Chr Number, initially with full number so the "units" (0-25), then number of 26's left over (if the number was so big to give any amount of 26's in it, then number of 26's in the 26's left over (if the number was so big to give any amount of 26 x 26's in it, Enit ?
' 'OR
Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL
Let lclm = (lclm - (1)) \ 26 'This gives the number of 26's ( if any ), but just the excact part, in the next number down , - so applying the rest formula to this new number will again leave a difference "left over" rest.
'lclm = (lclm - (rest + 1)) \ 26 ' As the number is effectively truncated here, any number from 1 to (rest +1) will do in the formula
Loop While lclm > 0 'Only loop further if number was big enough to still have 0-25's in it
End Function
Reply With Quote
Originally Posted by fixer
Bookmarks