Union of Ranges Failed

[Barry Wright]

Public Sub Workbook_Open()
' This Sub checks each Sheet (1-4), calculates the data range for
each, and
' then adds then all to a total Data Range (data ranges declared
Global
' as WeekOne, WeekTwo, WeekThree, WeekFour and WeekAll)
' Function Call CHKROW Just checks if the cell range is Blank to let
me know
' I have reached the end of the data lines on that sheet.

Dim AddRange As Range
Dim ChkSheet As Worksheet
Dim DataLine As Long
For Each Sh In ThisWorkbook.Worksheets
If Sh.Name <> "Totals" Then
DataLine = 0
Worksheets(Sh.Name).Activate
Set AddRange = Sheets(Sh.Name).Range(Cells(1, 1), Cells(1,
10))
Do
DataLine = DataLine + 1
Set AddRange = Union(AddRange,
Sheets(Sh.Name).Range(Cells(DataLine, 1), Cells(DataLine, 10)))
If ChkRow(DataLine) = Blank Then Exit Do
Loop
Select Case ActiveSheet.Index
Case 1
Set WeekOne = AddRange
Case 2
Set WeekTwo = AddRange
Case 3
Set WeekThree = AddRange
Case 4
Set WeekFour = AddRange
End Select
Else
Set WeekAll = Union(WeekOne, WeekTwo, WeekThree, WeekFour)

End If
Next
End Sub

This all works as I wanted it to, until It gets to "Set WeekAll =
Union(WeekOne, WeekTwo, WeekThree, WeekFour)" It give me an error
setting the range of the global variable. (all the rest worked just
fine) Any one know why?

 

[Tom Ogilvy]

You can't union ranges on separate sheets.