looping around several pages

Hi everyone!!

basically all i need it to do now is do this for multiple sheets selected from a drop down, (listed on a separate page). each sheet is a date for e.g 07.02.13 and basically the below code works mite be a bit long winded but it works lol!!

now the additional thing that i need it to do is store each i instead of writing it direct to cell_count i need it to total each i and then write it, ive tried a few things but its been a while since i used vb for 1 plus im only a novice! lol any help would be much appreciated!! i hope someone can help

Cheers

Excel / VB community

Sub date_range1()

Application.ScreenUpdating = False

Sheets("overview").Select

Dim date_start As String
    date_start = Range("H4")
    
Dim date_end As String
    date_end = Range("H7")
    
Dim datesel1 As Range

Dim selection() As String

Dim blDimensioned As Boolean

Dim lngPosition As Long

Sheets("Sheet1").Select

blDimensioned = False

For Each datesel1 In Range("A:A")

If date_start = datesel1 Then
    flag = True
End If
    
If flag = True Then
        If datesel1 <> "" Then
           If blDimensioned = True Then
                ReDim Preserve selection(0 To UBound(selection) + 1) As String
            Else
                ReDim selection(0 To 0) As String
             blDimensioned = True
           End If
            selection(UBound(selection)) = datesel1
        End If
End If
    
If date_end = datesel1 Then
    flag = False
End If
    
Next datesel1
      
   'Write to cells
     
For Position = LBound(selection) To UBound(selection)
      
    Sheets(selection(postion)).Select
    
    Dim cell_count As Integer
    
    Dim cell_id0 As String
    Dim cell_id1 As String
    Dim cell_id2 As String
    Dim cell_id3 As String
    Dim cell_id4 As String
    
    cell_count = 11
    For Each i In Range("I5:I24")
    
        cell_id0 = "C" & cell_count
        
        Sheets("overview").Select
        Range(cell_id0) = i
        
        Sheets(selection(Position)).Select
        
        cell_count = cell_count + 1
        
    Next i
    
    cell_count = 11
    For Each i In Range("J5:J24")
    
        cell_id1 = "D" & cell_count
        
        Sheets("overview").Select
        Range(cell_id1) = i
    
        Sheets(selection(Position)).Select
        
        cell_count = cell_count + 1
        
    Next i
    
    cell_count = 11
    For Each i In Range("K5:K24")
    
        cell_id2 = "E" & cell_count
        
        Sheets("overview").Select
        Range(cell_id2) = i
    
        Sheets(selection(Position)).Select
        
        cell_count = cell_count + 1
        
    Next i
    
    cell_count = 11
    For Each i In Range("L5:L24")
    
        cell_id3 = "F" & cell_count
        
        Sheets("overview").Select
        Range(cell_id3) = i
    
        Sheets(selection(Position)).Select
        
        cell_count = cell_count + 1
        
    Next i
    
    cell_count = 11
    For Each i In Range("M5:M24")
  
        cell_id4 = "G" & cell_count
        
        Sheets("overview").Select
        Range(cell_id4) = i
    
        Sheets(selection(Position)).Select
        
        cell_count = cell_count + 1
        
    Next i
             
Next Position
    
Erase selection

Application.ScreenUpdating = True

Sheets("overview").Activate

End Sub

Sub date_range1()
    
    Dim rngDateStart As Range
    Dim rngDateEnd As Range
    Dim rngDates As Range
    Dim rngDateCell As Range
    Dim counter As Long
    
    Application.ScreenUpdating = False
    
    Sheets("overview").Select
    
    Set rngDateStart = Range("H4")
    Set rngDateEnd = Range("H7")
    
    'Find date range
    With Sheets("Sheet1")
        Set rngDateStart = .Range("A:A").Find(rngDateStart.Text, , xlValues, xlWhole)
        Set rngDateEnd = .Range("A:A").Find(rngDateEnd.Text, , xlValues, xlWhole)
        If rngDateStart Is Nothing Or rngDateEnd Is Nothing Then
            MsgBox "Cannot locate date(s) on Sheet1. " & vbLf & vbLf & _
                    "Date Start: " & Range("H4").Text & vbLf & _
                    "Date End: " & Range("H7").Text, vbExclamation, "Date Not Found"
            Exit Sub
        End If
    End With
    
    'Write totals for each date sheet
    For Each rngDateCell In Sheets("Sheet1").Range(rngDateStart, rngDateEnd)
        'Totals
        With Sheets(rngDateCell.Text)
            'Range("N5").Offset(counter).Value = rngDateCell.Text   'Date
            Range("I5").Offset(counter).Value = Application.WorksheetFunction.Sum(.Range("C11:C30"))
            Range("J5").Offset(counter).Value = Application.WorksheetFunction.Sum(.Range("D11:D30"))
            Range("K5").Offset(counter).Value = Application.WorksheetFunction.Sum(.Range("E11:E30"))
            Range("L5").Offset(counter).Value = Application.WorksheetFunction.Sum(.Range("F11:F30"))
            Range("M5").Offset(counter).Value = Application.WorksheetFunction.Sum(.Range("G11:G30"))
            counter = counter + 1
        End With
    Next rngDateCell
    
    Application.ScreenUpdating = True
    
End Sub

hmmm i see what your trying to do (i think) but it doesnt work.... what i need is the bit where it writes to columns c d e f g , i need it to retain the current values and then add the new value i tried this :-

For Each i In Range("I5:I24")
    
        cell_id0 = "C" & cell_count
        
        Sheets("overview").Select
        Range(cell_id0) = i + cell.value 
        
        Sheets(selection(Position)).Select
        
        cell_count = cell_count + 1
        
    Next i

but it didnt work.... just says cell value is empty when i break on next i each time it progress's it still says cell.value is empty any clues.... hopefully you understand what i mean

I think I had the data columns and the destination columns swapped. Try this...

Sub date_range1()
    
    Dim rngDateStart As Range
    Dim rngDateEnd As Range
    Dim rngDates As Range
    Dim rngDateCell As Range
    Dim NextRow As Long
    
    Application.ScreenUpdating = False
    
    Sheets("overview").Select
    
    Set rngDateStart = Range("H4")
    Set rngDateEnd = Range("H7")
    NextRow = Range("C" & Rows.Count).End(xlUp).Offset(1).Row
    If NextRow < 5 Then NextRow = 5
    
    'Find date range
    With Sheets("Sheet1")
        Set rngDateStart = .Range("A:A").Find(rngDateStart.Text, , xlValues, xlWhole)
        Set rngDateEnd = .Range("A:A").Find(rngDateEnd.Text, , xlValues, xlWhole)
        If rngDateStart Is Nothing Or rngDateEnd Is Nothing Then
            MsgBox "Cannot locate date(s) on Sheet1. " & vbLf & vbLf & _
                    "Date Start: " & Range("H4").Text & vbLf & _
                    "Date End: " & Range("H7").Text, vbExclamation, "Date Not Found"
            Exit Sub
        End If
    End With
    
    'Write totals for each date sheet
    For Each rngDateCell In Sheets("Sheet1").Range(rngDateStart, rngDateEnd)
        'Totals
        With Sheets(rngDateCell.Text)
            Range("B" & NextRow).Value = rngDateCell.Text   'Date
            Range("C" & NextRow).Value = Application.WorksheetFunction.Sum(.Range("I11:I30"))
            Range("D" & NextRow).Value = Application.WorksheetFunction.Sum(.Range("J11:J30"))
            Range("E" & NextRow).Value = Application.WorksheetFunction.Sum(.Range("K11:K30"))
            Range("F" & NextRow).Value = Application.WorksheetFunction.Sum(.Range("L11:L30"))
            Range("G" & NextRow).Value = Application.WorksheetFunction.Sum(.Range("M11:M30"))
            NextRow = NextRow + 1
        End With
    Next rngDateCell
    
    Application.ScreenUpdating = True
    
End Sub

this is the document im working on mite make more sense i get what your trying to do and its because im not explaining myself thanks for all your efforts so far