Moving Info from onw sheet to another

[Mike]

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

 

[JP]

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

 

[JP]

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

 

[Mike]

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

 

[JP]

Check out this site for help on how to use:

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


HTH,
JP

 

[Mike]

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?

 

[Gord Dibben]

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

 

[JP]

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

 

[JP]

LOL Gord!!!!





--JP

 

[Mike]

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]

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

 

[Mike]

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]

What are the security settings under Tools>Options>Security>Macros?

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

 

[JP]

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 Data>Filter>Autofilter
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

 

[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

 

[JP]

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

 

[Mike]

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]

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

 

[Gord Dibben]

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.

File>Save As>File Type>MS 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
Insert>Worksheet.


Gord

 

[Mike]

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