Union of Ranges Failed
 

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?

Union of Ranges Failed
 

You can't union ranges on separate sheets.

--
Regards,
Tom Ogilvy

"Barry Wright" wrote in message
...
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?