Any way to pretty this up?

M

Matt S

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
 
D

Don Guillett

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

Gary Keramidas

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
 
M

Matt S

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 said:
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 address removed)
Matt S said:
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
Click to expand...

.
Click to expand...
 
M

Matt S

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 said:
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 said:
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
Click to expand...

.
Click to expand...
 
J

JLGWhiz

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


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



Matt S said:
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 said:
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 address removed)
Matt S said:
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
Click to expand...

.
Click to expand...
Click to expand...
 
A

Archimedes' Lever

That is your code creating a named range.


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


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



Matt S said:
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 said:
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 address 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

.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

conso macro 0
If Cells.Find returns run-time error '91': Object variableor With block variable not set 1
Data Cleanse Macro Running Very Slow 0
Get macro to run on all sheets not just the active sheet 2
Find copy data from a cell in Personal workbook and copy it to a cellin current workbook 14
On error goto specified Line 6
Loop to End 0
Error for a replace. It replace ALL instead of selected column 2

Top