Wow Ron, thank you very much for the great help.
The .PasteSpecial, like AutoFilter, is entirely new to me.
If I understand the code correctly, we are actually creating a new
workbook and putting the record returned from the list box into the
workbook.
What I actually need to do is read the record to identify which wss off
the master price sheet I need to add to the new workbook by comparing
the ws.name on the record to the ws.name in the master wb and then
copy/pastespecial those I need to the new wb.
Then I need to look at the worksheets in the new wb, identify the
columns that I need to show on each of these ws (will always be colA,
colB and colC plus 1 more column per ws), hide the rest of the columns,
and finally show the new wb to the user.
Your help in teaching me how to isolate the record that I need is a
major hurdle to have solved towards this app (and useful for most future
apps too I am sure). Now I need to learn how to read the record to pass
the ws names I need to a function? that will do the copy/paste job. Then
another function to read the record again to see what cols from each ws
in the new wb need to be hidden from view.
I'm thinking something like this might work (though I am really just
stumbling around trying to find my way here) to add the ws to the new
wb:
For each ws.name on record
if ws.name on record then
copy (or .PasteSpecial?) ws.name from master to new wb
end if
next
This needs to compare the record fields to the master wb wss.name
somehow
Then to hide the cols I don't want showing
For each ws.name in new wb
For each colAddr on record
if coladdr not on record then
hide.coladdr
end if
next
next
I'm sure my syntax needs work here but for now I am just trying to think
out the jobs that need to be accomplished.
I have to go thru many file folders to get the ws.names and coladdrs for
each of the customers' records, so I have time to keep pondering these
problems, keep reading the groups, and learn and test as much as I can.
Your help goes a long way in making this feasible for me to finish
Thank you.
Could you recommend a book or helper site to study that doesn't contain
888 bazillion pages? Just a good index and some good examples would be
great and something my schedule could handle.
TIA
Joanne
Ron said:
Rather than chop up your workbook, I'd recommend creating a new one (with
vba) and pasting the appropriate records into it.
Something like this variation of my previously posted code:
'---------Start of Code--------
Private Sub cmdPullSelectedData_Click()
Dim wbkNew As Workbook
Dim strCrit_1
Dim strCrit_2
Dim strCrit_3
With lbxCustName
If .ListIndex <> -1 Then
strCrit_1 = .List(.ListIndex, 0)
strCrit_2 = .List(.ListIndex, 1)
strCrit_3 = .List(.ListIndex, 2)
With Range("rngAllData")
.AutoFilter Field:=1, Criteria1:=strCrit_1
.AutoFilter Field:=2, Criteria1:=strCrit_2
.AutoFilter Field:=3, Criteria1:=strCrit_3
'Create a new workbook and put the selected record into it
Set wbkNew = Workbooks.Add
'Copy the visible cells from the filtered list
.SpecialCells(xlCellTypeVisible).Copy
With wbkNew.Sheets(1).Range("A1")
'Paste the col widths, values, and formats into the new wkbk
.PasteSpecial Paste:=xlPasteColumnWidths
.PasteSpecial Paste:=xlPasteFormats
.PasteSpecial Paste:=xlPasteValues
'Turn off copy mode
Application.CutCopyMode = False
End With
'Turn off the autofilter
.Parent.AutoFilterMode = False
End With
End If
End With
End Sub
'---------End of Code--------
Does that help?
***********
Regards,
Ron
XL2002, WinXP
:
Ron
That is way too cool. I have never used autofilter before. I think I
need to study up on it.
Before I go any further developing this little app, I would like your
advice on whether I am going about it correctly or if I should come in
from another angle.
I am going to add several more fields (12 maybe) to each record, and
depending on the record info,
I am going to open a copy of the master pricing sheet,
read the record to see which columns I need to show,
write the code to hide the columns I don't need to show,
then show the 'new' ws to the user so they can do their thing
So the next job I have is to first complete my table
Then learn how to cycle thru the record & hide unwanted columns
This should work, shouldn't it?
Again and again, thank you for all of your time and consideration of my
questions. You guys are great! I learn so much from reading thru the
groups and especially when you give my stuff your attention.
Joanne
Ron Coderre wrote:
I was only intending to show that you can isolate a row based on the user
ListBox selection. You'd want to do something with that record, then
disengage the AutoFilter. There are also other alternatives to using the
AutoFilter, but I started there because many people are familiar with its
functionality.
Try this edited code:
Private Sub cmdPullSelectedData_Click()
Dim strCrit_1
Dim strCrit_2
Dim strCrit_3
With lbxCustName
If .ListIndex <> -1 Then
strCrit_1 = .List(.ListIndex, 0)
strCrit_2 = .List(.ListIndex, 1)
strCrit_3 = .List(.ListIndex, 2)
With Range("rngAllData")
.AutoFilter Field:=1, Criteria1:=strCrit_1
.AutoFilter Field:=2, Criteria1:=strCrit_2
.AutoFilter Field:=3, Criteria1:=strCrit_3
MsgBox strCrit_1 & " " & strCrit_2 & " " & strCrit_3
'Do something with the data here...then turn off the autofilter
.Parent.AutoFilterMode = False
End With
End If
End With
Does that help?
***********
Regards,
Ron
XL2002, WinXP
:
Ron
How do I return my table to it's original state.
The only records showing are the first one, with drop down arrows in the
fields, and the isolated record.
I need to get to the table because I must add more info the the records
Thanks again
Joanne
Ron Coderre wrote:
Joanne
There was a flow control flaw in my posted code, but it shouldn't have
caused a problem...
In any case, incorporating your posted code and mine:
Private Sub cmdPullSelectedData_Click()
Dim wbNew As Workbook
Dim strCrit_1
Dim strCrit_2
Dim strCrit_3
With lbxCustName
If .ListIndex <> -1 Then
strCrit_1 = .List(.ListIndex, 0)
strCrit_2 = .List(.ListIndex, 1)
strCrit_3 = .List(.ListIndex, 2)
With Range("rngAllData")
.AutoFilter Field:=1, Criteria1:=strCrit_1
.AutoFilter Field:=2, Criteria1:=strCrit_2
.AutoFilter Field:=3, Criteria1:=strCrit_3
End With
End If
End With
Label1.Caption = strCrit_1 & " " & strCrit_2 & " " & strCrit_3
End Sub
Also...here are some alternatives to playing with Label1:
MsgBox strCrit_1 & " " & strCrit_2 & " " & strCrit_3
or...even better...
Set a break point at: With Range("rngAllData")
When the code pauses there...hover your mouse cursor over each criteria.
It's value will display.
or...
type this in the Immediate Window (then press enter):
? strCrit_1
When you're ready to let the code finish...
Press the [F5] key
Is that something you can work with?
***********
Regards,
Ron
XL2002, WinXP
:
Hi Ron
Trying to use your code but cannot get the result to print to
Label1.caption so that I can see if it is grabbing what is expected.
Here is how I wrote the code to show the record data in label1:
With lbxCustName
If .ListIndex <> -1 Then
strCrit_1 = .List(.ListIndex, 0) 'Custname
strCrit_2 = .List(.ListIndex, 1) 'CustStreet
strCrit_3 = .List(.ListIndex, 2) 'CustCity
End If
End With
With Range("rngAllData")
.AutoFilter Field:=1, Criteria1:=strCrit_1
.AutoFilter Field:=2, Criteria1:=strCrit_2
.AutoFilter Field:=3, Criteria1:=strCrit_3
End With
End Sub
Label1.Caption = strCrit_1 & " " & strCrit_2 & " " & strCrit_3
Nothing shows up in the test label - is there some other method I should
use to be able to study the results to be sure I am getting the expected
data?
Thanks for your time and knowledge
Ron Coderre wrote:
Private Sub cmdPullSelectedData_Click()
Dim wbNew As Workbook
Dim strCrit_1
Dim strCrit_2
Dim strCrit_3
With lbxCustName
If .ListIndex <> -1 Then
strCrit_1 = .List(.ListIndex, 0) 'Custname
strCrit_2 = .List(.ListIndex, 1) 'CustStreet
strCrit_3 = .List(.ListIndex, 2) 'CustCity
End If
End With
With Range("rngAllData")
.AutoFilter Field:=1, Criteria1:=strCrit_1
.AutoFilter Field:=2, Criteria1:=strCrit_2
.AutoFilter Field:=3, Criteria1:=strCrit_3
End With
