Useing Listbox value for loop

backup69

Active Member
Joined
Jan 20, 2004
Messages
271
what wrong here (listbox1.list have 4 sheet names)??
i need this code repeat all worksheet in listbox1 list
Code:
Dim ws As Worksheet
For Each ws In ListBox1.List(ListBox1.ListCount - 1)
        source1 = Label1.Caption
        Rows(source1).Select
        Selection.Copy
        dest = Label2.Caption
        Rows(dest).Select
        Selection.Insert Shift:=xlDown
Next

Error "Object required"
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,886
Office Version
  1. 365
Platform
  1. Windows
I have never tried to use ListBox in this way, so I don't even know if it is possible, however I will say this. Whenever you use a "For Loop", you usually need to enter a range of values. For example:

Code:
Dim cell as Range
For each cell in Range("A1:A100")

This tells the code to loop through all the cells in Range A1:A100.

If I understand it correctly, it appears that List(ListBox1.ListCount - 1) may not be correctly populating a range of worksheets. Even if the methodology is right, it appears it would only be referencing one sheet.
 

backup69

Active Member
Joined
Jan 20, 2004
Messages
271
TY for explain this metod. Listbox return only sheet names like Sheet1 Sheet2 Sheet3
i found solution
Code:
For i = 1 To ListBox1.ListCount
With Worksheets(ListBox1.List(i - 1)).Activate
        source1 = Label1.Caption
        Rows(source1).Select
        Selection.Copy
        dest = Label2.Caption
        Rows(dest).Select
        Selection.Insert Shift:=xlDown
    End With
Next
End If
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,163
Messages
5,768,555
Members
425,481
Latest member
ihumanl

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
Top