Read data from worksheet based on selection from ListBox

[abukarim]

Hi All,

I am very new to VBA and I need your help on this code. My userform has a ListBox that gets populated with text. Each text string is a name of worksheet in the same workbook. The idea of this code is that when I highlight one or more of text string at the ListBox, a command button is supposed to look at the each of these worksheet name and read the data range from cell A1 to last string in column A and go to the next sheet and so on. Below is my code. I ran the code and I am getting an error at "Set ws.Name = ListBox1.Selected(i)". Can you look at the code and let me know what I am doing wrong.
Thanks
-----------------------------
Private Sub AvailableServices_Click()
Dim cell As Range
Dim rng As Range
Dim ws As Worksheet
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
Set ws.Name = ListBox1.Selected(i)
Set rng = ws.Range("A1:")

For Each cell In rng.Cells
Me.ListBox2.AddItem cell.Value
Next cell
End If
Next i
End Sub

 

[jumbledore]

Hi All,

I am very new to VBA and I need your help on this code. My userform has a ListBox that gets populated with text. Each text string is a name of worksheet in the same workbook. The idea of this code is that when I highlight one or more of text string at the ListBox, a command button is supposed to look at the each of these worksheet name and read the data range from cell A1 to last string in column A and go to the next sheet and so on. Below is my code. I ran the code and I am getting an error at "Set ws.Name = ListBox1.Selected(i)". Can you look at the code and let me know what I am doing wrong.
Thanks
-----------------------------
Private Sub AvailableServices_Click()
Dim cell As Range
Dim rng As Range
Dim ws As Worksheet
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
Set ws.Name = ListBox1.Selected(i)
Set rng = ws.Range("A1:")

For Each cell In rng.Cells
Me.ListBox2.AddItem cell.Value
Next cell
End If
Next i
End Sub

can you try this

Set ws = ListBox1.Selected(i)

and change

Set rng = ws.Range("A1:")

to

Set rng = ws.Range("A:A")

also your list box 2 will contain entries for blank cells. so you need to use worksheetfunction.counta method to get only non blank cells in your listbox

 

[abukarim]

can you try this

Set ws = ListBox1.Selected(i)

and change

Set rng = ws.Range("A1:")

to

Set rng = ws.Range("A:A")

also your list box 2 will contain entries for blank cells. so you need to use worksheetfunction.counta method to get only non blank cells in your listbox

Thanks Jumbledore.
I tried your suggestion but now I am getting a different error "Compile error: Type mismatch" and .Selected(i) is highlighed. Any reason why?

 

[jumbledore]

Thanks Jumbledore.
I tried your suggestion but now I am getting a different error "Compile error: Type mismatch" and .Selected(i) is highlighed. Any reason why?

sorry change

[COLOR=#574123]Set ws = ListBox1.Selected(i)

to [/COLOR]

[COLOR=#574123]Set ws = ListBox1.[/COLOR][COLOR=#333333]Column[/COLOR][COLOR=#574123](i)[/COLOR]

 

[abukarim]

sorry change

[COLOR=#574123]Set ws = ListBox1.Selected(i)[/COLOR]

to

[COLOR=#574123]Set ws = ListBox1.[/COLOR][COLOR=#333333]Column[/COLOR][COLOR=#574123](i)[/COLOR]

I really appreciate your help here. Ok, so I tried your last suggestion and now I am getting a different error message. The error message is "Compile error: Object required" and the ws is highlighted on the code. What will be the object in this case for ws? Any idea

 

[abukarim]

I really appreciate your help here. Ok, so I tried your last suggestion and now I am getting a different error message. The error message is "Compile error: Object required" and the ws is highlighted on the code. What will be the object in this case for ws? Any idea

Sorry the error message that I sent you is not the right one. The error message after I changed the ListBox1.column(i) is "Run-time error '381': Could not get the column property. Invalid property array index"

 

[jumbledore]

Sorry the error message that I sent you is not the right one. The error message after I changed the ListBox1.column(i) is "Run-time error '381': Could not get the column property. Invalid property array index"

sorry instead of

Set ws = ListBox1.Column(i)

use

Set ws = ListBox1.List(i)

can you please upload your workbook?