coping all rows between two rows

[cirugio]

I need to copy all rows between the words “Report 1” and “Column Totals” into cell A1 of a new workbook worksheet. I am not exactly certain how to go about this. Hoping someone can help. I am at a total loss.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Example of Report:
<o></o>
Report 1
-
-
-
-
-
-
-
-
-
Column Totals:

 

[JPM]

I would use a macro like this one:
Sub Macro1()
Columns("A:A").Select 'Select column to search for the key words
Selection.Find(What:="Report 1", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate 'search for "Report 1"
Rng1 = ActiveCell.Row 'Beginning range
Selection.Find(What:="Column Totals", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate 'search for "Column Totals"
Rng2 = ActiveCell.Row 'Ending range
Rows(Rng1 & ":" & Rng2).Select 'Select rows to be copied
Selection.Copy
Workbooks.Add 'Open a new workbook
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select
End Sub

 

[cirugio]

Thanks for the reply. That worked out great.

Wondering if you can answer another question pertaining to the code.

I actually need to Paste only the values and not the formulas in my copy. I tried replacing your "ActiveSheet.Paste" logic with

ActiveSheet.PasteSpecial Paste:=xlValues ', Operation:=xlNone, SkipBlanks:=False, Transpose:=False

but it fails on me. Is there a why to only copy and paste the special values?

 

[dave3009]

Try like this maybe

Sub Macro1()
Dim stRange As Long
Dim enRange As Long
With Columns("A:A")
    stRange = .Find(What:="Report 1", After:=Range("A1")).Row 'Beginning range
    enRange = .Find(What:="Column Totals", After:=Range("A1")).Row 'search for "Column Totals"
End With
Rows(stRange & ":" & enRange).Copy
Workbooks.Add 'Open a new workbook
Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

 

[cirugio]

Thanks Dave. That worked out well.
I had to expand the logic (see below) so that it will then delete rows which contain certain values within column A (i.e. "testing", "Client A", "Client B", and blanks).

The logic works, but it only deletes the blank rows, and totally ignores the others. Would you know what it is doing this?

With ActiveSheet
.AutoFilterMode = False
With Range("A6:A" & enRange)
.AutoFilter 1, Criteria1:=Array("testing", "Client A", "Client B", "=")
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With