PPOOQQ,
Welcome to the MrExcel forum.
1. What version of Excel and Windows are you using?
2. Are you using a PC or a Mac?
Sample raw data in worksheet Sheet1:
Excel 2007
| A | B | C | D | E | F | G | H |
---|
1 | BATCH | IntDate | AccountNum | LineDescription | Product Type | DebitAmt | CreditAmt | FacilityID |
2 | IPF-2607 | 26/07/2014 | -11002 | Clem20140726 | Fee1 | 0 | 44.88 | 75 |
3 | IPF-2607 | 26/07/2014 | -11002 | Clem20140726 | Fee1 | 0 | 88.66 | 75 |
4 | IPF-2607 | 26/07/2014 | -11036 | Clem20140726 | Fee1 | 44.88 | 0 | 75 |
5 | IPF-2607 | 26/07/2014 | -11036 | Clem20140726 | Fee1 | 88.66 | 0 | 75 |
6 | IPF-2607 | 26/07/2014 | END | Clem20140726 | 0 | 0 | 0 | EN |
7 | IPF-2607 | 26/07/2014 | | Clem20140726 | 0 | 0 | 0 | |
8 | | | | | | | | |
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1
After the macro in a new worksheet Results:
Excel 2007
| A | B | C | D | E | F | G | H |
---|
1 | BATCH | IntDate | AccountNum | LineDescription | Product Type | DebitAmt | CreditAmt | FacilityID |
2 | IPF-2607 | 26/07/2014 | -11002 | Clem20140726 | Fee1 | 0 | 44.88 | 75 |
3 | IPF-2607 | 26/07/2014 | -11002 | Clem20140726 | Fee1 | 0 | 88.66 | 75 |
4 | IPF-2607 | 26/07/2014 | -11036 | Clem20140726 | Fee1 | 44.88 | 0 | 75 |
5 | IPF-2607 | 26/07/2014 | -11036 | Clem20140726 | Fee1 | 88.66 | 0 | 75 |
6 | | | | | | | | |
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Results
Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).
1. Copy the below code
2. Open your NEW workbook
3. Press the keys
ALT +
F11 to open the Visual Basic Editor
4. Press the keys
ALT +
I to activate the Insert menu
5. Press
M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys
ALT +
Q to exit the Editor, and return to Excel
8. To run the macro from Excel press
ALT +
F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.
Code:
Sub CopyAboveEND()
' hiker95, 07/28/2014, ME794581
Dim w1 As Worksheet, wr As Worksheet
Dim crng As Range
Set w1 = Sheets("Sheet1")
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wr = Sheets("Results")
wr.UsedRange.ClearContents
Set crng = w1.Columns(3).Find("END", LookAt:=xlWhole)
If crng Is Nothing Then
MsgBox "The word 'END' was not found - macro terminated!"
Exit Sub
ElseIf Not crng Is Nothing Then
w1.Range("A1:H" & crng.Row - 1).Copy Destination:=wr.Range("A1:H" & crng.Row - 1)
Application.CutCopyMode = False
End If
With wr
.Columns.AutoFit
.Activate
End With
End Sub
Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension
.xlsm
Then run the
CopyAboveEND macro.