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

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

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

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

Hi Kim,

If I understand correctly, your problem has to deal with dynamic ranges
....
If you were to Insert Name define your range dynamically, your users
could keep on inserting as many as needed, your range would be adjusted
accordingly ...
By the way, a dymanic range would defined with a formula structured as
follows :
=Offset(Sheet1!$A$2,0,0,CountA(Sheet1!$A:$A),7)
for a range located in Sheet1, starting in A2, for a changing number of
lines, spreading on 7 columns ... i.e to column G

HTH
Cheers
Carim

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

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

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

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

Hi Kimberley,

Just a suggestion: - You could create a defined name for the range of
source data. If users add or delete rows between the first and last row, it
adjusts itself. To handle users adding rows after the last row, make the
definition dynamic.

You'll find more info he

http://www.contextures.com/xlNames01.html#Dynamic

http://www.cpearson.com/excel/excelF.htm#DynamicRanges

HTH
Regards,
GS

Hi GS,

I thought about adding a "name," but the problem is that this macro could be
run on several worksheets within the same workbook, so I don't know how the
naming scheme would work in that situation without adjusting the macro for
each sheet.

Thanks
Kim



"GS" wrote in message
...
Hi Kimberley,

Just a suggestion: - You could create a defined name for the range of
source data. If users add or delete rows between the first and last row,
it
adjusts itself. To handle users adding rows after the last row, make the
definition dynamic.

You'll find more info he

http://www.contextures.com/xlNames01.html#Dynamic

http://www.cpearson.com/excel/excelF.htm#DynamicRanges

HTH
Regards,
GS

Hi Kim,

Names can be local (sheet level) or global (workbook level). If you use
names, they should almost always be local so they travel with the sheet when
copying to other workbooks, ..without conflicts. For example, you can use the
same name on as many sheets as you want within any single workbook without
conflict because the name is "proprietary" to the sheet it's defined on.

Here's how that works:

For each sheet that contains a similar range of data, define a local name
for the range like this:

'Sheet Name'!RangeName

If the base range is A2:G20, select it before defining the name.

Note the sheetname is wrapped in an apostrophe. This handles spaces and
other characters that are "legal" to use in names.

Note also, the exclamation character follows the sheetname, and tells Excel
the rangename is coming next.

The rangename here could also be written as Range_Name, or any other format
that contains no spaces. (not the same as allowed spaces in the sheetname)

Since the macro copies the same range (no matter what size it is) from each
sheet, a qualified reference to the sheet is all that's required. For
example, you mentioned in your post: "that the template has to be formatted
a certain way". This is typical of all accounting software apps that import
data from Excel spreadsheets. The key here is this, -Is your template a real
template (as in ".xlt") or just a sheet you copy when you need to perform
this task? Could it be set up as a template so every copy has the same
format, named ranges, and structure? -Yes it can! Then your macro will work
with any 'copy' of this sheet you run it on.

If it needs to be the active sheet then just select that sheet before
running the macro.

If you're looping through a number of sheets then just qualify a reference
to each sheet before running the macro on it. (loop structures do this by
default)

In the case of people inserting/deleting rows between A2 and G20, the named
range will adjust itself to include them.

In the case of adding rows after the last row, a named dynamic range would
be better. Of course this creates a problem with the SUM() formula for your
totals, but that can be prevented by using a named relative range as follows:

Assumes formula in C21 is =SUM(C2:C20)

Select any cell that has a total in it. Say C21
Define a local name like 'SheetName'!LastCell
In the RefersTo box enter =C20
Click "Add", "OK".

This creates a relative reference to the cell above the one containing the
formula the name is used in. To use it, change the formula in C21 to
=SUM(C2:LastCell). Now, you can insert rows above the totals row and you
formula adjusts to include them.

The links I posted will help you with creating/using dynamic ranges. If you
would like me to look at your template file or 'structure' it for you, post
back and I'll give you my emailing info. Try to keep the file as close to
"actual" scenario as possible. If the file contains macros, you'll need to
zip it to get through the firewall.

HTH
Regards,
Garry

GS,
That would be great. My e-mail is

Thanks

Kim

"GS" wrote in message
...
Hi Kim,

Names can be local (sheet level) or global (workbook level). If you use
names, they should almost always be local so they travel with the sheet
when
copying to other workbooks, ..without conflicts. For example, you can use
the
same name on as many sheets as you want within any single workbook without
conflict because the name is "proprietary" to the sheet it's defined on.

Here's how that works:

For each sheet that contains a similar range of data, define a local name
for the range like this:

'Sheet Name'!RangeName

If the base range is A2:G20, select it before defining the name.

Note the sheetname is wrapped in an apostrophe. This handles spaces and
other characters that are "legal" to use in names.

Note also, the exclamation character follows the sheetname, and tells
Excel
the rangename is coming next.

The rangename here could also be written as Range_Name, or any other
format
that contains no spaces. (not the same as allowed spaces in the sheetname)

Since the macro copies the same range (no matter what size it is) from
each
sheet, a qualified reference to the sheet is all that's required. For
example, you mentioned in your post: "that the template has to be
formatted
a certain way". This is typical of all accounting software apps that
import
data from Excel spreadsheets. The key here is this, -Is your template a
real
template (as in ".xlt") or just a sheet you copy when you need to perform
this task? Could it be set up as a template so every copy has the same
format, named ranges, and structure? -Yes it can! Then your macro will
work
with any 'copy' of this sheet you run it on.

If it needs to be the active sheet then just select that sheet before
running the macro.

If you're looping through a number of sheets then just qualify a reference
to each sheet before running the macro on it. (loop structures do this by
default)

In the case of people inserting/deleting rows between A2 and G20, the
named
range will adjust itself to include them.

In the case of adding rows after the last row, a named dynamic range would
be better. Of course this creates a problem with the SUM() formula for
your
totals, but that can be prevented by using a named relative range as
follows:

Assumes formula in C21 is =SUM(C2:C20)

Select any cell that has a total in it. Say C21
Define a local name like 'SheetName'!LastCell
In the RefersTo box enter =C20
Click "Add", "OK".

This creates a relative reference to the cell above the one containing the
formula the name is used in. To use it, change the formula in C21 to
=SUM(C2:LastCell). Now, you can insert rows above the totals row and you
formula adjusts to include them.

The links I posted will help you with creating/using dynamic ranges. If
you
would like me to look at your template file or 'structure' it for you,
post
back and I'll give you my emailing info. Try to keep the file as close to
"actual" scenario as possible. If the file contains macros, you'll need to
zip it to get through the firewall.

HTH
Regards,
Garry

I sent an email to the address you provided.

Regards,
GS

"Kim" wrote:

GS,
That would be great. My e-mail is

Thanks

Kim