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

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
(E-Mail Removed)
"Matt S" <(E-Mail Removed)> wrote in message
news:38CA4ED8-2544-4E56-B60B-(E-Mail Removed)...
>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

maybe something like this, but you don't give enough information to actually
complete the code, (like where you discern the lastrow from). but have a look
and maybe you can complete it on your own:


Option Explicit
Sub test()
Dim arr As Variant
Dim rngfound As Range
Dim itmfound As Range
Dim i As Long
Dim lastrow As Long
arr = Array("FG_HC", "CO", "HC", "NO")

With Sheets("Engine Data")
Set rngfound = .Cells.Find(What:="[Hertz]", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, _
SearchFormat:=False)

If Not rngfound Is Nothing Then
For i = LBound(arr) To UBound(arr)
With .Range(rngfound.Address,
..Range(rngfound.Address).End(xlToRight))

Set itmfound = .Find(What:=arr(i),
After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart,
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False).Activate
End With
.Range(rngfound.Address,
..Range(rngfound.Address).End(xlToRight)).Copy
Sheets("MFCs").Range("F10").PasteSpecial xlPasteAll
ActiveWorkbook.Names.Add Name:="FG_HC",
RefersToR1C1:="=MFCs!R13C6:R" & _
lastrow & "C"
Next
End If
End With
End Sub
--


Gary Keramidas
Excel 2003


"Matt S" <(E-Mail Removed)> wrote in message
news:38CA4ED8-2544-4E56-B60B-(E-Mail Removed)...
>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

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
> (E-Mail Removed)
> "Matt S" <(E-Mail Removed)> wrote in message
> news:38CA4ED8-2544-4E56-B60B-(E-Mail Removed)...
> >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
>
> .
>

ok, I tried one more thing and this seemed to work. Is there a way to make
it less condensed? For example... how come the following doesn't work?
Sheets("Engine Data").Cells.Find(What:="[Hertz]").Activate

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")
ActiveWorkbook.Names.Add Name:="FG_HC",
RefersTo:=Sheets("MFCs").Range("F10:F" & Sheets("MFCs").Cells(Rows.Count,
"E").End(xlUp).Row)


Thanks again!
Matt


"Gary Keramidas" wrote:

> maybe something like this, but you don't give enough information to actually
> complete the code, (like where you discern the lastrow from). but have a look
> and maybe you can complete it on your own:
>
>
> Option Explicit
> Sub test()
> Dim arr As Variant
> Dim rngfound As Range
> Dim itmfound As Range
> Dim i As Long
> Dim lastrow As Long
> arr = Array("FG_HC", "CO", "HC", "NO")
>
> With Sheets("Engine Data")
> Set rngfound = .Cells.Find(What:="[Hertz]", After:=ActiveCell,
> LookIn:=xlFormulas, _
> LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:=False, _
> SearchFormat:=False)
>
> If Not rngfound Is Nothing Then
> For i = LBound(arr) To UBound(arr)
> With .Range(rngfound.Address,
> ..Range(rngfound.Address).End(xlToRight))
>
> Set itmfound = .Find(What:=arr(i),
> After:=ActiveCell, _
> LookIn:=xlFormulas, LookAt:=xlPart,
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, MatchCase:=False, _
> SearchFormat:=False).Activate
> End With
> .Range(rngfound.Address,
> ..Range(rngfound.Address).End(xlToRight)).Copy
> Sheets("MFCs").Range("F10").PasteSpecial xlPasteAll
> ActiveWorkbook.Names.Add Name:="FG_HC",
> RefersToR1C1:="=MFCs!R13C6:R" & _
> lastrow & "C"
> Next
> End If
> End With
> End Sub
> --
>
>
> Gary Keramidas
> Excel 2003
>
>
> "Matt S" <(E-Mail Removed)> wrote in message
> news:38CA4ED8-2544-4E56-B60B-(E-Mail Removed)...
> >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
>
> .
>

RefersTo:=Sheets("MFCs").ActiveCell.Range 'how do I do this refers to?


RefersTo:=Sheets("MFCs").ActiveCell.Address



"Matt S" <(E-Mail Removed)> wrote in message
news:7D9215F7-3770-4B3A-8DAC-(E-Mail Removed)...
> 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
>> (E-Mail Removed)
>> "Matt S" <(E-Mail Removed)> wrote in message
>> news:38CA4ED8-2544-4E56-B60B-(E-Mail Removed)...
>> >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
>>
>> .
>>

That is your code creating a named range.


On Sun, 3 Jan 2010 15:41:43 -0500, "JLGWhiz" <(E-Mail Removed)> wrote:

>RefersTo:=Sheets("MFCs").ActiveCell.Range 'how do I do this refers to?
>
>
>RefersTo:=Sheets("MFCs").ActiveCell.Address
>
>
>
>"Matt S" <(E-Mail Removed)> wrote in message
>news:7D9215F7-3770-4B3A-8DAC-(E-Mail Removed)...
>> 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
>>> (E-Mail Removed)
>>> "Matt S" <(E-Mail Removed)> wrote in message
>>> news:38CA4ED8-2544-4E56-B60B-(E-Mail Removed)...
>>> >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
>>>
>>> .
>>>
>