Can't activate worksheet VBA

Question

I am new to coding and i cant seem to solve this problem. I am trying to copy and paste some ranges from one worksheet to another. When doing so, I continue to be prompted with an error message when the code attempts to activate the new worksheet. The Code is below. The error occurs when trying to active the "Summary" sheet before copying and pasting the ranges.

Sub nxt()
LR = Cells(Rows.Count, "A").End(xlUp).Row
Last = Cells(Rows.Count, "D").End(xlUp).Row
clryellow = RGB(256, 256, 0)


ThisWorkbook.Sheets("Rankings").Select
Sheets("Rankings").Select
ActiveSheet.Range("A1:H1").Select
 Selection.AutoFilter
  ActiveWorkbook.Worksheets("Rankings").AutoFilter.Sort.SortFields.Clear
   ActiveWorkbook.Worksheets("Rankings").AutoFilter.Sort.SortFields.Add Key:= _
    Range("H1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("Rankings").AutoFilter.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

ThisWorkbook.Sheets("Summary").Activate
Sheets("Summary").Select
Sheets("Summary").Range("A8:A18").Value = Sheets("Rankings").Range("A2:A12").Value
Sheets("Summary").Range("B8:B18").Value = Sheets("Rankings").Range("E2:E12").Value
Sheets("Summary").Range("C8:C18").Value = Sheets("Rankings").Range("G2:G12").Value
Sheets("Summary").Range("D8:D18").Value = Sheets("Rankings").Range("H2:H12").Value

ActiveWorkbook.Sheets("Summary").Activate
With ActiveSheet
For x = Last To 8 Step -1
    If (Cells(x, "D").Value) >= 6 Then
        Cells(x, "A").EntireRow.Delete
    ElseIf (Cells(x, 4).Value) < 6 Then
        Cells(x, 1).Interior.Color = clryellow
        Cells(x, 1).Font.Bold = True
        Cells(x, 4).Interior.Color = clryellow
        Cells(x, 4).Font.Bold = True
    End If
Next x
End With

For Each Worksheet In ActiveWorkbook.Worksheets
 ActiveSheet.Calculate
Next Worksheet

end sub

It isn't clear on which worksheet(s) **LR** and **Last** are intended to get their values from. — , Jul 10 '15 at 04:37
LR and Last are intended to get their values from the "Summary" sheet. The error message is Application-defined or object-defined error, and there is no trailing space after "Summary". When I originally wrote the code it worked to the extent require. When I reopened the workbook a week later to update some data it no longer worked. — Ben, Jul 10 '15 at 14:57

score 1 · Answer 1 · edited May 23 '17 at 12:14

You can .Select one or more objects (worksheets, cells, etc) into a collection but you can only .Activate one of them. Whatever is activated is always part of the selection, even if they are both the same single object. You do not need to both .Select and .Activate an object unless you are selecting more than one and require that one of them the the ActiveCell or ActiveSheet.

Essentially, a .Select method or .Activate method should be used to bring the worksheet or range object to the user's attention. It is not necessary to select or activate something in order to work with it (your value transfer speaks to that).

Here is a short rewrite of your routine that steers away from relying on .Select and .Activate to reference objects.

Sub summarizeRankings()
    Dim lstA As Long, lstD As Long, clrYellow As Long, x As Long, ws As Worksheet

    With ThisWorkbook
        With .Worksheets("Rankings")
            If .AutoFilterMode Then .AutoFilterMode = False
            With .Cells(1, 1).CurrentRegion
                With .Resize(.Rows.Count, 8)
                    .Cells.Sort Key1:=.Columns(8), Order1:=xlAscending, _
                                Orientation:=xlTopToBottom, Header:=xlYes
                    .AutoFilter
                End With
            End With
            Set ws = .Cells(1, 1).Parent
        End With
        With .Worksheets("Summary")
            .Range("A8:A18").Value = ws.Range("A2:A12").Value
            .Range("B8:B18").Value = ws.Range("E2:E12").Value
            .Range("C8:C18").Value = ws.Range("G2:G12").Value
            .Range("D8:D18").Value = ws.Range("H2:H12").Value

            lstA = .Cells(Rows.Count, "A").End(xlUp).Row
            lstD = .Cells(Rows.Count, "D").End(xlUp).Row
            clrYellow = RGB(256, 256, 0)

            For x = lstD To 8 Step -1
                If (.Cells(x, "D").Value) >= 6 Then
                    .Cells(x, "A").EntireRow.Delete
                ElseIf (.Cells(x, 4).Value) < 6 Then
                    .Cells(x, 1).Interior.Color = clrYellow
                    .Cells(x, 1).Font.Bold = True
                    .Cells(x, 4).Interior.Color = clrYellow
                    .Cells(x, 4).Font.Bold = True
                End If
            Next x
            .Activate  '<-last step brings the Summary worksheet to the front
        End With
    End With

    Application.Calculate

End Sub

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

Thank you Jeeped, this code is more concise than what i had written. The problem still persists though. When stepping through the code, it prompts application-defined or object-defined error at: .Range("A8:A18").Value = ws.Range("A2:A12").Value — Ben, Jul 10 '15 at 14:45

Can't activate worksheet VBA

1 Answers1