Case Statement Question

[Liz Steffen]

Can someone provide me assistance with this case statement. I have attached
it to a command button and it does nothing. The object is to copy
information from another sheet in the workbook based on the value of a
certain cell.

Sub GetGrades_Sh1()

Dim sGrade1 As String

sGrade1 = "Shift1!P101"

Select Case sGrade1
Case Is = "WF"
Sheets("Data").Range("B6:B17").Select
Selection.Copy
Sheets("Shift1").Range("A7").Select
Selection.Paste
Application.CutCopyMode = False
Case Is = "PP"
Sheets("Data").Range("C6:C22").Select
Selection.Copy
Sheets("Shift1").Range("A7").Select
Selection.Paste
Application.CutCopyMode = False
Case Is = "SP"
Sheets("Data").Range("D616").Select
Selection.Copy
Sheets("Shift1").Range("A7").Select
Selection.Paste
Application.CutCopyMode = False
End Select
End Sub

 

[Jim Thomlinson]

You are not comparing to the cell, you are comparing to the string
"Shift1!P101" which is not really what you are looking for. Try this...

Sub GetGrades_Sh1()

Select Case Sheets("Shift1").Range("P101").Value
Case "WF"
Sheets("Data").Range("B6:B17").Copy _
Sheets("Shift1").Range("A7")
Application.CutCopyMode = False
Case "PP"
Sheets("Data").Range("C6:C22").Copy _
Sheets("Shift1").Range("A7")
Application.CutCopyMode = False
Case "SP"
Sheets("Data").Range("D616").Copy _
Sheets("Shift1").Range("A7")
Application.CutCopyMode = False
End Select
End Sub

 

[Liz Steffen]

Works like a charm.

Thank you so much!