How to use VBA userform to sum accross worksheets.

[OtterBA]

I have workbook that has a tab for each week in a month. I have used a VBA userform with a multiselect listbox to allow the user to select multiple worksheets to roll-up into a summary worksheet. My problem is writting the sum formula to accept the listbox's array.
Here is my code. I have used the loop portion in other projects to copy and move worksheets to new workbooks and it works fine.

Private Sub CommandButton1_Click()
Worksheets(Worksheets.Count).Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = TextBox1.Text
Range("B2:H26,K2:O26,B32:k55").Select
Selection.ClearContents
Range("A1").Select
Selection.FormulaR1C1 = TextBox1.Text
Dim iloop As Integer
Dim arr()
Dim cntr As Variant
cntr = 0
For iloop = 1 To ListBox1.ListCount
If ListBox1.Selected(iloop - 1) = True Then
ListBox1.AddItem ListBox1.List(iloop - 1)
cntr = cntr + 1
ReDim Preserve arr(cntr)
arr(cntr) = ListBox1.List(iloop - 1)
End If
Next
Range("B2:H26,K2:O26,B32:k55").Select
Selection.FormulaR1C1 = "=sum(worksheets(arr(cntr))!RC)" 'this is where I am having my summing multiple sheets issues.
Unload UserForm1
End Sub

 

[VoG]

That won't work. The correct (worksheet) syntax for that type of formula is

=SUM(Sheet1:Sheet12!A1)

Please use code tags when posting code

[code]
your code here
[/code]

 

[OtterBA]

Thanks but I know that formula. I need something with more flexabiity for variable worksheets that can be selected in the multiselect listbox.

 

[VoG]

Then you will need to loop.

 

[OtterBA]

I was afraid of that. I've used sum(start:end!a1) alot. So how would I loop to sum the worksheets selected from the listbox?

 

[OtterBA]

I just thought of a work around. I could create and remove the start and end worksheets every time the form is used to do the calculations the do a copy paste values but it would be nice to have a more effiecient macro than creating and deleting worksheets everytime.

 

[VoG]

Try like this

For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
    mytotal = mytotal + Sheets(ListBox1.List(i)).Range("A1").Value
End If
Next i

 

[OtterBA]

I am having problems with the line

mytotal = mytotal + Sheets(ListBox1.List(i)).Range("A1").Value

It isn't doing anything.

Dim iloop As Integer
Dim arr()
Dim cntr As Variant
cntr = 0
For iloop = 1 To ListBox1.ListCount
If ListBox1.Selected(iloop - 1) = True Then
ListBox1.AddItem ListBox1.List(iloop - 1)
cntr = cntr + 1
ReDim Preserve arr(cntr)
arr(cntr) = ListBox1.List(iloop - 1)
End If

Next
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
mytotal = mytotal + Sheets(ListBox1.List(i)).Range("B4").Value
End If
Next i

I am a novice with VBA and have moments of wisdom but those are limited.