working with the correct worksheet

[goss9394]

Hi all -

The code sample below now works, but from everything I've read I should
not use

Worksheets("Data").Activate

I've tried using
===========================================
With wbBook
Set wsData = .Worksheets("Data")
Set wsFormulas = .Worksheets("Formulas")
Set wsMealPlan = .Worksheets("MealPlan")
Set wsComp = .Worksheets("COMP")
End With

With wsData
Set Rng = .Range("A1:L" & Get_Rows) 'Get_Rows is UDF
End With
=============================================
But sometimes if a different sheet is active
The code does not execute on the "Data" sheet.

What am I doing wrong?
Thanks
-goss

 

[Tom Ogilvy]

there is nothing in your code that does anything, so it is hard to say where
you might be going wrong.

 

[goss9394]

Here is the current full code

Thanks
-goss

Sub pus_FilterMealPlan()
'Get_Rows is UDF
'Globals wbBook, wsData, wsFormulas, wsHeader, rnFormula,
wsMealPlan, wsCom

Dim Rng As Range
Dim rngCopyTo As Range
Dim rngCopyFrom As Range
Dim mySwitch As String
Dim cntvalRange As Long
Dim lngRows As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

'Declarations
mySwitch = "MP"
lngRows = 0

Set wbBook = ThisWorkbook

With wbBook
Set wsData = .Worksheets("Data")
Set wsFormulas = .Worksheets("Formulas")
Set wsMealPlan = .Worksheets("MealPlan")
Set wsComp = .Worksheets("COMP")
End With

Worksheets("Data").Activate
With wsData
Set Rng = .Range("A1:L" & Get_Rows)
End With

With Rng
.AutoFilter Field:=12, Criteria1:=mySwitch
End With

With wsData
Set rngCopyFrom = .Range("A1:L" &
Get_Rows).SpecialCells(xlCellTypeVisible)
End With

Worksheets("MealPlan").Activate
With wsMealPlan
lngRows = Range("A65536").End(xlUp).Row
Range("A1:L" & lngRows).Clear
Set rngCopyTo = .Range("A39")
End With

rngCopyFrom.Copy
With rngCopyTo
.PasteSpecial xlPasteValuesAndNumberFormats
.PasteSpecial xlPasteFormats
End With

With wsMealPlan
cntvalRange = .Range("L65536").End(xlUp).Row
End With

With wbBook.Worksheets("MealPlan")
Do While cntvalRange >= 40
If .Cells(cntvalRange, 12) <> mySwitch Then
.Cells(cntvalRange, 12).EntireRow.Delete
End If
cntvalRange = cntvalRange - 1
Loop
End With

Worksheets("Data").Activate
With Rng
.AutoFilter
End With

'Reset / Cleanup
Set wbBook = Nothing
Set wsData = Nothing
Set wsMealPlan = Nothing
Set Rng = Nothing
Set valRange = Nothing
Set rngCopyTo = Nothing
Set rgnCopyFrom = Nothing

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True



End Sub