Help with combining 2 macros into 1

[SCPbrito]

I have 2 Macros I'm trying to combine into 1. And the first part runs fine but the 2nd one is not executing. Not sure what I'm missing. I even get the macro complete message at the end.

Sub OneClickTest()
'
' OneClickTest Macro
'

'

Dim i As Long
  
  Application.ScreenUpdating = False
  For i = 1 To Range("H" & Rows.Count).End(3).Row
    Select Case Left(Range("H" & i).Value, 1)
      Case "6": Range("I" & i).Value = "Cellphone Repair"
      Case "7": Range("I" & i).Value = "Metro PCS"
      Case "8": Range("I" & i).Value = "Cricket"
      Case Else
        Select Case Range("H" & i).Value
            Case "WR_BALAJI_OTHER", "WR_BALAJI_CRICKET"
                Range("I" & i).Value = "Cricket"
        End Select
    End Select
  Next
  Application.ScreenUpdating = True


    ActiveWorkbook.Worksheets("Scan_Record").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Scan_Record").Sort.SortFields.Add2 Key:= _
        Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Scan_Record").Sort
    .SetRange Range("$A$1").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("B:B").NumberFormat = "[$-en-US]m/d/yy h:mm AM/PM;@"
    
    
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim t As Long
    Dim currentTime As Date
    Dim cutoffTime As Date
  
    ' Set the worksheet
    Set ws = ActiveWorkbook.Sheets("Scan_Record") ' Change "Sheet1" to your sheet name
  
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add2 Key:= _
        Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    'With ws.Sort
    With ws.Sort
        .SetRange ws.Range("$A$1").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ws.Columns("B:B").NumberFormat = "[$-en-US]m/d/yy h:mm AM/PM;@"   ' XXX Not sure you need this
    ' Find the last row with data in column B
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    ' Get the current date and time
    currentTime = Now
    ' Set the cutoff time for the current day at 6:50 AM
    cutoffTime = DateValue(currentTime) + TimeValue("06:50:00")

    ' Loop from the last row to the first row (bottom-up)
    Application.ScreenUpdating = False
    For t = lastRow To 1 Step -1
        ' Check if the timestamp in column A is before the cutoff time
        If IsDate(ws.Cells(i, 2).Value) Then
            If ws.Cells(i, 2).Value < cutoffTime Then
                ' Delete the row if the condition is met
                ws.Rows(i).Delete
            End If
        End If
    Next t
    Application.ScreenUpdating = True
  
    MsgBox "Macro complete!"

End Sub

 

[Joe4]

Did you try stepping into your code, and using the F8 key to go through your code one line at at time while you watch what is happening to your workbook as you go through it?
Many times it will be obvious what the issue is as you watch it proceed through your code while watching the workbook.

Note that for your second part of your code, if you are not on the "Scan_Record" sheet, every range reference will need to include a sheet reference. Or else that part will default to whatever the current sheet is at the time. Some people may use a line like this at the beginning to ensure everything runs against the correct sheet:

ws.Select

Otherwise, I think you will need to update lines like this:

   ws.Sort.SortFields.Add2 Key:= _
        Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

to this:

   ws.Sort.SortFields.Add2 Key:= _
        ws.Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

There are also a few commands/functions that do not work well if you are not physically on that sheet. I do not recall what they are off the top of my head at the moment, but those sort of issues should become evident when you step through the code line-by-line.

 

[SCPbrito]

I tried adding ws. but it didnt work. I'm trying to break it down one macro at a time. When I use this macro below it doesn't work. i get a subscript out of range error.

Sub ViewPick()
    '
    ' BalajiRename2 Macro
    '

    ' Declare a worksheet variable and set it to the "View_Pick" sheet
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("View_Pick")

    Dim i As Long

    Application.ScreenUpdating = False

    ' Loop through the rows in Column H on the specified worksheet
    For i = 1 To ws.Range("H" & ws.Rows.Count).End(xlUp).Row
        Select Case Left(ws.Range("H" & i).Value, 1)
            Case "6": ws.Range("I" & i).Value = "Cellphone Repair"
            Case "7": ws.Range("I" & i).Value = "Metro PCS"
            Case "8": ws.Range("I" & i).Value = "Cricket"
            Case Else
                Select Case ws.Range("H" & i).Value
                    Case "WR_BALAJI_OTHER", "WR_BALAJI_CRICKET"
                        ws.Range("I" & i).Value = "Cricket"
                End Select
        End Select
    Next

    Application.ScreenUpdating = True
End Sub

 

[SCPbrito]

It highlights the line below. Even though i clearly have a worksheet with that name.

Set ws = ThisWorkbook.Sheets("View_Pick")

 

[MARK858]

Are you sure that there aren't any leading or trailing spaces in your sheet name? and is the code in the same workbook as the Sheets("View_Pick") sheet?

 

[SCPbrito]

I have no spaces before or after. My code is stored in my personal workbook. Is that the issue?

 

[MARK858]

Is that the issue?

ThisWorkbook refers to the workbook the code resides in so unless the sheet you want the code to work on is in your Personal workbook (and I can think of no reason why it would be) then yes it is your issue.

 

[SCPbrito]

Im still new to Excel. How can I adjsut my code to ensure it runs in the correct workbook? I assume with Active.workbook?

 

[MARK858]

If it is the Active Workbook then refer to it as ActiveWorkbook (no period in the middle) or omit the workbook entirely, if it isn't the Active Workbook then use it's name