I am trying to reference the name of a workbook selected using a listbox in a for loop. I am simply copying worksheets from the workbook in the listbox to a new worksheet where the code is located. I keep getting an error. I changed a couple of things to make it easier to see where I wanted to reference the name. OPW is the name I'm referencing.
'Opens each worksheet selected as individual spreadsheets
Dim i, added_sheet_count As Integer
Workbooks.Open ListBox1.List(i)
'Name of open workbook
OPW = ActiveWorkbook.Name
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 OPW
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
Bookmarks