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


"Joanne" wrote:

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


"Joanne" wrote:

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


"Joanne" wrote:

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
End Sub