J
Joanne
I have this sub routine to get the value of fname:
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#
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
End With
Workbooks.Open spath & fname
End Sub
Now I need to get the value of fname into this sub routine so that I can
cycle thru a different wb to find the matching ws name and open and copy
it so that I can paste it as a new ws in the wb in the above routine:
Public Sub GetMstrWS()
Dim fname As String
Workbook.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
Worksheet.Copy 'copy the entire worksheet
End If
End Sub
BTW, all of the above coding is either thru the generous help of the
mvps in these forums or from reading thru the forums, for which I am
very grateful.
Could someone please help me get fname from Public Sub lbVendor_Click
into Public Sub GetMstrWS? I was thinking maybe these subs need to be
functions because I thought I read somewhere in the forums that you need
a function if you want to share variables, but I tried it and it didn't
work at all - (could be my coding - I'm not real good at it)
Thanks for your time and consideration
Joanne
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#
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
End With
Workbooks.Open spath & fname
End Sub
Now I need to get the value of fname into this sub routine so that I can
cycle thru a different wb to find the matching ws name and open and copy
it so that I can paste it as a new ws in the wb in the above routine:
Public Sub GetMstrWS()
Dim fname As String
Workbook.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
Worksheet.Copy 'copy the entire worksheet
End If
End Sub
BTW, all of the above coding is either thru the generous help of the
mvps in these forums or from reading thru the forums, for which I am
very grateful.
Could someone please help me get fname from Public Sub lbVendor_Click
into Public Sub GetMstrWS? I was thinking maybe these subs need to be
functions because I thought I read somewhere in the forums that you need
a function if you want to share variables, but I tried it and it didn't
work at all - (could be my coding - I'm not real good at it)
Thanks for your time and consideration
Joanne