Thank you all very much for responding!! I am honored by the expertise of all. Here is my full code..
<font face=Tahoma New><SPAN style="color:#00007F">Sub</SPAN> Find_Monthly_Values()
Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#007F00">'--------------------------------------------------------------------</SPAN>
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet, thisWb <SPAN style="color:#00007F">As</SPAN> Workbook, thisWs <SPAN style="color:#00007F">As</SPAN> Worksheet, waterWb <SPAN style="color:#00007F">As</SPAN> Workbook
<SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> findValue <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, lookRng <SPAN style="color:#00007F">As</SPAN> Range, foundRng <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Dim</SPAN> monthCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, monthVal <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, notFoundList <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> tmpSht <SPAN style="color:#00007F">As</SPAN> Worksheet, hasErr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>, lRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> fPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, fName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, tmpThisWb <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> Year4 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Year2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> varFileFind <SPAN style="color:#00007F">As</SPAN> Range, fileFoundRng <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#007F00">'--------------------------------------------------------------------</SPAN>
Year4 = ActiveSheet.Range("A1").Text
Year2 = Right(Year4, 2)
<SPAN style="color:#00007F">Set</SPAN> varFileFind = Sheets("Admin").Range("A:A")
<SPAN style="color:#00007F">Set</SPAN> fileFoundRng = varFileFind.Find("WTR" & Year2 & ".xls", _
after:=varFileFind.Cells(1), _
lookat:=xlWhole, _
MatchCase:=True)
<SPAN style="color:#00007F">If</SPAN> fileFoundRng <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
MsgBox "The file was not found on file!" & vbNewLine & vbNewLine & _
"Please contact your systems" & vbNewLine & "adminstrator.", _
vbExclamation, "ERROR!"
<SPAN style="color:#00007F">GoTo</SPAN> EndMeNow
End <SPAN style="color:#00007F">If</SPAN>
fPath = fileFoundRng.Offset(, 1).Value & fileFoundRng.Value
fName = fileFoundRng.Value
fileFoundRng.Offset(, 2).Value = Format(Date, "dd-mmm-yy")
tmpThisWb = ThisWorkbook.Name
Workbooks(fName).Activate
<SPAN style="color:#00007F">If</SPAN> Err = 0 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> hasItOpen
Err.Clear
Workbooks.Open (fPath)
hasItOpen:
Workbooks(tmpThisWb).Activate
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
<SPAN style="color:#007F00">'--------------------------------------------------------------------</SPAN>
<SPAN style="color:#00007F">Set</SPAN> thisWb = ThisWorkbook
<SPAN style="color:#00007F">Set</SPAN> thisWs = thisWb.Sheets(Year4)
<SPAN style="color:#00007F">Set</SPAN> waterWb = Workbooks(fileFoundRng.Value)
lastRow = thisWs.Range("A65536").<SPAN style="color:#00007F">End</SPAN>(xlUp).Row
hasErr = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">For</SPAN> i = 4 <SPAN style="color:#00007F">To</SPAN> lastRow <SPAN style="color:#00007F">Step</SPAN> 1
findValue = thisWs.Range("A" & i).Value
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> waterWb.Worksheets
<SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Left(LCase(ws.Name), 3)
Case "jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec"
<SPAN style="color:#00007F">Set</SPAN> lookRng = ws.Range("B62:AY62")
<SPAN style="color:#00007F">Set</SPAN> foundRng = lookRng.Find(thisWs.Cells(i, 1).Value, _
lookat:=xlWhole, _
Match<SPAN style="color:#00007F">Case</SPAN>:=True)
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> foundRng <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
monthCol = FindTheMonth(foundRng.Parent.Name)
<SPAN style="color:#007F00">'----------------------------------------------------------------</SPAN>
<SPAN style="color:#007F00">'monthVal = IsDuplicate(foundRng.Offset(34), i, thisWs)</SPAN>
monthVal = foundRng.Offset(34).Value
<SPAN style="color:#007F00">'----------------------------------------------------------------</SPAN>
thisWs.Cells(i, monthCol).Value = monthVal
<SPAN style="color:#00007F">Else</SPAN>
<SPAN style="color:#00007F">If</SPAN> hasErr = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#00007F">Set</SPAN> tmpSht = thisWb.Worksheets.Add
End <SPAN style="color:#00007F">If</SPAN>
hasErr = <SPAN style="color:#00007F">True</SPAN>
tmpSht.[A65536].<SPAN style="color:#00007F">End</SPAN>(xlUp).Offset(1).Value = _
thisWs.Cells(i, 1).Value
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Set</SPAN> foundRng = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
<SPAN style="color:#00007F">Next</SPAN> ws
<SPAN style="color:#00007F">Next</SPAN> i
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> tmpSht <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#00007F">With</SPAN> tmpSht
<SPAN style="color:#00007F">With</SPAN> .Range("A1")
.Value = "Not Found:"
.Font.Bold = <SPAN style="color:#00007F">True</SPAN>
.Font.Name = "Veranda"
.Font.Size = 14
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
.Range("A:A").AdvancedFilter xlFilterInPlace, , , <SPAN style="color:#00007F">True</SPAN>
.Cells.EntireColumn.AutoFit
.PrintOut copies:=1
.Delete
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
End <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">With</SPAN> thisWs
lRow = .Range("A65536").<SPAN style="color:#00007F">End</SPAN>(xlUp).Row
.Range("Q4", .Range("Q" & lRow)).FormulaR1C1 = _
"=SUM(RC[-13]:RC[-2])"
.Range("Q:Q").Font.Bold = <SPAN style="color:#00007F">True</SPAN>
.Cells.EntireColumn.AutoFit
.Range("A3", .Range("Q" & lRow)).AutoFilter field:=1
.Activate
End <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN>MeNow:
Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
MsgBox "Complete!"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">Function</SPAN> FindTheMonth(strMonth <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> currMonth <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
currMonth = Left(strMonth, 3)
<SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> currMonth
<SPAN style="color:#00007F">Case</SPAN> "Jan": FindTheMonth = 4
<SPAN style="color:#00007F">Case</SPAN> "Feb": FindTheMonth = 5
<SPAN style="color:#00007F">Case</SPAN> "Mar": FindTheMonth = 6
<SPAN style="color:#00007F">Case</SPAN> "Apr": FindTheMonth = 7
<SPAN style="color:#00007F">Case</SPAN> "May": FindTheMonth = 8
<SPAN style="color:#00007F">Case</SPAN> "Jun": FindTheMonth = 9
<SPAN style="color:#00007F">Case</SPAN> "Jul": FindTheMonth = 10
<SPAN style="color:#00007F">Case</SPAN> "Aug": FindTheMonth = 11
<SPAN style="color:#00007F">Case</SPAN> "Sep": FindTheMonth = 12
<SPAN style="color:#00007F">Case</SPAN> "Oct": FindTheMonth = 13
<SPAN style="color:#00007F">Case</SPAN> "Nov": FindTheMonth = 14
<SPAN style="color:#00007F">Case</SPAN> "Dec": FindTheMonth = 15
Case Else: FindTheMonth = 16 <SPAN style="color:#007F00">'extra column</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
End <SPAN style="color:#00007F">Function</SPAN></FONT>
It may not be the best, but it gets the job done. I can't seem t o get the HTML Maker to work, else I would post a sample of my data structure.
Basically, this is a new spreadsheet that I created, which will open and retreive specific data from another spreadsheet. These other spreadsheets are inherited and are not laid out as they should have been. And rather than re-create them (which I may have to do) I was trying to bring together all of the information to one location. Each file represents one full calendar year (Jan 1 - Dec 31) of water/wastewater meter readings. The figures being returned are the monthly totals.
In cell A1 of each sheet is the year of the water report I wish to query. From A4:A
lastRow is the data
headings I need returned. The headings found on the annual water sheets will always be a set amount of rows above the figures I need retreived. D3:O3 on my summary workbook ("WaterTotals.xls") are listed January - December. So the figures retreived will go into the matrix of D4:O
lastRow.
Let me know if further explanation is needed.
Any pointers here would be much appreciated. Thanks for you help guys!!
