pull data from other worksheets macro

[bshaggy6]

Hello,

I need help with a macro that does the following: I have 31 worksheets labeled from "1" to "31" with certain data points on it. I am trying to pull data from those 31 sheets into a worksheet in the same workbook called "Weekly Report Results". I setup cells on the weekly report result worksheet wher i can type in a number from 1-31 in cell g12 and type a number from 1-31 in cell h12. What im trying to tell excel is this: For example- If I put 1 in cell g12 and 5 in cell h12, i want it to sum all of the values in cell A3 from sheets 1 to 5 and place that summed value in cell A7 on the Weekly Report Results worksheet. Is it possible to create a macro that can do this? Thank you in advance for any help!

 

[dmt32]

Hi,
Assuming that by worksheets labelled from 1 to 31 you mean that this is their tab names then give following a try:

Place code in a standard module:

Function SUMSHEETS(ByVal StartSheet As Integer, ByVal EndSheet As Integer, CellAddress As String) As Double
    Dim i As Integer
    For i = StartSheet To EndSheet Step IIf(EndSheet < StartSheet, -1, 1)
        SUMSHEETS = SUMSHEETS + Sheets(CStr(i)).Range(CellAddress)
    Next i
End Function

Then in your Weekly Report Results worksheet add following Formula to cell A7

=SUMSHEETS(G12,H12,"A7")

The 1st Argument specifies the StartSheet by it's numeric name.
The 2nd argument specifies the EndSheet also by it's numeric name.
The 3rd argument is the Cell in each sheet you want to Sum.

Hope Helpful

Dave

 

[bshaggy6]

Hi Dave,

I appreciate you taking the time to help me. I tried your function as suggested and I'm getting a #NUM! error in the A7 field. Not sure if thats an error on my part or not. Thanks! And yes I have 31 tabs all labeled from "1" to "31"

 

[dmt32]

Hi Dave,

I appreciate you taking the time to help me. I tried your function as suggested and I'm getting a #NUM! error in the A7 field. Not sure if thats an error on my part or not. Thanks! And yes I have 31 tabs all labeled from "1" to "31"

Error suggest that you have invalid numeric values ranges

have look here how to resolve:
https://support.office.com/en-nz/article/Correct-a-NUM-error-edb4fc8b-9fae-4fe8-a668-f9bc19b7959a

 

[Copan1795]

I think the code is missing End Sub. just a guess.


Copan

 

[dmt32]

I think the code is missing End Sub. just a guess.


Copan

Code is a Function and includes End Function.

 

[bshaggy6]

Hi Dave,

I'm unsure as to why I'm getting the #num error when I use the function created. I have valid numbers in the cell A3 from sheets 1-31 and when I enter the formula as =SUMSHEETS(G12,H12,"A3") I get the #num error and when I tried =SUMSHEETS(G12,H12,"A7")I get the same error as well. I'm entering this formula in cell A7 on the Weekly Report Results Tab. If you could help advise me as to what I'm doing wrong I'd really apreciate it. Thanks.

 

[dmt32]

Hi,
without seeing your data can only make guesses but see if this modified version of function helps:

Function SUMSHEETS(ByVal StartSheet As Integer, ByVal EndSheet As Integer, CellAddress As String) As Double
    Dim i As Integer
    For i = StartSheet To EndSheet Step IIf(EndSheet < StartSheet, -1, 1)
        SUMSHEETS = SUMSHEETS + Val(ThisWorkbook.Sheets(CStr(i)).Range(CellAddress).Text)
    Next i
End Function

Dave

 

[bshaggy6]

Still getting same error. In cell g12 if it has just the number 1 and in cell h12 it has just the number 5 and then in cell a3 of the sheets from 1-5 are numberic values. I can't give specifics on the data unfortunately. I'm trying to enter the formula in cell A7 on a worksheet labeled "Weekly Report Results" which is where i'm inserting the module on VBA. Hope that helps with the data.

 

[dmt32]

The Function should be in a Standard module - NOT your sheets code page.

Dave