My code retrieves information from multiple workbooks, posts the values into a sheet in the current workbook, and them sums the column values and posts the sums into a row on sheet 2.
The problem is I only have it set up at the moment to do this for 2 rows of data, where as I need to do this for another 83 rows (at least), and it already takes a while to process.
Any help would be greatly appreciated
The problem is I only have it set up at the moment to do this for 2 rows of data, where as I need to do this for another 83 rows (at least), and it already takes a while to process.
Any help would be greatly appreciated
Code:
Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String
' Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function
Sub TestGetValue()
NumLoops = (Range("D2") - Range("D1")) / 7 + 1
''Sets the value for NumLoops based on 2 date values in sheet1
WeekSNum = (Range("D1") - Range("G1")) / 7 + 1
''Sets the value for WeekSNum based on 2 values in sheet1
X = 0
Y = 0
''Sets the start value for X and Y
Do Until X = NumLoops
p = "C:\\myfilelocation"
f = "Week_" & (WeekSNum + X) & " we_" & Format(Range("D1") + Y, "ddmmyy") & "_ISSUED.xls"
s = "summary"
FHat = "C6"
FWey = "E6"
FDar = "G6"
FCov = "I6"
FMan = "K6"
FSou = "M6"
FLee = "O6"
FWhi = "Q6"
FBri = "S6"
FWim = "U6"
FTot = "W6"
''Sets the path, filename, sheet
''Sets the cell references that apply to Row1
Hat = GetValue(p, f, s, FHat)
Wey = GetValue(p, f, s, FWey)
Dar = GetValue(p, f, s, FDar)
Cov = GetValue(p, f, s, FCov)
Man = GetValue(p, f, s, FMan)
Sou = GetValue(p, f, s, FSou)
Lee = GetValue(p, f, s, FLee)
Whi = GetValue(p, f, s, FWhi)
Bri = GetValue(p, f, s, FBri)
Wim = GetValue(p, f, s, FWim)
Tot = GetValue(p, f, s, FTot)
''Uses the function GetValue to return the values from the specified cells, and names them
Sheets("Sheet3").Range("A1").Offset(X, 0) = Hat
Sheets("Sheet3").Range("A1").Offset(X, 1) = Wey
Sheets("Sheet3").Range("A1").Offset(X, 2) = Dar
Sheets("Sheet3").Range("A1").Offset(X, 3) = Cov
Sheets("Sheet3").Range("A1").Offset(X, 4) = Man
Sheets("Sheet3").Range("A1").Offset(X, 5) = Sou
Sheets("Sheet3").Range("A1").Offset(X, 6) = Lee
Sheets("Sheet3").Range("A1").Offset(X, 7) = Whi
Sheets("Sheet3").Range("A1").Offset(X, 8) = Bri
Sheets("Sheet3").Range("A1").Offset(X, 9) = Wim
Sheets("Sheet3").Range("A1").Offset(X, 10) = Tot
''Specifies where on sheet3 to post the cell values
FHat1 = "C7"
FWey1 = "E7"
FDar1 = "G7"
FCov1 = "I7"
FMan1 = "K7"
FSou1 = "M7"
FLee1 = "O7"
FWhi1 = "Q7"
FBri1 = "S7"
FWim1 = "U7"
FTot1 = "W7"
''Lists the cell references that apply to row 2
Hat1 = GetValue(p, f, s, FHat1)
Wey1 = GetValue(p, f, s, FWey1)
Dar1 = GetValue(p, f, s, FDar1)
Cov1 = GetValue(p, f, s, FCov1)
Man1 = GetValue(p, f, s, FMan1)
Sou1 = GetValue(p, f, s, FSou1)
Lee1 = GetValue(p, f, s, FLee1)
Whi1 = GetValue(p, f, s, FWhi1)
Bri1 = GetValue(p, f, s, FBri1)
Wim1 = GetValue(p, f, s, FWim1)
Tot1 = GetValue(p, f, s, FTot1)
''Uses the function GetValue to return the values from the specified cells, and names them
Sheets("Sheet3").Range("A1").Offset(X, 11) = Hat1
Sheets("Sheet3").Range("A1").Offset(X, 12) = Wey1
Sheets("Sheet3").Range("A1").Offset(X, 13) = Dar1
Sheets("Sheet3").Range("A1").Offset(X, 14) = Cov1
Sheets("Sheet3").Range("A1").Offset(X, 15) = Man1
Sheets("Sheet3").Range("A1").Offset(X, 16) = Sou1
Sheets("Sheet3").Range("A1").Offset(X, 17) = Lee1
Sheets("Sheet3").Range("A1").Offset(X, 18) = Whi1
Sheets("Sheet3").Range("A1").Offset(X, 19) = Bri1
Sheets("Sheet3").Range("A1").Offset(X, 20) = Wim1
Sheets("Sheet3").Range("A1").Offset(X, 21) = Tot1
''Specifies where on sheet3 to post the cell values
X = X + 1
Y = Y + 7
''increases the value for X and Y before the next loop
Loop
''Loops
Sheets("Sheet2").Range("B5") = WorksheetFunction.Sum(Sheets("Sheet3").Range("A1:A" & X))
Sheets("Sheet2").Range("D5") = WorksheetFunction.Sum(Sheets("Sheet3").Range("B1:B" & X))
Sheets("Sheet2").Range("F5") = WorksheetFunction.Sum(Sheets("Sheet3").Range("C1:C" & X))
Sheets("Sheet2").Range("H5") = WorksheetFunction.Sum(Sheets("Sheet3").Range("D1:D" & X))
Sheets("Sheet2").Range("J5") = WorksheetFunction.Sum(Sheets("Sheet3").Range("E1:E" & X))
Sheets("Sheet2").Range("L5") = WorksheetFunction.Sum(Sheets("Sheet3").Range("F1:F" & X))
Sheets("Sheet2").Range("N5") = WorksheetFunction.Sum(Sheets("Sheet3").Range("G1:G" & X))
Sheets("Sheet2").Range("P5") = WorksheetFunction.Sum(Sheets("Sheet3").Range("H1:H" & X))
Sheets("Sheet2").Range("R5") = WorksheetFunction.Sum(Sheets("Sheet3").Range("I1:I" & X))
Sheets("Sheet2").Range("T5") = WorksheetFunction.Sum(Sheets("Sheet3").Range("J1:J" & X))
Sheets("Sheet2").Range("V5") = WorksheetFunction.Sum(Sheets("Sheet3").Range("K1:K" & X))
Sheets("Sheet2").Range("B6") = WorksheetFunction.Sum(Sheets("Sheet3").Range("L1:L" & X))
Sheets("Sheet2").Range("D6") = WorksheetFunction.Sum(Sheets("Sheet3").Range("M1:M" & X))
Sheets("Sheet2").Range("F6") = WorksheetFunction.Sum(Sheets("Sheet3").Range("N1:N" & X))
Sheets("Sheet2").Range("H6") = WorksheetFunction.Sum(Sheets("Sheet3").Range("O1:O" & X))
Sheets("Sheet2").Range("J6") = WorksheetFunction.Sum(Sheets("Sheet3").Range("P1:P" & X))
Sheets("Sheet2").Range("L6") = WorksheetFunction.Sum(Sheets("Sheet3").Range("Q1:Q" & X))
Sheets("Sheet2").Range("N6") = WorksheetFunction.Sum(Sheets("Sheet3").Range("R1:R" & X))
Sheets("Sheet2").Range("P6") = WorksheetFunction.Sum(Sheets("Sheet3").Range("S1:S" & X))
Sheets("Sheet2").Range("R6") = WorksheetFunction.Sum(Sheets("Sheet3").Range("T2:T" & X))
Sheets("Sheet2").Range("T6") = WorksheetFunction.Sum(Sheets("Sheet3").Range("U1:U" & X))
Sheets("Sheet2").Range("V6") = WorksheetFunction.Sum(Sheets("Sheet3").Range("V1:V" & X))
''Specifies where the sum of row1 and 2 of the relevant columns in sheet3, should be posted to on sheet2
End Sub
