|
Any way to pretty this up?
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 |
Any way to pretty this up?
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" 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 |
Any way to pretty this up?
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 . |
Any way to pretty this up?
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").Ac tivate 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" 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 . |
Any way to pretty this up?
RefersTo:=Sheets("MFCs").ActiveCell.Range 'how do I do this refers to?
RefersTo:=Sheets("MFCs").ActiveCell.Address "Matt S" wrote in message ... 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 . |
Any way to pretty this up?
That is your code creating a named range.
On Sun, 3 Jan 2010 15:41:43 -0500, "JLGWhiz" wrote: RefersTo:=Sheets("MFCs").ActiveCell.Range 'how do I do this refers to? RefersTo:=Sheets("MFCs").ActiveCell.Address "Matt S" wrote in message ... 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 . |
| All times are GMT +1. The time now is 07:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com