Maybe like this:

Sub cpy()
Dim cRow As Long
Dim fRng As Range
'find first empty row on Sales Report for copy
cRow = Sheets("SALES REPORT").Cells(Rows.Count, 1).End(xlUp).Row + 1
With Sheets("January 2005")
With Sheets("January 2005").Columns(1)
Set fRng = .Find("Total")
End With
If Not fRng Is Nothing Then
.Range(.Cells(10, 7), .Cells(fRng.Row, 7)).Copy _
Sheets("SALES REPORT").Cells(cRow, 1)
End If
End With
End Sub

Hope this helps
Rowan

xcelelder wrote:
i am copying a dynamic range of cells from twelve different worksheets.
i need to select a range that starts of g10 on every sheet, but ends
differently on every sheet. the range ends depending on column A in the
last row of each range, the word 'total'. i would like to select the
range in column g10 down to the cell corresponding with 'total' in
column a in column g. For example, if there is 'total' is in a56, i
want to select and copy g10-g56. If on another sheet 'total' is in a68,
i want to select and copy g10-g68. there is information below the range.
i want to ignore the data below altogther. here is the code that i have
now for the first sheet. it grabs G10 through the last cell in column
g. any help would be appreciated. thanks.


Code:
--------------------
Sheets("January 2005").Select
Range(Range("G10"), Cells(Rows.Count, Columns("G").Column).End(xlUp)) _
Copy Worksheets("SALES REPORT").Range("a2")
--------------------