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!

 

[bshaggy6]

Still not working. Would it have to do with the fact that there is a macro on this workbook? Or should that not have any effect?

 

[dmt32]

Still not working. Would it have to do with the fact that there is a macro on this workbook? Or should that not have any effect?

Depends hat your macro does -
Can you create an anonymous copy of your workbook with sample data & place in a dropbox?

There is a small error in the function:

in this line:

SUMSHEETS = SUMSHEETS + Val(ThisWorkbook.Sheets(CStr(i)).Range(CellAddress).Value)

Change Text to Value where shown in RED.

Dave

 

[bshaggy6]

It didn't change anything. My company wont allow me to make a drop box. Would making a macro work easier than creating a function?

 

[bshaggy6]

I may have found the issue. In cell g12 and h12 it has the numbers of the sheets i want it sum to and from as i mentioned before lets say g12 has the value 1 and h12 has the value 5 i want it sum worksheets 1 through 5 the value in cell a3. I'm using the microsoft date and time control to put those numbers into g12 and h12 then just re-formatted it to the value of 1 and 5 even thoughh the true value is lets say 6/1/2015 and 6/5/2015. Is there anyway to fix it now?

 

[dmt32]

I'm using the microsoft date and time control to put those numbers into g12 and h12 then just re-formatted it to the value of 1 and 5

Detail like this is very important to know when asking for help - should have mentioned this from the outset would have saved a lot of time.

Try this update:

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

I have assumed from your sample that you are formatting dates to display MONTH?

Dave

 

[bshaggy6]

Hi Dave,

It works perfectly now. My apologies for making it take a while to work. thanks again for all of your help!