Can anybody help explain why the following code does not work?

The value in range D2 is =TEXT($C$2,"DDDDDD"), c2 is todays date

Dim rng As Range

Set rng = ActiveSheet.Range("D2")

With rng
If .Value = "MONDAY" Then
'Do something,
'
Sheets("MON").Select
ActiveSheet.Rows("7:126").Select
Selection.Copy
Sheets("24HR").Select
ActiveSheet.Rows("7:126").Select
ActiveSheet.Paste
Else
If .Value = "TUESDAY" Then
'Do something,
'
Sheets("TUE").Select
ActiveSheet.Rows("7:126").Select
Selection.Copy
Sheets("24HR").Select
ActiveSheet.Rows("7:126").Select
ActiveSheet.Paste
Else
If .Value = "WEDNESDAY" Then
'Do something,
'
Sheets("WED").Select
ActiveSheet.Rows("7:126").Select
Selection.Copy
Sheets("24HR").Select
ActiveSheet.Rows("7:126").Select
ActiveSheet.Paste
Else

End If
End If
End If

End With

Dim rng As Range

Set rng = ActiveSheet.Range("D2")

With rng
Select Case UCase(.Value)

Case "MONDAY"
'Do something,
'
Sheets("24HR").Rows("7:126").Copy Sheets("MON").Range("A7")
Case "TUESDAY"
'Do something,
'
Sheets("24HR").Rows("7:126").Copy Sheets("TUE").Range("A7")
Case "WEDNESDAY"
'Do something,
'
Sheets("24HR").Rows("7:126").Copy Sheets("WED").Range("A7")
End Select
End With


or even

Dim rng As Range

Set rng = ActiveSheet.Range("D2")

With rng
'Do something,
'
Sheets("24HR").Rows("7:126").Copy Sheets(UCase(.Value)).Range("A7")
End With


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"thomsonpa" wrote in message
...
Can anybody help explain why the following code does not work?

The value in range D2 is =TEXT($C$2,"DDDDDD"), c2 is todays date

Dim rng As Range

Set rng = ActiveSheet.Range("D2")

With rng
If .Value = "MONDAY" Then
'Do something,
'
Sheets("MON").Select
ActiveSheet.Rows("7:126").Select
Selection.Copy
Sheets("24HR").Select
ActiveSheet.Rows("7:126").Select
ActiveSheet.Paste
Else
If .Value = "TUESDAY" Then
'Do something,
'
Sheets("TUE").Select
ActiveSheet.Rows("7:126").Select
Selection.Copy
Sheets("24HR").Select
ActiveSheet.Rows("7:126").Select
ActiveSheet.Paste
Else
If .Value = "WEDNESDAY" Then
'Do something,
'
Sheets("WED").Select
ActiveSheet.Rows("7:126").Select
Selection.Copy
Sheets("24HR").Select
ActiveSheet.Rows("7:126").Select
ActiveSheet.Paste
Else

End If
End If
End If

End With

Great, many thanks works perfectly..



"Bob Phillips" wrote:

Dim rng As Range

Set rng = ActiveSheet.Range("D2")

With rng
Select Case UCase(.Value)

Case "MONDAY"
'Do something,
'
Sheets("24HR").Rows("7:126").Copy Sheets("MON").Range("A7")
Case "TUESDAY"
'Do something,
'
Sheets("24HR").Rows("7:126").Copy Sheets("TUE").Range("A7")
Case "WEDNESDAY"
'Do something,
'
Sheets("24HR").Rows("7:126").Copy Sheets("WED").Range("A7")
End Select
End With


or even

Dim rng As Range

Set rng = ActiveSheet.Range("D2")

With rng
'Do something,
'
Sheets("24HR").Rows("7:126").Copy Sheets(UCase(.Value)).Range("A7")
End With


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"thomsonpa" wrote in message
...
Can anybody help explain why the following code does not work?

The value in range D2 is =TEXT($C$2,"DDDDDD"), c2 is todays date

Dim rng As Range

Set rng = ActiveSheet.Range("D2")

With rng
If .Value = "MONDAY" Then
'Do something,
'
Sheets("MON").Select
ActiveSheet.Rows("7:126").Select
Selection.Copy
Sheets("24HR").Select
ActiveSheet.Rows("7:126").Select
ActiveSheet.Paste
Else
If .Value = "TUESDAY" Then
'Do something,
'
Sheets("TUE").Select
ActiveSheet.Rows("7:126").Select
Selection.Copy
Sheets("24HR").Select
ActiveSheet.Rows("7:126").Select
ActiveSheet.Paste
Else
If .Value = "WEDNESDAY" Then
'Do something,
'
Sheets("WED").Select
ActiveSheet.Rows("7:126").Select
Selection.Copy
Sheets("24HR").Select
ActiveSheet.Rows("7:126").Select
ActiveSheet.Paste
Else

End If
End If
End If

End With