Hi All,
I am trying to use 1 sheet for my data storage then sort that info to
another sheet that looks only for a value I am looking for. Here is a example
of what I am looking at.

Flt Dept Dest Equip Freq Pier
639 0625 SJU 757 D 3
1487 0645 ORD/RNO 757 D 13
404 0650 MCO A300 D 14
1114 0650 LGA 757 D 2
377 0700 PAP A300 D 13

Now what I need to find out is what is the COMMAND that will allow excel
to look for the data such as pier 13 and transfer the info from that line
(i.e 1487 0650 LGA 757 D 13) and move it to another sheet that is only
looking
for the pier 13 value. Not sure if anyone can help

If you were looking for a VBA solution, you could do an autofilter on
the data, then copy the visible rows to another worksheet. For
example,

Sub autofiltvisibleandcopytonewwkbk()

Dim rng As Excel.Range
Dim X As Excel.Workbook

Set rng = ActiveSheet.UsedRange.Rows

With rng
.AutoFilter Field:=6, Criteria1:="=13"
End With

With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Resize(.Rows.count -
1, .Columns.count).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

Set X = Workbooks.Add

rng.Copy X.Sheets(1).Range("A1")

End Sub


HTH,
JP



On Jan 9, 11:47*am, Mike wrote:
Hi All,
I am trying to use 1 sheet for my data storage then sort that info to
another sheet that looks only for a value I am looking for. Here is a example
of what I am looking at.

Flt * * Dept * *Dest * *Equip * Freq * *Pier
639 * * 0625 * *SJU * * 757 * * D * * * 3
1487 * *0645 * *ORD/RNO 757 * * D * * * 13
404 * * 0650 * *MCO * * A300 * *D * * * 14
1114 * *0650 * *LGA * * 757 * * D * * * 2
377 * * 0700 * *PAP * * A300 * *D * * * 13

Now what I need to find out is what is the COMMAND that will allow excel
to look for the data such as pier 13 and transfer the info from that line
(i.e 1487 0650 LGA *757 D 13) and move it to another sheet that is only
looking
for the pier 13 value. Not sure if anyone can help

Sorry I just realized you were asking for a new sheet, not a new
workbook. You could simply change "Set X = Workbooks.Add" to add a
sheet instead of a workbook, Dim X as Excel.Sheet instead, and change
the copy line to "rng.Copy X..Range("A1")" and it should still work.

HTH,
JP

On Jan 9, 12:04*pm, JP wrote:

Sub autofiltvisibleandcopytonewwkbk()

Dim rng As Excel.Range
Dim X As Excel.Workbook

Set rng = ActiveSheet.UsedRange.Rows

With rng
* * .AutoFilter Field:=6, Criteria1:="=13"
End With

With ActiveSheet.AutoFilter.Range
On Error Resume Next
* * Set rng = .Resize(.Rows.count -
1, .Columns.count).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

Set X = Workbooks.Add

rng.Copy X.Sheets(1).Range("A1")

End Sub

Ok Now I think I am getting it. But I I keep getting a compile error wher it
says
Set rng = .resize ( .Rows.count - 1, .colums.count) any help there?

"JP" wrote:

Sorry I just realized you were asking for a new sheet, not a new
workbook. You could simply change "Set X = Workbooks.Add" to add a
sheet instead of a workbook, Dim X as Excel.Sheet instead, and change
the copy line to "rng.Copy X..Range("A1")" and it should still work.

HTH,
JP

On Jan 9, 12:04 pm, JP wrote:

Sub autofiltvisibleandcopytonewwkbk()

Dim rng As Excel.Range
Dim X As Excel.Workbook

Set rng = ActiveSheet.UsedRange.Rows

With rng
.AutoFilter Field:=6, Criteria1:="=13"
End With

With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Resize(.Rows.count -
1, .Columns.count).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

Set X = Workbooks.Add

rng.Copy X.Sheets(1).Range("A1")

End Sub

Mike

That line is part of the next line........wordwrap gotcha.

Set rng = .Resize(.Rows.Count - 1, _
.Columns.Count).SpecialCells(xlCellTypeVisible)

Note I have added the underscore which is a line-continuation character.


Gord Dibben MS Excel MVP

On Wed, 9 Jan 2008 10:31:03 -0800, Mike wrote:

Ok Now I think I am getting it. But I I keep getting a compile error wher it
says
Set rng = .resize ( .Rows.count - 1, .colums.count) any help there?

"JP" wrote:

Sorry I just realized you were asking for a new sheet, not a new
workbook. You could simply change "Set X = Workbooks.Add" to add a
sheet instead of a workbook, Dim X as Excel.Sheet instead, and change
the copy line to "rng.Copy X..Range("A1")" and it should still work.

HTH,
JP

On Jan 9, 12:04 pm, JP wrote:

Sub autofiltvisibleandcopytonewwkbk()

Dim rng As Excel.Range
Dim X As Excel.Workbook

Set rng = ActiveSheet.UsedRange.Rows

With rng
.AutoFilter Field:=6, Criteria1:="=13"
End With

With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Resize(.Rows.count -
1, .Columns.count).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

Set X = Workbooks.Add

rng.Copy X.Sheets(1).Range("A1")

End Sub

LOL Gord!!!!

:-)



--JP

On Jan 9, 1:53*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Mike

That line is part of the next line........wordwrap gotcha.

Set rng = .Resize(.Rows.Count - 1, _
* * .Columns.Count).SpecialCells(xlCellTypeVisible)

Note I have added the underscore which is a line-continuation character.

Gord Dibben *MS Excel MVP

Man this is making me CrAzY !!!!! Thank you All for your help. But this WAY
OVER MY HEAD! I have a headache now from trying to get this... LOL I guess I
need to take some course's on MVB and writting macro's.

"Gord Dibben" wrote:

Mike

That line is part of the next line........wordwrap gotcha.

Set rng = .Resize(.Rows.Count - 1, _
.Columns.Count).SpecialCells(xlCellTypeVisible)

Note I have added the underscore which is a line-continuation character.


Gord Dibben MS Excel MVP

On Wed, 9 Jan 2008 10:31:03 -0800, Mike wrote:

Ok Now I think I am getting it. But I I keep getting a compile error wher it
says
Set rng = .resize ( .Rows.count - 1, .colums.count) any help there?

"JP" wrote:

Sorry I just realized you were asking for a new sheet, not a new
workbook. You could simply change "Set X = Workbooks.Add" to add a
sheet instead of a workbook, Dim X as Excel.Sheet instead, and change
the copy line to "rng.Copy X..Range("A1")" and it should still work.

HTH,
JP

On Jan 9, 12:04 pm, JP wrote:

Sub autofiltvisibleandcopytonewwkbk()

Dim rng As Excel.Range
Dim X As Excel.Workbook

Set rng = ActiveSheet.UsedRange.Rows

With rng
.AutoFilter Field:=6, Criteria1:="=13"
End With

With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Resize(.Rows.count -
1, .Columns.count).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

Set X = Workbooks.Add

rng.Copy X.Sheets(1).Range("A1")

End Sub

It might be due to poor wrapping from the ng posting. Remove the "Set
rng" statement and replace it with the one below.

Set rng = .Resize(.Rows.Count - 1, .Columns.Count). _
SpecialCells(xlCellTypeVisible)


--JP


On Jan 9, 1:31*pm, Mike wrote:
Ok Now I think I am getting it. But I I keep getting a compile error wher it
says
Set rng = .resize ( .Rows.count - 1, .colums.count) any help there?



"JP" wrote:
Sorry I just realized you were asking for a new sheet, not a new
workbook. You could simply change "Set X = Workbooks.Add" to add a
sheet instead of a workbook, Dim X as Excel.Sheet instead, and change
the copy line to "rng.Copy X..Range("A1")" and it should still work.

HTH,
JP

On Jan 9, 12:04 pm, JP wrote:

Sub autofiltvisibleandcopytonewwkbk()

Dim rng As Excel.Range
Dim X As Excel.Workbook

Set rng = ActiveSheet.UsedRange.Rows

With rng
* * .AutoFilter Field:=6, Criteria1:="=13"
End With

With ActiveSheet.AutoFilter.Range
On Error Resume Next
* * Set rng = .Resize(.Rows.count -
1, .Columns.count).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

Set X = Workbooks.Add

rng.Copy X.Sheets(1).Range("A1")

End Sub- Hide quoted text -

- Show quoted text -

Thanks JP
Never worte in VBE before but will give it a try...

"JP" wrote:

If you were looking for a VBA solution, you could do an autofilter on
the data, then copy the visible rows to another worksheet. For
example,

Sub autofiltvisibleandcopytonewwkbk()

Dim rng As Excel.Range
Dim X As Excel.Workbook

Set rng = ActiveSheet.UsedRange.Rows

With rng
.AutoFilter Field:=6, Criteria1:="=13"
End With

With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Resize(.Rows.count -
1, .Columns.count).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

Set X = Workbooks.Add

rng.Copy X.Sheets(1).Range("A1")

End Sub


HTH,
JP



On Jan 9, 11:47 am, Mike wrote:
Hi All,
I am trying to use 1 sheet for my data storage then sort that info to
another sheet that looks only for a value I am looking for. Here is a example
of what I am looking at.

Flt Dept Dest Equip Freq Pier
639 0625 SJU 757 D 3
1487 0645 ORD/RNO 757 D 13
404 0650 MCO A300 D 14
1114 0650 LGA 757 D 2
377 0700 PAP A300 D 13

Now what I need to find out is what is the COMMAND that will allow excel
to look for the data such as pier 13 and transfer the info from that line
(i.e 1487 0650 LGA 757 D 13) and move it to another sheet that is only
looking
for the pier 13 value. Not sure if anyone can help

Check out this site for help on how to use:

http://www.rondebruin.nl/code.htm


HTH,
JP

On Jan 9, 12:43*pm, Mike wrote:
Thanks JP
Never worte in VBE before but will give it a try...