VBA Code Help - Userform needs to select specific page

[LNG2013]

Can somone help me with my code below - The issue I am having is that the code fails if there is ever a "sheet2" left open. I need it to always target the sheet Data5m.

What seems to happen is the sheet targets the first available sheet. This book only consists of 1 sheet, but generates other sheets later, sometimes extra sheets are left open.



Microsoft Excel Objects - ThisWorkbook code

'The following is for the Userform DataReport code

DataReport.ComboBox1.Clear
    Columns("N:N").Select
    Range("A2:HX29921").Sort Key1:=Range("N2"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
Data5m.Range("A1").AutoFilter
Call FillCombobox(Data5m.Range("N2", Data5m.Cells(Rows.Count, "N").End(xlUp)), DataReport.ComboBox1)
DataReport.Show
End Sub

Forms - DataReport

Private Sub ComboBox1_Change()  'was A, now N
    DataReport.ComboBox2.Clear
    If Data5m.FilterMode = True Then: Data5m.ShowAllData
    Data5m.Range("A1").AutoFilter field:=14, Criteria1:="=" & DataReport.ComboBox1.Value
    Call FillCombobox(Data5m.Range("X2", Data5m.Cells(Rows.Count, "X").End(xlUp)).SpecialCells(xlCellTypeVisible), DataReport.ComboBox2)
End Sub
Private Sub ComboBox2_Change()  'was EJ, now X
    DataReport.ComboBox3.Clear
    Data5m.Range("A1").AutoFilter field:=24, Criteria1:="=" & DataReport.ComboBox2.Value
    Call FillCombobox(Data5m.Range("P2", Data5m.Cells(Rows.Count, "P").End(xlUp)).SpecialCells(xlCellTypeVisible), DataReport.ComboBox3)
End Sub
Private Sub ComboBox3_Change()  'was ED, now P
    DataReport.ComboBox4.Clear
    Data5m.Range("A1").AutoFilter field:=16, Criteria1:="=" & DataReport.ComboBox3.Value
    Call FillCombobox(Data5m.Range("Q2", Data5m.Cells(Rows.Count, "Q").End(xlUp)).SpecialCells(xlCellTypeVisible), DataReport.ComboBox4)
End Sub
Private Sub ComboBox4_Change()  'was EI, now Q
    Data5m.Range("A1").AutoFilter field:=17, Criteria1:="=" & DataReport.ComboBox4.Value
End Sub
Private Sub RunButton_Click()
Unload DataReport
Call Filtered
Call AMasterBuild
End Sub
Private Sub CancelButton_Click()
Unload DataReport
End Sub

 

[Trevor G]

You can use the real sheet name rather than worksheet Tab

sheets(1) would be the first sheet in the workbook.

To check the real name out, in your vba screen look at the project window and the sheet name appears before the tab name.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> she()<br><SPAN style="color:#007F00">'Simple sample</SPAN><br>Sheets(2).Select<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>