Copy Range with Additional Rows to New Workbook

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" <Kim@hr.com> wrote in message
news:%2320qJ3vTGHA.2656@TK2MSFTNGP10.phx.gbl...
> 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
news:uw3gCCwTGHA.1204@TK2MSFTNGP12.phx.gbl...
> 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" <Kim@hr.com> wrote in message
> news:%2320qJ3vTGHA.2656@TK2MSFTNGP10.phx.gbl...
>> 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" <Kim@hr.com> wrote in message
news:emKdqJwTGHA.2444@TK2MSFTNGP14.phx.gbl...
> 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
> news:uw3gCCwTGHA.1204@TK2MSFTNGP12.phx.gbl...
>> 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" <Kim@hr.com> wrote in message
>> news:%2320qJ3vTGHA.2656@TK2MSFTNGP10.phx.gbl...
>>> 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
>>>
>>
>>
>
>

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
news:eQzvVSwTGHA.5172@TK2MSFTNGP12.phx.gbl...
> 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" <Kim@hr.com> wrote in message
> news:emKdqJwTGHA.2444@TK2MSFTNGP14.phx.gbl...
>> 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
>> news:uw3gCCwTGHA.1204@TK2MSFTNGP12.phx.gbl...
>>> 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" <Kim@hr.com> wrote in message
>>> news:%2320qJ3vTGHA.2656@TK2MSFTNGP10.phx.gbl...
>>>> 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" <nigel-sw@suxnospampanet.com> wrote in message
news:Onx$XPyTGHA.5108@TK2MSFTNGP09.phx.gbl...
> 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
> news:eQzvVSwTGHA.5172@TK2MSFTNGP12.phx.gbl...
>> 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" <Kim@hr.com> wrote in message
>> news:emKdqJwTGHA.2444@TK2MSFTNGP14.phx.gbl...
>>> 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
>>> news:uw3gCCwTGHA.1204@TK2MSFTNGP12.phx.gbl...
>>>> 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" <Kim@hr.com> wrote in message
>>>> news:%2320qJ3vTGHA.2656@TK2MSFTNGP10.phx.gbl...
>>>>> 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" <Kim@hr.com> wrote in message
news:uqPQt95TGHA.4492@TK2MSFTNGP09.phx.gbl...
>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" <nigel-sw@suxnospampanet.com> wrote in message
> news:Onx$XPyTGHA.5108@TK2MSFTNGP09.phx.gbl...
>> 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
>> news:eQzvVSwTGHA.5172@TK2MSFTNGP12.phx.gbl...
>>> 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" <Kim@hr.com> wrote in message
>>> news:emKdqJwTGHA.2444@TK2MSFTNGP14.phx.gbl...
>>>> 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
>>>> news:uw3gCCwTGHA.1204@TK2MSFTNGP12.phx.gbl...
>>>>> 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" <Kim@hr.com> wrote in message
>>>>> news:%2320qJ3vTGHA.2656@TK2MSFTNGP10.phx.gbl...
>>>>>> 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
news:OBh7xk7TGHA.5108@TK2MSFTNGP09.phx.gbl...
> 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" <Kim@hr.com> wrote in message
> news:uqPQt95TGHA.4492@TK2MSFTNGP09.phx.gbl...
>>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" <nigel-sw@suxnospampanet.com> wrote in message
>> news:Onx$XPyTGHA.5108@TK2MSFTNGP09.phx.gbl...
>>> 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
>>> news:eQzvVSwTGHA.5172@TK2MSFTNGP12.phx.gbl...
>>>> 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" <Kim@hr.com> wrote in message
>>>> news:emKdqJwTGHA.2444@TK2MSFTNGP14.phx.gbl...
>>>>> 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
>>>>> news:uw3gCCwTGHA.1204@TK2MSFTNGP12.phx.gbl...
>>>>>> 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" <Kim@hr.com> wrote in message
>>>>>> news:%2320qJ3vTGHA.2656@TK2MSFTNGP10.phx.gbl...
>>>>>>> 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 here:

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" <GS@discussions.microsoft.com> wrote in message
news:BBFE58C0-B9B0-4987-A558-7DDBE39122ED@microsoft.com...
> 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 here:
>
> 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 kh5264@gmail.com

Thanks

Kim

"GS" <GS@discussions.microsoft.com> wrote in message
news:1A67BD16-5B7D-4068-B453-B908AA3BBCA5@microsoft.com...
> 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 kh5264@gmail.com
>
> Thanks
>
> Kim