get data from any sheet with VBA

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
I have this code that I am using to send data to any sheet in my workbook.
Code:
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?
Code:
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
I have this code that I am using to send data to any sheet in my workbook.
Code:
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?
Code:
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

I believe it can be done
 
Upvote 0
I am still hoping someone will look at this for me
 
Upvote 0
Kelly Mort,
If I am understanding your request correctly, then you want to run the code in the second box on what ever sheet is selected by the code in the first box? Are all these sheets in the same workbook?

thanks,
Computerman
 
Upvote 0
Yes they are all in the same workbook. The first code is what i use to send data. I have a work that has the second code and this one just send data to one sheet only. So i want to get data from sheets based on selection from the combobox
 
Last edited:
Upvote 0
Kelly Mort,
If I am understanding your request correctly, then you want to run the code in the second box on what ever sheet is selected by the code in the first box? Are all these sheets in the same workbook?

thanks,
Computerman

Yes they are all in the same workbook. The first code is what i use to send data. I have a work that has the second code and this one just send data to one sheet only. So i want to get data from sheets based on selection from the combobox

Can it work the way i am thinking?
 
Upvote 0
Kelly mort,
So instead of using your current code to select the sheet:
With Sheet1.Range("C3:C303")

Use:
with Sheets(sht).range("C3:C303")

this way what sheet was chosen will be used by the second codeset

Hope that helps,
Computerman
 
Upvote 0
Kelly mort,
So instead of using your current code to select the sheet:
With Sheet1.Range("C3:C303")

Use:
with Sheets(sht).range("C3:C303")

this way what sheet was chosen will be used by the second codeset

Hope that helps,
Computerman

I tried and it shows the error message instead of populating the listbox
 
Upvote 0
Kelly mort,
So instead of using your current code to select the sheet:
With Sheet1.Range("C3:C303")

Use:
with Sheets(sht).range("C3:C303")

this way what sheet was chosen will be used by the second codeset

Hope that helps,
Computerman

Okay just saw the mistake . Have to set the sht = combobox1.value
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top