Excel Macro for Summations
-
- StarLounger
- Posts: 94
- Joined: 16 Oct 2021, 16:22
Excel Macro for Summations
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
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
-
- Administrator
- Posts: 78485
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel Macro for Summations
Could you provide an example of what the end result should look like?
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 94
- Joined: 16 Oct 2021, 16:22
Re: Excel Macro for Summations
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.
-
- Administrator
- Posts: 78485
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel Macro for Summations
Which column from the Sample Data workbook should be used? That for the current month, or for next month, or ...?
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 94
- Joined: 16 Oct 2021, 16:22
Re: Excel Macro for Summations
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.
-
- Administrator
- Posts: 78485
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel Macro for Summations
The email notification service is not 100% dependable, so you should check the forum from time to time.
Here is a macro:
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
Hans
-
- StarLounger
- Posts: 94
- Joined: 16 Oct 2021, 16:22
Re: Excel Macro for Summations
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?
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?
-
- Administrator
- Posts: 78485
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel Macro for Summations
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.
ThisWorkbook.Path & Application.PathSeparator & "Sample Data.xlsb"
to the complete path and filename of the data workbook.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 94
- Joined: 16 Oct 2021, 16:22
Re: Excel Macro for Summations
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?
-
- Administrator
- Posts: 78485
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel Macro for Summations
Should each sheet become a new row on the resulting sheet, or should everything be totaled in a single row?
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 94
- Joined: 16 Oct 2021, 16:22
Re: Excel Macro for Summations
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.
-
- StarLounger
- Posts: 94
- Joined: 16 Oct 2021, 16:22
Re: Excel Macro for Summations
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.
-
- Administrator
- Posts: 78485
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel Macro for Summations
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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 94
- Joined: 16 Oct 2021, 16:22
Re: Excel Macro for Summations
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?
-
- Administrator
- Posts: 78485
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel Macro for Summations
Yes. change the line
to
Code: Select all
Set wbk = ThisWorkbook
Code: Select all
Set wbk = ActiveWorkbook
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 94
- Joined: 16 Oct 2021, 16:22
Re: Excel Macro for Summations
Do you teach classes or anything? You seem to know more about excel than anyone I've met
-
- Administrator
- Posts: 78485
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel Macro for Summations
Best wishes,
Hans
Hans