for next loop problem
 

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

for next loop problem
 

Put a breakpoint at If ws.Name = fname Then
examine both ws.Name and fname in the immediate window. Remember that VB is
case sensitive. Also it's not unusual to find an extra space either at the
front or end of a sheet tab name.
You could put these statements just before that If statement:
Debug.Print Chr$(34) & fname & Chr$(34), _
Debug.Print Chr$(34) & ws.Name & Chr$(34)
that will enclose both strings within " characters, making seeing extra
spaces easier.


"Joanne" wrote:

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

for next loop problem
 

Thanks for the pointer. I actually didn't know that I was dealing with
case sensitive. I will use your suggestion and see how it plays out
Joanne
removethis wrote:

Put a breakpoint at If ws.Name = fname Then
examine both ws.Name and fname in the immediate window. Remember that VB is
case sensitive. Also it's not unusual to find an extra space either at the
front or end of a sheet tab name.
You could put these statements just before that If statement:
Debug.Print Chr$(34) & fname & Chr$(34), _
Debug.Print Chr$(34) & ws.Name & Chr$(34)
that will enclose both strings within " characters, making seeing extra
spaces easier.


"Joanne" wrote:

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

for next loop problem
 

To make the test case insensitive, use either UCase or LCase for both
arguments as:
If UCase(ws.Name) = UCase(fname) Then
doesn't matter which you use, result is same: all alpha characters in each
are examined as upper/lower case only.

And if you want to eliminate chance of a space either at start or end of
either/both, then add Trim to the test as:
If UCase(Trim(ws.Name)) = UCase(Trim(fname)) Then


"Joanne" wrote:

Thanks for the pointer. I actually didn't know that I was dealing with
case sensitive. I will use your suggestion and see how it plays out
Joanne
removethis wrote:

Put a breakpoint at If ws.Name = fname Then
examine both ws.Name and fname in the immediate window. Remember that VB is
case sensitive. Also it's not unusual to find an extra space either at the
front or end of a sheet tab name.
You could put these statements just before that If statement:
Debug.Print Chr$(34) & fname & Chr$(34), _
Debug.Print Chr$(34) & ws.Name & Chr$(34)
that will enclose both strings within " characters, making seeing extra
spaces easier.


"Joanne" wrote:

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