Solved: Code fails to run on Non-ActiveSheet

[YellowLabPro]

I have two sheets that I run the same code on to edit a column's values. I am using the Select Case method.
It properly performs on the activesheet, it however fails on the the second sheet to perform the code. There is no error message(just for info). If I manually activate the second sheet and run the code it works then.
I have a reference to the second Sheet. The second sheet's name is PCCombined_VB. I use the With method.

My guess is either I am not properly or fully qualifying the reference to the second sheet. Can someone point out my oversight here please?

[VBA]
Sub StandSizes()
Dim shCalcSetting As Long
Dim c As Range
Dim mTimer As Single
Dim LRowf As Long, LRowV As Long
Dim Wsv As Worksheet
Set Wsv = Worksheets("PCCombined_VB")
' shCalcSetting = Application.Calculation
' Application.ScreenUpdating = False
' Application.Calculation = xlManual

LRowf = Cells(Rows.Count, 1).End(xlUp).Row

For Each c In Range("M4:M" & LRowf)

Select Case c.Value

Case "Xs", "Xsml", "Xxs"
c.Value = "X-Small"

Case "S", "Sm", "Small", "Sml"
c.Value = "Small"

Case "M", "Md", "Med", "Medium"
c.Value = "Medium"

Case "L", "Large", "Lg", "Lrg"
c.Value = "Large"

Case "Xlarge", "Xlg", "Xlrg", "Xl"
c.Value = "X-Large"

Case "Xx", "2x", "Xxl"
c.Value = "XX-Large"

Case "S/M"
c.Value = "Sm/Md"

Case "M/L"
c.Value = "Md/Lg"

Case "L/Xl"
c.Value = "Lg/Xl"

End Select
Next c
'----------------------------------------------------VB-------------------------------------------------

With Wsv
LRowV = Cells(Rows.Count, 1).End(xlUp).Row

For Each c In Range("M4:M" & LRowV)

Select Case c.Value

Case "Xs", "Xsml", "Xxs"
c.Value = "X-Small"

Case "S", "Sm", "Small", "Sml"
c.Value = "Small"

Case "M", "Md", "Med", "Medium"
c.Value = "Medium"

Case "L", "Large", "Lg", "Lrg"
c.Value = "Large"

Case "Xlarge", "Xlg", "Xlrg", "Xl"
c.Value = "X-Large"

Case "Xx", "2x", "Xxl"
c.Value = "XX-Large"

Case "S/M"
c.Value = "Sm/Md"

Case "M/L"
c.Value = "Md/Lg"

Case "L/Xl"
c.Value = "Lg/Xl"

End Select
Next c
End With

' ActiveSheet.Calculate
' Application.Calculation = shCalcSetting
' Application.ScreenUpdating = True



End Sub
[/VBA]

[daniel_d_n_r]

[vba]Sub File_Sheet_Script()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
s = ws.Name
UserForm1.ListBox1.AddItem (s)
Next
UserForm1.Show
End Sub[/vba]

[vba]Sub sheet__Script()

Sheets("dat_2").Select

End Sub[/vba]

This is just a bit of code I have on my computer.2 methods of sheet selection.
Maybe it will give a few ideas, sorry ,not an exact answer but may help.

for starters you have not referenced the first sheet, the code seems to simply be running on the first active sheet by default,then as you are trying to call the second sheet it fails because the 'with statment' will not work in this way.
Usually it works with a selection.
i.e
With Selection
blah
blah
End With

I have not used the with statment like this, so I cannot say the with statment will work even if you select the second sheet.

However the above code will select the second sheet, while the array code will loop through every sheet .

if you only need this code to run on these 2 sheets then simply use the second method of sheet selection.
if you need the code to run on many sheets use the first code to loop through all the sheets in the workbook.
if you need the code to select a sheet from many by name you may have to run some sort of loop to iterate through the sheets and select a sheet by name.

cheers

[Bob Phillips]

Doug,

You need to qualify all range objects (Cells, Rows as well as Range), with the sheet object

[vba]

LRowf = Cells(Rows.Count, 1).End(xlUp).Row
[/vba]

becomes

[vba]

LRowf = Wsv.Cells(Wsv.Rows.Count, 1).End(xlUp).Row
[/vba]

etc.

[YellowLabPro]

Post back results:
Thanks Bob- I did not see it, makes perfect logic. Works so nice now
[VBA]
With Wsv
LRowV = Wsv.Cells(Wsv.Rows.Count, 1).End(xlUp).Row

For Each c In Wsv.Range("M4:M" & LRowV)
[/VBA]

Daniel,
Thanks for the suggestion, it will come in handy for later use. There is no need to reference the 1st sheet in this case, as it will always be active. The second sheet however can be referenced to run the code w/ the With End-With construct. As Xld points out, I failed to reference the object ranges- the lastrow and the loop range.

Cheers Gents,

Doug

[p45cal]

Note that if you're using the With..End With facility then you don't need to keep on using 'Wsv' within it, so this:

Post back results:
[vba]
With Wsv
LRowV = Wsv.Cells(Wsv.Rows.Count, 1).End(xlUp).Row
For Each c In Wsv.Range("M4:M" & LRowV)
[/vba]

becomes
[vba]
With Wsv
LRowV = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each c In .Range("M4:M" & LRowV)
[/vba]

[YellowLabPro]

Hi P45cal,

Thanks for the tip-

[mperrah]

I saw some use of case select here and thought to try.
I trying to modify this code to delete all sheets not in sarray
[VBA]
sarray = Array("QCDetail", "WQC", "Chart", "WPR", "MenuSheet")
For Each sht In ActiveWorkbook.Worksheets
Select Case sht.Name
Case ("QCDetail")
Case ("WQC")
Case ("Chart")
Case ("WPR")
Case ("MenuSheet")
Case Else
sht.Delete ' not sure how to verb this line correctly
End Select
Next sht[/VBA]

[Bob Phillips]

[vba]

For Each sht In ActiveWorkbook.Worksheets
Select Case sht.Name
Case "QCDetail", "WQC", "Chart", "WPR", "MenuSheet"
Case Else
sht.Delete
End Select
Next sht
[/vba]

[mdmackillop]

Note that XLD's code is case sensitive. You might want to build in tolerance for this.

[Bob Phillips]

He should know his own sheet names!

[YellowLabPro]

One more layer of complexity to mperrah's thread, it that it has drifted into mine, which is totally unrelated and is solved.... lol!

[mperrah]

Sorry to butt in,
thanks xld,ylp, md and daniel

My post didn't fit in with were this thread was leading, but I searched on case methods and this came up. I did find what I needed.
I had problems at first because I couldn't delete a hidden sheet I had forgot about. Daniel pointed that out. Anyway, thank you all, sorry for the intrusion.
Mark

[YellowLabPro]

Mark,
No worries, it just gets weird to track. What I do in a case like this is to copy the link of a related post into my current one.

Cheers