Gary,
Thank you so much. That code worked perfect for me.

Kim



"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
kim:

see if this helps. i used a1:a50 just as an arbitrary range, you can
adjust. i search for the word total and set the row to that row -1

Option Explicit

Sub test()
Dim rng As Range
Dim lastrow As Long
Dim c As String
On Error GoTo xit
Application.ScreenUpdating = False

With Worksheets("sheet1").Range("A1:A50")
lastrow = .Find(What:="Total", LookIn:=xlValues, lookat:=xlPart).Row - 1

.Range("A2:g" & lastrow).Copy
' rest of your code here

End With

xit:
Application.ScreenUpdating = True
End Sub


--


Gary


"Kim" wrote in message
...
I think this could do it. The row after the last row in the range always
has the same value in the "G column." That value in that cell is "TOTAL"
If we can set the macro to copy the range (A2:lastrow), where the last row
equals the cell one row up from the row that contains the "TOTAL" value.
Any ideas on how to write this?

Thanks Again






"Nigel" wrote in message
...
If the first reference is always row 20 then unless you can ensure that
the last row is the last entry on the sheet to copy, you must track the
addition (and possibly the deletion) of rows in between. You could set
up a control value that is changed whenever a row is added or deleted
then use that to determine the range. Can you say if the data you want
to copy is always the last row on the sheet or not?

--
Cheers
Nigel



"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
is there something consistent with a20? i don't know how to tell if
somebody inserts rows if row 20 isn't the last row of data or there is
something that can always be checked for in the last row of the data
you want to copy. maybe someone else does.

--


Gary


"Kim" wrote in message
...
Hi Gary,
Here is what I am doing. At my accounting job we use a program called
"automate" that takes an excel document that contains an accounting
journal entry and keys that entry into our general ledger system. The
problem is that the template has to be formatted a certain way, so I
am writing am macro that will take a journal entry in excel and
convert it over to a new workbook that the automate program can read.
So let's say that the a workbook has 3 sheets, each one containing a
journal entry. Let's say that the actual entry on each sheet is on
"A2:G20." If a user needs an extra row to add another line entry,
then he or she will insert the row somewhere between A2:G20. Column A
contains the description, columns D-G contains the G/L account, debits
and credits. Does this make any sense? Let me know if you need more
info.

Thanks
Kim



"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
hi kimberly:

don't have enough info so i'll make a stab at it. i am assuming the
g20, and later g22 is the last row of data on sheet1.

so use these:

dim LastRow as long
LastRow = Worksheets("Sheet1").Cells(Rows.Count, "G").End(xlUp).Row
range("A2:G" & Lastrow).copy

give this a try

--


Gary


"Kim" wrote in message
...
Hi everybody,
My name is Kimberly and here is what I am trying to do. I am trying
to write a macro that will take a range (let's say A2:G20) and copy
it to a new workbook. I know how to write a macro on how to do
that, but here is my situation. Is there a way to have the macro
copy the activesheet range of A2:G20, but if a user inserts a row or
rows inbetween A2:G20, that the macro adjusts itself to pick up the
additional rows that are added (ex., if two rows are added, the the
macro copies A2:G22)?


Thanks Again

Kimberly