I'm new at this so I'm not sure of the validity of my question. I'm
trying to import data from a standardized format. The problem is that
the number of rows will vary depending on the set of data. So I need
some code that will take a selection starting from "Operating
Expenses" to the last expense (always 2 cells above "Total Operating
Expenses"). This is what I've got so far:

Sub CommandButton1_Click()
Sheets("Cash Flow").Select
ActiveSheet.Range("A1").Select
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset(1, 0).Value = "Operating Expenses"
Set TopCell = Cells(ActiveCell.Row, ActiveCell.Column)
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset(1, 0).Value = "Total Operating Expenses"
ActiveCell.Offset(-1, 0).Select
Set BottomCell = Cells(ActiveCell.Row, ActiveCell.Column)

Range(TopCell, BottomCell).Select
Selection.Copy
Sheets("Sheet1").Select
Selection.Paste
End Sub

I keep getting the "selection method of range class failed" error for
the "Range(TopCell, BottomCell).Select"

Any suggestions?

Thanks!

try:
Sub CommandButton1_Click()

Dim topcell As Range
Dim bottomcell As Range

Sheets("Cash Flow").Select
Set Cell = Range("a1")
Do
Set Cell = Cell.Offset(1, 0)
Loop Until Cell.Value = "Operating Expenses"

Set topcell = Cells(Cell.Row, Cell.Column)

Do
Set Cell = Cell.Offset(1, 0)
Loop Until Cell.Value = "Total Operating Expenses"

Cell.Offset(-2, 0).Select

Set bottomcell = Cells(ActiveCell.Row, ActiveCell.Column)

Range(topcell, bottomcell).Copy Sheets("Sheet1").Range("a1")


End Sub

" wrote:

I'm new at this so I'm not sure of the validity of my question. I'm
trying to import data from a standardized format. The problem is that
the number of rows will vary depending on the set of data. So I need
some code that will take a selection starting from "Operating
Expenses" to the last expense (always 2 cells above "Total Operating
Expenses"). This is what I've got so far:

Sub CommandButton1_Click()
Sheets("Cash Flow").Select
ActiveSheet.Range("A1").Select
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset(1, 0).Value = "Operating Expenses"
Set TopCell = Cells(ActiveCell.Row, ActiveCell.Column)
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset(1, 0).Value = "Total Operating Expenses"
ActiveCell.Offset(-1, 0).Select
Set BottomCell = Cells(ActiveCell.Row, ActiveCell.Column)

Range(TopCell, BottomCell).Select
Selection.Copy
Sheets("Sheet1").Select
Selection.Paste
End Sub

I keep getting the "selection method of range class failed" error for
the "Range(TopCell, BottomCell).Select"

Any suggestions?

Thanks!