VBA to reference listbox item

rigray

New Member
Joined
Jul 12, 2011
Messages
9
I have a problem with my vba code. When ran, it opens a userform and the user can select a .xls and click ok. It runs fine. However, if the user has ANY other xls files open it copies contents from those as well. I just want it to copy from the .xls selected in the listbox. Here is the code of interest.

Code:
'Deletes any accidentally saved sheets from previous run
    Do Until Sheets(1).Name = "SOTAnalogs"
        Application.DisplayAlerts = False
        Worksheets(1).delete
    Loop
    Application.DisplayAlerts = True
    
    'Opens each worksheet selected as individual spreadsheets
    Dim i, added_sheet_count As Integer
        Workbooks.Open ListBox1.List(i)
    
    'Name of open workbook
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    
    Unload UserForm2

    'Moves individual sheets into the same workbook adding it to the first page position each time
    i = 0
    Counter_temp = 0
    For Each w In Workbooks
        If w.Name <> ThisWorkbook.Name Then
            w.Sheets(1).copy before:=ThisWorkbook.Sheets(1)
            w.Sheets(2).copy After:=ThisWorkbook.Sheets(1)
            w.Sheets(3).copy After:=ThisWorkbook.Sheets(2)
            w.Sheets(4).copy After:=ThisWorkbook.Sheets(3)

            'Check for Counter data in PA Sheet
            If w.Sheets(5).Cells(7, 1).Value <> Empty Then
                w.Sheets(5).copy After:=ThisWorkbook.Sheets(4)
                Counter_temp = 1
            End If

            i = i + 1
        End If
    Next w
    
    wb.Close SaveChanges:=False

TIA
 

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