Not ignoring worksheets

**wwu-dillon** · 09-09-2008, 12:40 PM

Sub budgethistory()
Dim myKey, myMessage, myTitle
myMessage = "Which month are you entering? Enter month as a number."
myTitle = "History"
myKey = InputBox(myMessage, myTitle)

Sheets("PivotData").Activate
    Range("ammountthisperiod").Select
    Selection.Copy
    
    monthnum = myKey
    
    Range("hist").Offset(0, monthnum - 1).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    
Dim Arr As Variant
Dim i As Long
Dim Wsht As Worksheet
Arr = Array("E19", "E52", "E85", "E118", "E151", "E184", "E217", "E249")


For Each Wsht In Worksheets
    Select Case UCase(Wsht.Name)
    Case "pivotdata", "sumtable"
     '
     Case Else
        For i = 0 To UBound(Arr)
            Wsht.Range(Arr(i)).ClearContents
        Next i
    End Select
Next Wsht
    
End Sub

I have a problem with my code written above. this section:

    Select Case UCase(Wsht.Name)
    Case "pivotdata", "sumtable"
     '

is not working and the array i have named is being deleted on this worksheet as well. i want to be able to input the worksheets ex. pivotdata, sumtable and have them be pardoned from the deleting. any thoughts?

thanks

-dillon

**wwu-dillon** · 09-09-2008, 01:18 PM

Case "pivotdata", "sumtable"

i tried changing this line to

Case Wsht.pivotdata, Wsht.sumtable

didnt work. also tried

Case Sheets("pivotdata", "sumtable")

didnt work. any suggestions to look into or anything. also i think that maybe the problem has to do with the case else as it is unbound, or would the sheets be ignored since they fit into the case section. lemme know your thought or suggestions on fixing this problem.

ty in advance.

**royUK** · 09-10-2008, 04:32 AM

You are telling it to check for Upper Case, but using Lower Case. You don't need to loop through an array eitherr

For Each Wsht In Worksheets
    Select Case UCase(Wsht.Name)
    Case "PIVOTDATA", "SUMTABLE"
     '
     Case Else
            Wsht.Range("E19, E52, E85, E118, E151, E184, E217, E249").ClearContents
    End Select
Next Wsht

**wwu-dillon** · 09-12-2008, 02:11 PM

thank you very much for your help. i just changed them to uppercase because i dont understand the looping thing and its still working. thank you very much royuk