Target specific sheet

[JoeExcelHelp]

Trying to incorporate the following whereas the sheet doesn't need to be selected and it only references "ClassDist"
With Worksheets("ClassDist")
End With

Code: Select all

Sub SortClassDist()
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Range("A17:AI141").Sort Key1:=Range("D16"), Header:=xlYes
    Range("AK17:BS141").Sort Key1:=Range("AN16"), Header:=xlYes
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    
End Sub

[HansV]

Like this:

Code: Select all

Sub SortClassDist()
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    With Worksheets("ClassDist")
        .Range("A17:AI141").Sort Key1:=.Range("D16"), Header:=xlYes
        .Range("AK17:BS141").Sort Key1:=.Range("AN16"), Header:=xlYes
    End With
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub

[JoeExcelHelp]

Ha.. I should have tried it, that was easy for some reason i have no confidence in myself :) Thank You

[JoeExcelHelp]

Same topic
This code requires the sheet be selected in order for it to run.. How could I run the code without the sheet selected?
does it involve "defining shd and wbD then Set shD = wbD.Sheets("Data4")
I tried it but not certain if I accounted for everything

Code: Select all

Sub CombineAllData4()
    Dim shM As Worksheet
    Dim Sht As Worksheet
    Dim r As Long
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Set shM = Worksheets("Data4")
    ' Clear the Data sheet
    shM.Range("2:" & shM.Rows.Count).ClearContents

    For Each Sht In ActiveWorkbook.Worksheets
        Select Case Sht.Name
            Case "Schedule", "Hours", "Attrition", "Orientation", "Actuals", "ClassDist", "HiringPlan", "Data", "Data2", "Data3", "Data4", "Data5", "Data6"
                ' Ignore these sheets
            Case Else
                r = shM.Range("A" & shM.Rows.Count).End(xlUp).Row + 1
                shM.Range("A" & r).Resize(82).Value = Sht.Name
            ' Copy range, then paste values
            
            Sht.Range("E74:BF114").Copy
                shM.Range("B" & r).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            
            Sht.Range("E115:BF155").Copy
                shM.Range("B" & r + 41).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            
        End Select
    Next Sht
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

[HansV]

As far as I can tell, this code does not require a specific sheet to be selected before running it.

[JoeExcelHelp]

You are correct Hans.. I just realized I have a SheetActivate WB code that runs when a sheet is selected.. Any way of avoiding other codes influencing that sheet?

Code: Select all

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  Sh.Range("G33").Select
End Sub

[HansV]

Near the beginning of your CombineAllData4 macro, where you set calculation to manual and disable screen updating, insert a line

Code: Select all

    Application.EnableEvents = False

This prevents Excel from executing event procedures such as Workbook_SheetActivate.
Near the end, where you set calculation to automatic and enable screen updating again, insert

Code: Select all

    Application.EnableEvents = True

[JoeExcelHelp]

Thank You Hans