J
Joanne
I am trying to use the following code to identify users choice from
listbox, compare it to ws in master wb, when the users choice and the
master ws names are the same, I want to copy the ws from the master ws,
but what happens is the loop keeps cycling thru all the worksheets,
going right past the matching ws name.I've tried restructuring the lines
of code in the loop but I cannot get it to take the matched name, found
= true, and ws.copy.
Could someone please check my code and see where I am messed up?
Also, when you do ws.copy, does it copy the worksheet to the office
clipboard? I have looked at the clipboard and all that is there is the
line ws.copy and when i paste it, that's all i get ;o (
Public Sub lbVendor_Click()
Dim fname As String, spath As String
Dim wb As Workbook, ws As Worksheet
Dim found As Boolean
With lbVendor
fname = .List(.ListIndex) 'capture the value of the list index#
selected
Debug.Print fname
If obHouse Then
spath = "C:\Pricing\House\"
ElseIf obDan Then
spath = "C:\Pricing\Dan\"
ElseIf obEdwin Then
spath = "C:\Pricing\Edwin\"
ElseIf obJeff Then
spath = "C:\Pricing\Jeff\"
ElseIf obJohn Then
spath = "C:\Pricing\John\"
End If
Debug.Print spath
End With
Workbooks.Open spath & fname
Workbooks.Open "c:\Pricing\Outlook Master Pricing.xls"
found = False
For Each ws In Worksheets
If ws.Name = fname Then
found = True
Exit For
End If
'Next ws
If found = True Then
ws.Copy
End If
Debug.Print ws.Name
Next ws
End Sub
Thanks a lot for your time and consideration of my problem
Joanne
listbox, compare it to ws in master wb, when the users choice and the
master ws names are the same, I want to copy the ws from the master ws,
but what happens is the loop keeps cycling thru all the worksheets,
going right past the matching ws name.I've tried restructuring the lines
of code in the loop but I cannot get it to take the matched name, found
= true, and ws.copy.
Could someone please check my code and see where I am messed up?
Also, when you do ws.copy, does it copy the worksheet to the office
clipboard? I have looked at the clipboard and all that is there is the
line ws.copy and when i paste it, that's all i get ;o (
Public Sub lbVendor_Click()
Dim fname As String, spath As String
Dim wb As Workbook, ws As Worksheet
Dim found As Boolean
With lbVendor
fname = .List(.ListIndex) 'capture the value of the list index#
selected
Debug.Print fname
If obHouse Then
spath = "C:\Pricing\House\"
ElseIf obDan Then
spath = "C:\Pricing\Dan\"
ElseIf obEdwin Then
spath = "C:\Pricing\Edwin\"
ElseIf obJeff Then
spath = "C:\Pricing\Jeff\"
ElseIf obJohn Then
spath = "C:\Pricing\John\"
End If
Debug.Print spath
End With
Workbooks.Open spath & fname
Workbooks.Open "c:\Pricing\Outlook Master Pricing.xls"
found = False
For Each ws In Worksheets
If ws.Name = fname Then
found = True
Exit For
End If
'Next ws
If found = True Then
ws.Copy
End If
Debug.Print ws.Name
Next ws
End Sub
Thanks a lot for your time and consideration of my problem
Joanne