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