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

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...

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

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 -

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

Mike

Don't despair, we'll get you going yet. The revised code from JP autofilters
the activesheet and finds all rows with "13" in Column F.

Adds a new sheet and copies the filtered rows to that sheet.

Sub autofiltvisibleandcopytonewwkbk()
Dim rng As Range
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

rng.Copy Sheets.Add.Range("A1")
End Sub


Gord


On Wed, 9 Jan 2008 11:46:01 -0800, Mike wrote:

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

Gord,
Again thanks for your help... You too JP, But this is WAY to much for me. I
have gotten past the compiling error now it is saying something about my
marco is not digitly signed.... I give up, Surrender even. is there like a
=index type command I can use instead ???? I hope so otherwise I am lost.

Mike

"Gord Dibben" wrote:

Mike

Don't despair, we'll get you going yet. The revised code from JP autofilters
the activesheet and finds all rows with "13" in Column F.

Adds a new sheet and copies the filtered rows to that sheet.

Sub autofiltvisibleandcopytonewwkbk()
Dim rng As Range
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

rng.Copy Sheets.Add.Range("A1")
End Sub


Gord


On Wed, 9 Jan 2008 11:46:01 -0800, Mike wrote:

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

What are the security settings under ToolsOptionsSecurityMacros?

Set to medium and try again.

But maybe you have gone as far as you're willing to work for.

Too bad......VBA and macros opens a whole new world of efficiency for Excel
users.


Gord

On Wed, 9 Jan 2008 19:39:06 -0800, Mike wrote:

Gord,
Again thanks for your help... You too JP, But this is WAY to much for me. I
have gotten past the compiling error now it is saying something about my
marco is not digitly signed.... I give up, Surrender even. is there like a
=index type command I can use instead ???? I hope so otherwise I am lost.

Mike

"Gord Dibben" wrote:

Mike

Don't despair, we'll get you going yet. The revised code from JP autofilters
the activesheet and finds all rows with "13" in Column F.

Adds a new sheet and copies the filtered rows to that sheet.

Sub autofiltvisibleandcopytonewwkbk()
Dim rng As Range
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

rng.Copy Sheets.Add.Range("A1")
End Sub


Gord


On Wed, 9 Jan 2008 11:46:01 -0800, Mike wrote:

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

At this point I recommend you just apply the autofilter to your data,
filter accordingly, then paste the rows into another sheet. This is
what the macro was doing for you, but no reason why you could not do
it manually.

1. Click inside your data area, choose DataFilterAutofilter
2. Click the dropdown arrow in the "Pier" column and choose the value
you want to view
3. Highlight and copy the rows, paste into a new sheet.


HTH,
JP


On Jan 9, 10:39*pm, Mike wrote:
Gord,
*Again thanks for your help... You too JP, But this is WAY to much for me. I
have gotten past the compiling error now it is saying something about my
marco is not digitly signed.... I give up, Surrender even. is there like a
=index type command I can use instead ???? I hope so otherwise I am lost..

Mike

I am willing to work at it more. Just have ALOT of questions that need to be
answered. Like Once a macro is made can you immbed it into a worksheet. How
many macro's can you have running in in one workbook at a time. I have so
many many you can suggest a course I can learn about Excel and it's other
fuctions. Here is another question I came up with. With the code below you
sent me. the SUB line does the autofiltvisibleandcopytonewwkbk() have to be
there or is that the name of the filter? Next is where it says rng. Copy
Sheets.Add.Range("A1") is the A1 supposed to be the name of the sheet I want
the filter to search for the info??

Sub autofiltvisibleandcopytonewwkbk()
Dim rng As Range
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

rng.Copy Sheets.Add.Range("A1")
End Sub

You asked:

Once a macro is made can you immbed it into a worksheet.

Not exactly, it is placed in a hidden code module which is part of the
programming environment.

How many macro's can you have running in in one workbook at a time.

*Running*, only one, but you can have many different macros stored in
a module and can use them as needed.

the SUB line does the autofiltvisibleandcopytonewwkbk() have to be there or is that the name of the filter?

Yes, the "Sub" line tells Excel that you have a procedure and its
name. You could change it to anything you want, I just made up a name
for purposes of demonstration.

where it says rng.Copy Sheets.Add.Range("A1") is the A1 supposed to be the name of the sheet I want the filter to search for the info??

A1 is the cell on the new sheet added by the macro, where the filtered
rows are going to be placed. Remember you asked for the applicable
rows to be copied to a new sheet, this is the functionality that does
it for you.

You definitely want to check out http://www.rondebruin.nl/code.htm and
http://www.mvps.org/dmcritchie/excel/getstarted.htm for help on basics
like where to put macros and how to use them.


HTH,
JP


On Jan 10, 11:51*am, Mike wrote:
I am willing to work at it more. Just have ALOT of questions that need to be
answered. Like Once a macro is made can you immbed it into a worksheet. How
many macro's can you have running in in one workbook at a time. I have so
many many you can suggest a course I can learn about Excel and it's other
fuctions. Here is another question I came up with. *With the code below you
sent me. the SUB line does the autofiltvisibleandcopytonewwkbk() have to be
there or is that the name of the filter? Next is where it says rng. Copy
Sheets.Add.Range("A1") is the A1 supposed to be the name of the sheet I want
the filter to search for the info??

*Sub autofiltvisibleandcopytonewwkbk()
* Dim rng As Range
* * * *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

* rng.Copy Sheets.Add.Range("A1")
* End Sub

WOW!!!!!!!!!!!!!!!! This is really starting to come together... BUT one other
question. Suppose I already have a sheet formated (i.e) with headers and
footers set. How do I get the macro to put the info onto that sheet? or is
that asking to much??? LOL

Thank you SOOOOOO Much JP

"JP" wrote:

You asked:

Once a macro is made can you immbed it into a worksheet.

Not exactly, it is placed in a hidden code module which is part of the
programming environment.

How many macro's can you have running in in one workbook at a time.

*Running*, only one, but you can have many different macros stored in
a module and can use them as needed.

the SUB line does the autofiltvisibleandcopytonewwkbk() have to be there or is that the name of the filter?

Yes, the "Sub" line tells Excel that you have a procedure and its
name. You could change it to anything you want, I just made up a name
for purposes of demonstration.

where it says rng.Copy Sheets.Add.Range("A1") is the A1 supposed to be the name of the sheet I want the filter to search for the info??

A1 is the cell on the new sheet added by the macro, where the filtered
rows are going to be placed. Remember you asked for the applicable
rows to be copied to a new sheet, this is the functionality that does
it for you.

You definitely want to check out http://www.rondebruin.nl/code.htm and
http://www.mvps.org/dmcritchie/excel/getstarted.htm for help on basics
like where to put macros and how to use them.


HTH,
JP


On Jan 10, 11:51 am, Mike wrote:
I am willing to work at it more. Just have ALOT of questions that need to be
answered. Like Once a macro is made can you immbed it into a worksheet. How
many macro's can you have running in in one workbook at a time. I have so
many many you can suggest a course I can learn about Excel and it's other
fuctions. Here is another question I came up with. With the code below you
sent me. the SUB line does the autofiltvisibleandcopytonewwkbk() have to be
there or is that the name of the filter? Next is where it says rng. Copy
Sheets.Add.Range("A1") is the A1 supposed to be the name of the sheet I want
the filter to search for the info??

Sub autofiltvisibleandcopytonewwkbk()
Dim rng As Range
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

rng.Copy Sheets.Add.Range("A1")
End Sub

This updated code will add the header and footer to the new worksheet.
It assumes that the footer text is in the worksheet in a range
A100:G103. If you put this text somewhere else, simply adjust the
range in the macro.


Sub autofiltvisibleandcopytonewwkbk()
Dim rng As Excel.Range
Dim rng2 As Excel.Range
Dim NewSht As Worksheet

Set rng = ActiveSheet.UsedRange.Rows
' adjust the line below to fit your worksheet
Set rng2 = Range("A100:G103")

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

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

Set NewSht = Sheets.Add
rng.Copy NewSht.Range("A1")

rng2.Copy NewSht.Range("A65536").End(xlUp).Offset(2, 0)

End Sub

On Jan 10, 12:36*pm, Mike wrote:
WOW!!!!!!!!!!!!!!!! This is really starting to come together... BUT one other
question. Suppose I already have a sheet formated (i.e) with headers and
footers set. How do I get the macro to put the info onto that sheet? or is
that asking to much??? LOL

Thank you SOOOOOO Much JP

Mike

Good to see you did not surrender and are sticking with it.

You can add to the macro to format the new sheet as you wish, which requires
more lines of code or you could go with a sheet template.

Create a workbook with one sheet.

Format that sheet as you wish with headers and footers and anything else you
want.

FileSave AsFile TypeMS Excel Template(*.xlt)

Save as SHEET.......let Excel add the .xlt

Place this SHEET.xlt in your XLSTART folder

In the macro change this line

rng.Copy Sheets.Add.Range("A1")

which uses the Excel default worksheet,

to this, which uses your custom sheet.

rng.Copy Sheets.Add(Type:="Worksheet").Range("A1")

NOTE: SHEET.xlt will also be the default for any sheet added through
InsertWorksheet.


Gord

On Thu, 10 Jan 2008 09:36:00 -0800, Mike wrote:

WOW!!!!!!!!!!!!!!!! This is really starting to come together... BUT one other
question. Suppose I already have a sheet formated (i.e) with headers and
footers set. How do I get the macro to put the info onto that sheet? or is
that asking to much??? LOL

Thank you SOOOOOO Much JP

"JP" wrote:

You asked:

Once a macro is made can you immbed it into a worksheet.

Not exactly, it is placed in a hidden code module which is part of the
programming environment.

How many macro's can you have running in in one workbook at a time.

*Running*, only one, but you can have many different macros stored in
a module and can use them as needed.

the SUB line does the autofiltvisibleandcopytonewwkbk() have to be there or is that the name of the filter?

Yes, the "Sub" line tells Excel that you have a procedure and its
name. You could change it to anything you want, I just made up a name
for purposes of demonstration.

where it says rng.Copy Sheets.Add.Range("A1") is the A1 supposed to be the name of the sheet I want the filter to search for the info??

A1 is the cell on the new sheet added by the macro, where the filtered
rows are going to be placed. Remember you asked for the applicable
rows to be copied to a new sheet, this is the functionality that does
it for you.

You definitely want to check out http://www.rondebruin.nl/code.htm and
http://www.mvps.org/dmcritchie/excel/getstarted.htm for help on basics
like where to put macros and how to use them.


HTH,
JP


On Jan 10, 11:51 am, Mike wrote:
I am willing to work at it more. Just have ALOT of questions that need to be
answered. Like Once a macro is made can you immbed it into a worksheet. How
many macro's can you have running in in one workbook at a time. I have so
many many you can suggest a course I can learn about Excel and it's other
fuctions. Here is another question I came up with. With the code below you
sent me. the SUB line does the autofiltvisibleandcopytonewwkbk() have to be
there or is that the name of the filter? Next is where it says rng. Copy
Sheets.Add.Range("A1") is the A1 supposed to be the name of the sheet I want
the filter to search for the info??

Sub autofiltvisibleandcopytonewwkbk()
Dim rng As Range
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

rng.Copy Sheets.Add.Range("A1")
End Sub

Thanks JP But I dont think you understand what I trying to do. Dont get me
wrong Thank you SOOO MUCH for all your help. If I ramble I am sorry.. I am
trying to set up a filter that can take the info from the (Sort Sheet) Where
the info is stored. Sort it By Piers (Which there are 16 of them) to each
pier having its own sheet With it's own headers and footers. and do this
without having to copy and paste. I will see what this you code you gave can
do.

Thanks again
Mike

"JP" wrote:

This updated code will add the header and footer to the new worksheet.
It assumes that the footer text is in the worksheet in a range
A100:G103. If you put this text somewhere else, simply adjust the
range in the macro.


Sub autofiltvisibleandcopytonewwkbk()
Dim rng As Excel.Range
Dim rng2 As Excel.Range
Dim NewSht As Worksheet

Set rng = ActiveSheet.UsedRange.Rows
' adjust the line below to fit your worksheet
Set rng2 = Range("A100:G103")

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

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

Set NewSht = Sheets.Add
rng.Copy NewSht.Range("A1")

rng2.Copy NewSht.Range("A65536").End(xlUp).Offset(2, 0)

End Sub

On Jan 10, 12:36 pm, Mike wrote:
WOW!!!!!!!!!!!!!!!! This is really starting to come together... BUT one other
question. Suppose I already have a sheet formated (i.e) with headers and
footers set. How do I get the macro to put the info onto that sheet? or is
that asking to much??? LOL

Thank you SOOOOOO Much JP

Someone posted a link earlier today to a macro that can do this, I'll
try to find it for you.

--JP

On Jan 10, 1:26*pm, Mike wrote:
Thanks JP But I dont think you understand what I trying to do. Dont get me
wrong Thank you SOOO MUCH for all your help. If I ramble I am sorry.. I am
trying to set up a filter that can take the info from the (Sort Sheet) Where
the info is stored. Sort it By Piers (Which there are 16 of them) to each
pier having its own sheet With it's own headers and footers. and do this
without having to copy and paste. I will see what this you code you gave can
do.

Thanks again
Mike

Thanks I was just looking at that page

"JP" wrote:

Someone posted a link earlier today to a macro that can do this, I'll
try to find it for you.

--JP

On Jan 10, 1:26 pm, Mike wrote:
Thanks JP But I dont think you understand what I trying to do. Dont get me
wrong Thank you SOOO MUCH for all your help. If I ramble I am sorry.. I am
trying to set up a filter that can take the info from the (Sort Sheet) Where
the info is stored. Sort it By Piers (Which there are 16 of them) to each
pier having its own sheet With it's own headers and footers. and do this
without having to copy and paste. I will see what this you code you gave can
do.

Thanks again
Mike

Check out the thread "Extract Whole Row If Q" in the
microsoft.public.excel.programming group, you will need some more
macro expertise to debug and implement this code for your project.


HTH,
JP


On Jan 10, 1:26*pm, Mike wrote:
Thanks JP But I dont think you understand what I trying to do. Dont get me
wrong Thank you SOOO MUCH for all your help. If I ramble I am sorry.. I am
trying to set up a filter that can take the info from the (Sort Sheet) Where
the info is stored. Sort it By Piers (Which there are 16 of them) to each
pier having its own sheet With it's own headers and footers. and do this
without having to copy and paste. I will see what this you code you gave can
do.

Thanks again
Mike



"JP" wrote:
This updated code will add the header and footer to the new worksheet.
It assumes that the footer text is in the worksheet in a range
A100:G103. If you put this text somewhere else, simply adjust the
range in the macro.

*Sub autofiltvisibleandcopytonewwkbk()
* Dim rng As Excel.Range
* Dim rng2 As Excel.Range
* Dim NewSht As Worksheet

* * * *Set rng = ActiveSheet.UsedRange.Rows
' adjust the line below to fit your worksheet
* * * *Set rng2 = Range("A100:G103")

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

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

*Set NewSht = Sheets.Add
* rng.Copy NewSht.Range("A1")

* rng2.Copy NewSht.Range("A65536").End(xlUp).Offset(2, 0)

* End Sub

On Jan 10, 12:36 pm, Mike wrote:
WOW!!!!!!!!!!!!!!!! This is really starting to come together... BUT one other
question. Suppose I already have a sheet formated (i.e) with headers and
footers set. How do I get the macro to put the info onto that sheet? or is
that asking to much??? LOL

Thank you SOOOOOO Much JP- Hide quoted text -

- Show quoted text -

I am a MACRO making fool. I am getting it to everything I need it took a
while to get the exact coding and order down but I am getting there... I just
hope there is not a limit of lines of code I can put into a macro. LOL!!!! My
first Macro has about 308 lines of code...LOL!!!!!!

"JP" wrote:

Check out the thread "Extract Whole Row If Q" in the
microsoft.public.excel.programming group, you will need some more
macro expertise to debug and implement this code for your project.


HTH,
JP


On Jan 10, 1:26 pm, Mike wrote:
Thanks JP But I dont think you understand what I trying to do. Dont get me
wrong Thank you SOOO MUCH for all your help. If I ramble I am sorry.. I am
trying to set up a filter that can take the info from the (Sort Sheet) Where
the info is stored. Sort it By Piers (Which there are 16 of them) to each
pier having its own sheet With it's own headers and footers. and do this
without having to copy and paste. I will see what this you code you gave can
do.

Thanks again
Mike



"JP" wrote:
This updated code will add the header and footer to the new worksheet.
It assumes that the footer text is in the worksheet in a range
A100:G103. If you put this text somewhere else, simply adjust the
range in the macro.

Sub autofiltvisibleandcopytonewwkbk()
Dim rng As Excel.Range
Dim rng2 As Excel.Range
Dim NewSht As Worksheet

Set rng = ActiveSheet.UsedRange.Rows
' adjust the line below to fit your worksheet
Set rng2 = Range("A100:G103")

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

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

Set NewSht = Sheets.Add
rng.Copy NewSht.Range("A1")

rng2.Copy NewSht.Range("A65536").End(xlUp).Offset(2, 0)

End Sub

On Jan 10, 12:36 pm, Mike wrote:
WOW!!!!!!!!!!!!!!!! This is really starting to come together... BUT one other
question. Suppose I already have a sheet formated (i.e) with headers and
footers set. How do I get the macro to put the info onto that sheet? or is
that asking to much??? LOL

Thank you SOOOOOO Much JP- Hide quoted text -

- Show quoted text -