Excel: get data from any sheet with VBA


I have this code that I am using to send data to any sheet in my workbook.


Private Sub CmdSend_Click()
    Dim cNum As Integer
    Dim x As Integer
    Dim nextrow As Range
    Dim sht As String
    sht = ComboBox1.Value
        If Me.ComboBox1.Value = "" Then
            MsgBox "Please select a sheet from the combobox"
            Exit Sub
        End If
        cNum = 13
        Set nextrow = Sheets(sht).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        For x = 1 To cNum
            nextrow = Me.Controls("Reg" & x).Value
            Set nextrow = nextrow.Offset(0, 1)
        Next
        For x = 1 To cNum
            Me.Controls("Reg" & x).Value = ""
        Next
            
End Sub
How do I modify the one below here to get the data from the sheet for me?

Sub Lookup()
        Dim rngFind As Range
        Dim strFirstFind As String
        
        On Error GoTo errHandler:
        lstWin.ColumnCount = 11
        Dim myArray As Variant
        myArray = [C2].Resize(, lstWin.ColumnCount + 1).Value
        lstWin.List = myArray
        lstWin.Clear
        With Sheet1.Range("C3:C303")
        Set rngFind = .Find(txtLookup.Text, LookIn:=xlValues, lookat:=xlPart)
        If Not rngFind Is Nothing Then
        strFirstFind = rngFind.Address
        
        Do
        If rngFind.Row > 1 Then
        lstWin.AddItem rngFind.Offset(0, -1)
        lstWin.List(lstWin.ListCount - 1, 1) = rngFind
        lstWin.List(lstWin.ListCount - 1, 2) = rngFind.Offset(0, 1)
        lstWin.List(lstWin.ListCount - 1, 3) = rngFind.Offset(0, 2)
        lstWin.List(lstWin.ListCount - 1, 4) = rngFind.Offset(0, 3)
        lstWin.List(lstWin.ListCount - 1, 5) = rngFind.Offset(0, 4)
        lstWin.List(lstWin.ListCount - 1, 6) = rngFind.Offset(0, 5)
        lstWin.List(lstWin.ListCount - 1, 7) = rngFind.Offset(0, 6)
        lstWin.List(lstWin.ListCount - 1, 8) = rngFind.Offset(0, 7)
        lstWin.List(lstWin.ListCount - 1, 9) = rngFind.Offset(0, 8)
        lstWin.List(lstWin.ListCount - 1, 10) = rngFind.Offset(0, 9)
        End If
        Set rngFind = .FindNext(rngFind)
        Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
        End If
        End With
        Me.Reg1.Enabled = False
        On Error GoTo 0
        Exit Sub
        
errHandler::
            MsgBox "Check your entry for errors"
    End Sub
My headache just got tough. I hope to get your attention


This question generated 18 answers. To proceed to the answers, click here.

This thread is current as of May 10, 2017.


For more resources for Microsoft Excel