0

I have the below code that allows me to open 12 Excel files of data, one for each month of the year. After running a macro on each file to clean up the data, the main code is supposed to take data from specific columns/rows, compile it together, and enter it into a new workbook.

I am able to loop through opening each file and running the custom macro on them, but unable to add them together.

Below is the entire code that I have. Under the "Add data to table" section, an attempt at the correct code is made, however it just appears to add the final months data for each cell into the new book, instead of adding 12 months of data from each cell. The next 4 sections are the old code I was using, with sections 2 and 4 divided by 12 to get an average (these are call times in minutes/seconds, where as the 1st, 3rd, and 5th sections are total numbers).

Sub ECHI12MonthCombiner()
'
' ECHI12MonthCombiner Macro
'
'
'Intialize workbooks
    Dim MonthSum(1 To 12) As Workbook
    Dim File As Variant
    ChDir "C:\Users\xxxxxxxxxxxxxxx\Desktop\Monthly Performance Summary"
    Dim Month As Integer
    For Month = 1 To 12
        File = Application.GetOpenFilename
        Set MonthSum(Month) = Workbooks.Open(File)
        ActiveSheet.name = MonthName(Month, True)
        Call ECHIBasicMonthlySummary
    Next

'Create new workbook
    Dim combinedMonthlySummaries As Workbook
    Set combinedMonthlySummaries = Workbooks.Add
    Sheets("Sheet1").name = "Call Data"

'Change analysts to variables
    Set Anal = Workbooks.Open("C:\Users\stefan.bagnato\Desktop\Analysts\Analysts")
    Dim var1, var2, var3, var4, var5, var6, var7 As String
    var1 = Workbooks("Analysts").Worksheets("Analysts").Range("A1")
    var2 = Workbooks("Analysts").Worksheets("Analysts").Range("A2")
    var3 = Workbooks("Analysts").Worksheets("Analysts").Range("A3")
    var4 = Workbooks("Analysts").Worksheets("Analysts").Range("A4")
    var5 = Workbooks("Analysts").Worksheets("Analysts").Range("A5")
    var6 = Workbooks("Analysts").Worksheets("Analysts").Range("A6")
    var7 = Workbooks("Analysts").Worksheets("Analysts").Range("A7")
    Workbooks("Analysts").Close

'Create table
    'Add table headers
        Range("A2") = var1
        Range("A3") = var2
        Range("A4") = var3
        Range("A5") = var4
        Range("A6") = var5
        Range("A7") = var6
        Range("A8") = var7
        Range("B1") = "Staff Time"
        Range("C1") = "Calls Offered"
        Range("D1") = "ACD Calls"
        Range("E1") = "AHT"
        Range("F1") = "Exn Out Calls"
        Range("G1") = "Avg Extn Out Time"
        Range("H1") = "Avail Time"
        Range("I1") = "AUX Time"
        Range("J1") = "Lunch Break"
        Range("K1") = "Short Break"
        Range("L1") = "Special Project"
        Range("M1") = "In A Meeting"
        Range("N1") = "Default"
        Range("O1") = "Tea Break"
    'Justify cells
        Range("B1:O8").HorizontalAlignment = xlCenter
    'Format columns
        Range("B2:B8,E2:E8,G2:O8").NumberFormat = "[h]:mm:ss"
        Range("A2:A8,B1:O1").Font.Bold = True
    'Widen columns
        Range("A:A").ColumnWidth = 16.5
        Range("B:O").ColumnWidth = 12
    'Wrap text
        Range("B1:O1").WrapText = True

'Add data to table
    Dim callRow As Long, callCol As Long, Sum As Double
    For callRow = 2 To 8
        For callCol = 2 To 4
            For Month = 1 To 12
                Sum = MonthSum(Month).Worksheets(MonthName(Month, True)).Cells(callRow, callCol).Value
            Next
            combinedMonthlySummaries.Worksheets("Call Data").Cells(callRow, callCol).Value = Sum
        Next
    Next
    For callRow = 2 To 8
        For callCol = 5 To 5
            combinedMonthlySummaries.Worksheets("Call Data").Cells(callRow, callCol).Value = _
                (monthSumJan.Worksheets("Agent Group Summary Monthly-jan").Cells(callRow, callCol).Value + _
                monthSumFeb.Worksheets("Agent Group Summary Monthly-feb").Cells(callRow, callCol).Value + _
                monthSumMar.Worksheets("Agent Group Summary Monthly-mar").Cells(callRow, callCol).Value + _
                monthSumApr.Worksheets("Agent Group Summary Monthly-apr").Cells(callRow, callCol).Value + _
                monthSumMay.Worksheets("Agent Group Summary Monthly-may").Cells(callRow, callCol).Value + _
                monthSumJun.Worksheets("Agent Group Summary Monthly-jun").Cells(callRow, callCol).Value + _
                monthSumJul.Worksheets("Agent Group Summary Monthly-jul").Cells(callRow, callCol).Value + _
                monthSumAug.Worksheets("Agent Group Summary Monthly-aug").Cells(callRow, callCol).Value + _
                monthSumSep.Worksheets("Agent Group Summary Monthly-sep").Cells(callRow, callCol).Value + _
                monthSumOct.Worksheets("Agent Group Summary Monthly-oct").Cells(callRow, callCol).Value + _
                monthSumNov.Worksheets("Agent Group Summary Monthly-nov").Cells(callRow, callCol).Value + _
                monthSumDec.Worksheets("Agent Group Summary Monthly-dec").Cells(callRow, callCol).Value) / 12
        Next
    Next
    For callRow = 2 To 8
        For callCol = 6 To 6
            combinedMonthlySummaries.Worksheets("Call Data").Cells(callRow, callCol).Value = _
                monthSumJan.Worksheets("Agent Group Summary Monthly-jan").Cells(callRow, callCol).Value + _
                monthSumFeb.Worksheets("Agent Group Summary Monthly-feb").Cells(callRow, callCol).Value + _
                monthSumMar.Worksheets("Agent Group Summary Monthly-mar").Cells(callRow, callCol).Value + _
                monthSumApr.Worksheets("Agent Group Summary Monthly-apr").Cells(callRow, callCol).Value + _
                monthSumMay.Worksheets("Agent Group Summary Monthly-may").Cells(callRow, callCol).Value + _
                monthSumJun.Worksheets("Agent Group Summary Monthly-jun").Cells(callRow, callCol).Value + _
                monthSumJul.Worksheets("Agent Group Summary Monthly-jul").Cells(callRow, callCol).Value + _
                monthSumAug.Worksheets("Agent Group Summary Monthly-aug").Cells(callRow, callCol).Value + _
                monthSumSep.Worksheets("Agent Group Summary Monthly-sep").Cells(callRow, callCol).Value + _
                monthSumOct.Worksheets("Agent Group Summary Monthly-oct").Cells(callRow, callCol).Value + _
                monthSumNov.Worksheets("Agent Group Summary Monthly-nov").Cells(callRow, callCol).Value + _
                monthSumDec.Worksheets("Agent Group Summary Monthly-dec").Cells(callRow, callCol).Value
        Next
    Next
    For callRow = 2 To 8
        For callCol = 7 To 7
            combinedMonthlySummaries.Worksheets("Call Data").Cells(callRow, callCol).Value = _
                (monthSumJan.Worksheets("Agent Group Summary Monthly-jan").Cells(callRow, callCol).Value + _
                monthSumFeb.Worksheets("Agent Group Summary Monthly-feb").Cells(callRow, callCol).Value + _
                monthSumMar.Worksheets("Agent Group Summary Monthly-mar").Cells(callRow, callCol).Value + _
                monthSumApr.Worksheets("Agent Group Summary Monthly-apr").Cells(callRow, callCol).Value + _
                monthSumMay.Worksheets("Agent Group Summary Monthly-may").Cells(callRow, callCol).Value + _
                monthSumJun.Worksheets("Agent Group Summary Monthly-jun").Cells(callRow, callCol).Value + _
                monthSumJul.Worksheets("Agent Group Summary Monthly-jul").Cells(callRow, callCol).Value + _
                monthSumAug.Worksheets("Agent Group Summary Monthly-aug").Cells(callRow, callCol).Value + _
                monthSumSep.Worksheets("Agent Group Summary Monthly-sep").Cells(callRow, callCol).Value + _
                monthSumOct.Worksheets("Agent Group Summary Monthly-oct").Cells(callRow, callCol).Value + _
                monthSumNov.Worksheets("Agent Group Summary Monthly-nov").Cells(callRow, callCol).Value + _
                monthSumDec.Worksheets("Agent Group Summary Monthly-dec").Cells(callRow, callCol).Value) / 12
        Next
    Next
    For callRow = 2 To 8
        For callCol = 8 To 15
            combinedMonthlySummaries.Worksheets("Call Data").Cells(callRow, callCol).Value = _
                monthSumJan.Worksheets("Agent Group Summary Monthly-jan").Cells(callRow, callCol).Value + _
                monthSumFeb.Worksheets("Agent Group Summary Monthly-feb").Cells(callRow, callCol).Value + _
                monthSumMar.Worksheets("Agent Group Summary Monthly-mar").Cells(callRow, callCol).Value + _
                monthSumApr.Worksheets("Agent Group Summary Monthly-apr").Cells(callRow, callCol).Value + _
                monthSumMay.Worksheets("Agent Group Summary Monthly-may").Cells(callRow, callCol).Value + _
                monthSumJun.Worksheets("Agent Group Summary Monthly-jun").Cells(callRow, callCol).Value + _
                monthSumJul.Worksheets("Agent Group Summary Monthly-jul").Cells(callRow, callCol).Value + _
                monthSumAug.Worksheets("Agent Group Summary Monthly-aug").Cells(callRow, callCol).Value + _
                monthSumSep.Worksheets("Agent Group Summary Monthly-sep").Cells(callRow, callCol).Value + _
                monthSumOct.Worksheets("Agent Group Summary Monthly-oct").Cells(callRow, callCol).Value + _
                monthSumNov.Worksheets("Agent Group Summary Monthly-nov").Cells(callRow, callCol).Value + _
                monthSumDec.Worksheets("Agent Group Summary Monthly-dec").Cells(callRow, callCol).Value
        Next
    Next

'Close monthly summary workbooks
    Application.DisplayAlerts = False
        For Month = 1 To 12
            MonthSum(Month).Close
        Next
    Application.DisplayAlerts = True

EDIT - in response to @Spinjector When adding Sum + to `Sum = MonthSum(Month).Worksheets(MonthName(Month, True)).Cells(callRow, callCol).Value', I get the below values, which are random and incorrect. enter image description here

Improve this question
3
  • Please post only the relevant part of your code - by that I mean what actually reproduces the issue, as you should as per Minimal, Complete and Verifiable Example As of now, your code is a chore to read through Commented Jul 10, 2018 at 13:34
  • Understood, although this reduces the likelihood of the inevitable "post your entire code so we know what you are trying to do" comments. Commented Jul 10, 2018 at 14:41
  • I think you're mis-interpreting that rule. As paraphrased in the MCVE (abbreviation) above: Your goal is to post the most minimalistic example that is capable of replicating the issue The post your entire code applies to, "post your entire code required for replicating the issue" - basically so we can grab your code and put it into our compiler and it makes sense, not dump your 40k lines of code here and expect us to sift through it, because it works together. The "entire code" line is commonly cited to users, who post 5 lines of code and expect us to fill out the blanks. Hence it's there! Commented Jul 10, 2018 at 14:47

1 Answer 1

Reset to default
2

It seems the addition and division for the sum is missing.

See below. Notice the bits I added: Sum = Sum + ... and = Sum / 12.

Dim callRow As Long
Dim callCol As Long
Dim Sum As Double
For callRow = 2 To 8
    For callCol = 2 To 4
        Sum = 0 'Reset the sum for each pass of the loop.
        For Month = 1 To 12
            Sum = Sum + MonthSum(Month).Worksheets(MonthName(Month, True)).Cells(callRow, callCol).Value
        Next
        combinedMonthlySummaries.Worksheets("Call Data").Cells(callRow, callCol).Value = Sum / 12
    Next
Next
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Hm. That does not appear to fix it. I believe the Sum + is what breaks it. I am adding an edit to the OP with a screenshot of what happens with the two edits you suggested. Col B should be in the format of 00:00:00 with each cell having somewhere in the neighborhood of 1500 hours or so. And Col C and D are integers which should also be around 1500-2000.
What about keeping the = Sum + but removing the / 12. I didn't comprehend what you described at first and thought the first section was to replace the others. If the first section is to calculate a sum, it has to accumulate the values somehow, but without the Sum + the value will be replaced in each pass of the loop, thus giving what you described where it only shows the final month of data.
Sorry for the confusion. We can ignore the / 12 for now. I can figure that part out (for the second section) later. Yes, tried it with ` = Sum + ` but that is what gives those really weird numbers that seem to be increasing by some unknown factor for each cell. I could go in an manually add up the hours (col B) or call numbers (col C and D) to see what the real answers should be, but I know the ranges should be approx what I listed above.
Ok what's the purpose of the For Month = loop..? There is only one line of code there Sum = MonthSum(Month).... In each pass of the loop, Sum is overwritten with the new valule. There are no other actions. Then the following line, combinedMonthlySummaries.Worksheets(... gets the value = Sum, which would only be what came from the last pass of the loop (Dec?). It seems there should be something more taking place there. As for the whacky values you see, could it be a formatting problem? Dates/Times look like huge decimal numbers. Have you checked the cell formats are Date?
Hm. I can't answer the first question, lol. Maybe that is where my issue is. The goal for this first section is to sum the values from cell B2 in each months file and insert them into B2 of the new file. It should do this for B2:D8. I understand what you are saying. Just trying to figure out how to correct the code. In regards to the formatting, everything is correct via the Create table section further up in the code. That part I know is not the issue. Thank you for helping me with this, btw.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.