Hi
i want to compare items in listbox to list of open workbooks, where the name matches i want to copy that sheet into a new sheet. problem here is the list of open workbooks have same named sheet so when i run the macro it copies sheet(for example accounts) from all the open booksbooks..Problem i am facing is if there is only one workbook containing sheet called accounts it works fine but if there are more then one workbooks open with a sheet called Accounts , it will paste all those sheets . can anyone help please as in how can i overcome this problem of selecting items from list box and when that name matches open workbooks ,sheet from only that workbook is copied
my code is
Sub Clic()
'Function Initialize()
Dim shtToCopy As Worksheet
Dim wkbDest As Workbook
Set wkbDest = Workbooks("working test")
Dim wb As Workbook
For Each wb In Workbooks
On Error Resume Next 'Will continue if an error results
'***********************
If Workbooks(ListBox1.Value).Select = wb.Name Then
wb.Activate
Set shtToCopy = wb.Sheets("Accounts")
shtToCopy.Copy wkbDest.Sheets(3)
'Delete.Sheet (3)
End If
Next wb
end sub
i want to compare items in listbox to list of open workbooks, where the name matches i want to copy that sheet into a new sheet. problem here is the list of open workbooks have same named sheet so when i run the macro it copies sheet(for example accounts) from all the open booksbooks..Problem i am facing is if there is only one workbook containing sheet called accounts it works fine but if there are more then one workbooks open with a sheet called Accounts , it will paste all those sheets . can anyone help please as in how can i overcome this problem of selecting items from list box and when that name matches open workbooks ,sheet from only that workbook is copied
my code is
Sub Clic()
'Function Initialize()
Dim shtToCopy As Worksheet
Dim wkbDest As Workbook
Set wkbDest = Workbooks("working test")
Dim wb As Workbook
For Each wb In Workbooks
On Error Resume Next 'Will continue if an error results
'***********************
If Workbooks(ListBox1.Value).Select = wb.Name Then
wb.Activate
Set shtToCopy = wb.Sheets("Accounts")
shtToCopy.Copy wkbDest.Sheets(3)
'Delete.Sheet (3)
End If
Next wb
end sub