Excel Macro for Summations

JDeMaro22
StarLounger
Posts: 94
Joined: 16 Oct 2021, 16:22

Excel Macro for Summations

Post by JDeMaro22 »

Hi,

I need to do summations for various rows in two different worksheets for various single doctor regions. I'm just looking for a faster way to complete this. Recording macros has never worked in my favor. But for each doctors file the worksheets are labeled the same names and the row numbers are always the same as well. If there was a way to take the below information and put it into a table for me to copy over to my workbook that would save me hours.

Sum of Row 97,98 + Row 102
Row 158 minus Row 227
Row 159 minus 230
Row 160 minus 231
Row 103
Row 243


https://fromsmash.com/Sample-Data-2

I've tried making my file as small as possible but i still couldnt attach it. You can find one of the worksheet examples above.

Thank you very much

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel Macro for Summations

Post by HansV »

Could you provide an example of what the end result should look like?
Best wishes,
Hans

JDeMaro22
StarLounger
Posts: 94
Joined: 16 Oct 2021, 16:22

Re: Excel Macro for Summations

Post by JDeMaro22 »

Table Example.xlsx
This is what the worksheet looks like I am inputting these values into. I am doing these calculations on two different sheets, creating a summation of those then entering those results into a table like the one I attached. I figured the easiest way would be if a macro could calculate those values into a table like that on each sheet then I would manually sum the values into my worksheet.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel Macro for Summations

Post by HansV »

Which column from the Sample Data workbook should be used? That for the current month, or for next month, or ...?
Best wishes,
Hans

JDeMaro22
StarLounger
Posts: 94
Joined: 16 Oct 2021, 16:22

Re: Excel Macro for Summations

Post by JDeMaro22 »

Sorry Hans I never got a notification that you responded. I was just checking the forum and saw that you had. I'm not sure i understand the question but for which column should be used it would be a sum of all three D,E,F.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel Macro for Summations

Post by HansV »

The email notification service is not 100% dependable, so you should check the forum from time to time.
Here is a macro:

Code: Select all

Sub ImportData()
    Dim wshT As Worksheet
    Dim wbkS As Workbook
    Dim wshS As Worksheet
    Set wshT = ActiveSheet
    ' ***** Change as needed! *****
    Set wbkS = Workbooks.Open(Filename:=ThisWorkbook.Path & Application.PathSeparator & "Sample Data.xlsb", UpdateLinks:=False)
    Set wshS = wbkS.Worksheets(1)
    wshT.Range("A2").Value = Application.Sum(wshS.Range("D9:F98,D102:F102"))
    wshT.Range("B2").Value = Application.Sum(wshS.Range("D158:F158")) - Application.Sum(wshS.Range("D227:F227"))
    wshT.Range("C2").Value = Application.Sum(wshS.Range("D159:F159")) - Application.Sum(wshS.Range("D230:F230"))
    wshT.Range("D2").Value = Application.Sum(wshS.Range("D160:F160")) - Application.Sum(wshS.Range("D231:F231"))
    wshT.Range("E2").Value = Application.Sum(wshS.Range("D103:F103"))
    wshT.Range("F2").Value = Application.Sum(wshS.Range("D243:F243"))
    wbkS.Close SaveChanges:=False
End Sub
Best wishes,
Hans

JDeMaro22
StarLounger
Posts: 94
Joined: 16 Oct 2021, 16:22

Re: Excel Macro for Summations

Post by JDeMaro22 »

Thanks a lot Hans,

I'm getting debugged on the first line " Set wbkS = Workbooks.Open(Filename:=ThisWorkbook.Path & Application.PathSeparator & "Sample Data.xlsb", UpdateLinks:=False)". I'm not great at reading vba but I tried changing the "Sample Data.xlsb" to the proper workbook name but it still came up as an error?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel Macro for Summations

Post by HansV »

I assumed that the workbook with the data would be in the same folder as the workbook with the code. If it is in another folder, you will have to change

ThisWorkbook.Path & Application.PathSeparator & "Sample Data.xlsb"

to the complete path and filename of the data workbook.
Best wishes,
Hans

JDeMaro22
StarLounger
Posts: 94
Joined: 16 Oct 2021, 16:22

Re: Excel Macro for Summations

Post by JDeMaro22 »

Perhaps I am not understanding how to run this particular macro. I assumed I would just run this one each sheet individually. In the real workbook there are maybe 20 sheets and I would need to run this one two of them to make those calculations. How does this one work exactly?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel Macro for Summations

Post by HansV »

Should each sheet become a new row on the resulting sheet, or should everything be totaled in a single row?
Best wishes,
Hans

JDeMaro22
StarLounger
Posts: 94
Joined: 16 Oct 2021, 16:22

Re: Excel Macro for Summations

Post by JDeMaro22 »

So I think the easiest way to do this would be to create a new sheet and put the results on there. As I mentioned I will need to run this macro on two different worksheets. If this macro could run these calculations and put the results on a new worksheet then I could combine the results manually. Does that make sense? In my head its much simpler but hard to describe.

JDeMaro22
StarLounger
Posts: 94
Joined: 16 Oct 2021, 16:22

Re: Excel Macro for Summations

Post by JDeMaro22 »

For an example I would run the macro on one worksheet. It would create a new worksheet, run those calculations and put the results in A1,B1,C1,D1,E1,F1. Then I would run the macro again on the second worksheet and put the results in A2,B2,C2,D2,E2,F2.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel Macro for Summations

Post by HansV »

Here is a new macro, to be run form the data workbook. It has been included in the attached version.

Code: Select all

Sub SummarizeData()
    Dim wbk As Workbook
    Dim wshT As Worksheet
    Dim wshS As Worksheet
    Dim i As Long
    Dim n As Long
    Set wbk = ThisWorkbook
    n = wbk.Worksheets.Count
    Set wshT = wbk.Worksheets.Add(Before:=wbk.Worksheets(1))
    wshT.Range("A1:F1").Value = Array("PET / CT / RAD", "RX-to-Go", "Central Lab", "Pathology", "RIT", "PDP")
    For i = 2 To n + 1
        Set wshS = wbk.Worksheets(i)
        wshT.Range("A" & i).Value = Application.Sum(wshS.Range("D9:F98,D102:F102"))
        wshT.Range("B" & i).Value = Application.Sum(wshS.Range("D158:F158")) - Application.Sum(wshS.Range("D227:F227"))
        wshT.Range("C" & i).Value = Application.Sum(wshS.Range("D159:F159")) - Application.Sum(wshS.Range("D230:F230"))
        wshT.Range("D" & i).Value = Application.Sum(wshS.Range("D160:F160")) - Application.Sum(wshS.Range("D231:F231"))
        wshT.Range("E" & i).Value = Application.Sum(wshS.Range("D103:F103"))
        wshT.Range("F" & i).Value = Application.Sum(wshS.Range("D243:F243"))
    Next i
    wshT.Range("A1:F1").EntireColumn.AutoFit
End Sub
Sample Data.xlsb
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

JDeMaro22
StarLounger
Posts: 94
Joined: 16 Oct 2021, 16:22

Re: Excel Macro for Summations

Post by JDeMaro22 »

Thanks a lot Hans. I noticed that this macro only works when I save it in the sheet itself, is there a way I can add this to my personal workbook so I don't have to re-paste the code each month for every workbook I run this on?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel Macro for Summations

Post by HansV »

Yes. change the line

Code: Select all

    Set wbk = ThisWorkbook
to

Code: Select all

    Set wbk = ActiveWorkbook
Best wishes,
Hans

JDeMaro22
StarLounger
Posts: 94
Joined: 16 Oct 2021, 16:22

Re: Excel Macro for Summations

Post by JDeMaro22 »

Do you teach classes or anything? You seem to know more about excel than anyone I've met

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel Macro for Summations

Post by HansV »

No, I don't. I suggest taking a look at these sites:
Excel-Easy
Exceljet
Contextures
Best wishes,
Hans