Don and Gary thanks so much. I probably gave more information than needed
and also made the post too broad. I was more looking for a syntax cleaning.
This is what I have so far in my attempts to clean it up... I'm stuck on the
last part where I define the final pasted range as a name. Don I will not be
able to send you the file. It's got too much classified information in it.
Here is what I have so far:

Sheets("Engine Data").Select
Cells.Find(What:="[Hertz]").Activate
ActiveCell.Range("A2", Range("A2").End(xlToRight)).Find
What:="FG_HC").Activate
Range(Selection, Selection.End(xlDown)).Copy
Destination:=Sheets("MFCs").Range("F10")

'It works up to this point perfectly... then the following line doesn't work
out. I'm trying to get rid of the LastRow reference.

ActiveWorkbook.Names.Add Name:="FG_HC",
RefersTo:=Sheets("MFCs").ActiveCell.Range 'how do I do this refers to?

Thanks,
Matt

"Don Guillett" wrote:

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Matt S" wrote in message
...
I am trying to copy data from "Engine Data" and paste it into a new sheet.
Engine Data contains many columns of FG_HC... I only want the one under
the
[Hertz] heading. The format of the Engine Data looks like the following:

[Mode]
FG_NOX FG_HC FG_CO
data data data

[Hertz]
FG_NOX FG_HC FG_CO
data data data

etc


Here is my code:

Sheets("Engine Data").Select
Cells.Find(What:="[Hertz]", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Range("A2", Range("A2").End(xlToRight)).Select
Selection.Find(What:="FG_HC", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Copy
Sheets("MFCs").Select
Range("F10").Select
ActiveSheet.Paste
ActiveWorkbook.Names.Add Name:="FG_HC", RefersToR1C1:="=MFCs!R13C6:R" &
LastRow & "C"


It's not pretty and I have to do this 11 times with different FG species.
(CO, HC, NO, etc.)

Any help would be appreciated!
Thanks,
Matt

.