VBA Worksheets.Select Not Working

Question

I'm trying to write some code that copies a worksheet from a workbook to a sheet in a different workbook. Once the worksheet is loaded into the new workbook, I want to lock the first row of the sheet and also add a filter to it. I can lock the row fine, but for some reason I can't add a filter to the first row. I keep getting errors no matter what I've been trying. Here's what I've ended up with so far:

Private Sub CommandButton3_Click()

'IMPORT DATA

Sheets("Raw Data").Unprotect

Application.DisplayAlerts = False
Sheets("Raw Data").Delete
Sheets.Add After:=Worksheets(1)
Worksheets(2).Name = "Raw Data"
Application.DisplayAlerts = True

Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim SourceRcount As Long
Dim lastRow As Long
Dim lastColumn As Long
Dim n As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant
Dim Path As String
Dim FileName As String
Dim FileType As String
Dim strFName As String
Dim FSO As Scripting.FileSystemObject
Set FSO = New FileSystemObject

    SaveDriveDir = CurDir
    MyPath = "H:"
    ChDrive MyPath
    ChDir MyPath

    FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", MultiSelect:=True)
    If IsArray(FName) Then
        Application.ScreenUpdating = False
        Set basebook = ThisWorkbook
        For n = LBound(FName) To UBound(FName)
            Set mybook = Workbooks.Open(FName(n))
            Set sourceRange = mybook.Worksheets(1).Cells
            SourceRcount = sourceRange.Rows.count
            Set destrange = basebook.Sheets("Raw Data").Cells
            sourceRange.Copy destrange
            mybook.Close True
        Next n
    End If

    ChDrive SaveDriveDir
    ChDir SaveDriveDir

    'Locks first row of sheet
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True

    FilterReport

    Sheets("Main").Select
    Cells(5, 4).Value = FName
    strFName = Cells(5, 4).Value
    FileName = FSO.GetFileName(strFName)
    Cells(5, 4).Value = FileName
    Application.CutCopyMode = False

Application.ScreenUpdating = True

Sheets("Raw Data").Protect

End Sub

Private Sub FilterReport()

Dim lastRow As Long
Dim lastColumn As Long

    Worksheets("Raw Data").Activate
    Worksheets("Raw Data").Select

    lastRow = Range("A" & Rows.count).End(xlUp).row
    lastColumn = Cells(1, Columns.count).End(xlToLeft).Column

    Range(Cells(1, 1), Cells(1, lastColumn)).Select
    Selection.AutoFilter

End Sub

Through some debugging I found out that one of the issues is that the "Raw Data" sheet never gets selected in the FilterReport sub. For some reason it just selects the "Main" sheet and I have no idea why.

Revised Sub:

Private Sub FilterReport()

Dim RD As Worksheet
Dim lastRow As Long
Dim lastColumn As Long

    Set RD = Sheets("CS-CRM Raw Data")

    lastRow = RD.Range("A" & Rows.count).End(xlUp).row
    lastColumn = RD.Cells(1, Columns.count).End(xlToLeft).Column

    With RD.Range(Cells(1, 1), Cells(1, lastColumn))
        .AutoFilter
    End With

End Sub

I get errors on the With statement now, but lastRow and lastColumn are correct now. I don't know why it would switch sheets in between lastColumn and the With statement.

Create your objects and then work with them rather than using `.Select`. See [THIS](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros/10718179#10718179) — Siddharth Rout, Aug 14 '14 at 15:25
Ok, I've created a worksheet object and set it equal to the "Raw Data" sheet. This fixed the issue where Raw Data wasn't getting selected for 'lastRow' and 'lastColumn'. However, it still wants to default back to the "Main" sheet when I try to apply the autofilter. I'll post the code as I have it now above. — user3783788, Aug 14 '14 at 15:45
You haven't fully qualified the objects ;) For example Change `RD.Range(Cells(1, 1), Cells(1, lastColumn))` to `RD.Range(RD.Cells(1, 1), RD.Cells(1, lastColumn))` — Siddharth Rout, Aug 14 '14 at 15:53
my guess, Worksheets("Raw Data") is hidden, and you cant select hidden sheets, but you still can pull its data without making them visible by qualifying them `Worksheets("Raw Data").range("A1")`. If you have to qualify a lot of times use `with`, see example in my answer — Patrick Lepelletier, Aug 15 '14 at 12:56

score 0 · Answer 1 · answered Aug 15 '14 at 13:04

here is the completed cose using with properly :

Private Sub FilterReport()

Dim RD As Worksheet
Dim lastRow As Long
Dim lastColumn As Long

Set RD = Sheets("CS-CRM Raw Data")

with RD

    lastRow = .Range("A" & .Rows.count).End(xlUp).row 'i guess you forgot the first dot at .rows.count
    lastColumn = .Cells(1, .Columns.count).End(xlToLeft).Column

    With .Range(.Cells(1, 1), .Cells(lastrow, lastColumn)) 'here some dots forgotten from you AGAIN (before .cells)
    'sorry i replaced a 1 by lastrow wiwh makes more sense to me 
        .AutoFilter
    End With

end with

Set RD = Nothing

End Sub

VBA Worksheets.Select Not Working

1 Answers1