Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
working with the correct worksheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
working with the correct worksheet
there is nothing in your code that does anything, so it is hard to say where
you might be going wrong. -- Regards, Tom Ogilvy wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
working with the correct worksheet
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 Tom Ogilvy wrote: there is nothing in your code that does anything, so it is hard to say where you might be going wrong. -- Regards, Tom Ogilvy wrote in message ups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort function not working correct | Excel Discussion (Misc queries) | |||
The F2 function key is not working properly. How do I correct | Excel Discussion (Misc queries) | |||
Formulas showing as correct but not working | Excel Worksheet Functions | |||
Auto Correct Isn't Working (Excel 2003) | Excel Discussion (Misc queries) | |||
My formulas stopped working even though equations are correct | Excel Discussion (Misc queries) |