I built a macro which Inserts Copied Cells (about 20 rows or so each time its
used) at the TOP of a data table, right below the header row in columns A
through D and right above all the existing data already in the table. The
macro then appends formulae to the right of the inserted data in columns E
through G by using AutoFill and a hidden row above the data housing the
formulae that need to be copied below.

Here's the catch: It's essential that the macro only fills the BLANK CELLS
downward until it hits the formulae it inserted the last time it was run, NOT
the whole column of data.

To so this, I used the following code:

Range("E12"G12").AutoFill Destination:=Range("E12", Range("G12").End(xlDown)
(0))

On my computer it works fine: if I insert 12 rows, the macro copies the
hidden formulae down 12 rows, not overwriiting anything below it. However,
on my client's computer, the macro consistently overwrites the entire column.
Someone on this board suggested I delete the "(0)" at the end the code, but
that unfortunately did nothing.

I'm so desperate to solve this - if anyone has any thoughts or suggestions,
I'd be incredibly grateful!!! Are there possibly Tools/Option selections
that might cause this to happen? Or other things specific to a certain
computer?

Many, many, many thanks,

Marika :)

Different versions of Excel? Insert rows is done differently in Excel
2000 forward than it was in previous versions (I had a similar problem)

Actually, no....we both have Excel 2002....one is a company, one is for
student & teachers...

Thanks, nevertheless....and any more ideas would be incredibly appreciated!!!!

Marika :)



"Chip" wrote:

Different versions of Excel? Insert rows is done differently in Excel
2000 forward than it was in previous versions (I had a similar problem)

Is there already existing data in E13:G13 when this line runs on the problem
machine? In otherwords, is there already existing formulas in these columns
for the existing data?

If not, I would need to see the code that inserts the rows to see what you
are using to determine how many rows to insert.

Perhaps you can make the change yourself. Assume the variable "numrows"
holds the number of rows you inserted. then you could do


Range("E12"G12").AutoFill Destination:=Range("E12").Resize(numrows + 1,3)

The above should work in almost any situation.

--
Regards,
Tom Ogilvy





"marika1981" wrote in message
...
I built a macro which Inserts Copied Cells (about 20 rows or so each time
its
used) at the TOP of a data table, right below the header row in columns A
through D and right above all the existing data already in the table. The
macro then appends formulae to the right of the inserted data in columns E
through G by using AutoFill and a hidden row above the data housing the
formulae that need to be copied below.

Here's the catch: It's essential that the macro only fills the BLANK CELLS
downward until it hits the formulae it inserted the last time it was run,
NOT
the whole column of data.

To so this, I used the following code:

Range("E12"G12").AutoFill Destination:=Range("E12",
Range("G12").End(xlDown)
(0))

On my computer it works fine: if I insert 12 rows, the macro copies the
hidden formulae down 12 rows, not overwriiting anything below it.
However,
on my client's computer, the macro consistently overwrites the entire
column.
Someone on this board suggested I delete the "(0)" at the end the code,
but
that unfortunately did nothing.

I'm so desperate to solve this - if anyone has any thoughts or
suggestions,
I'd be incredibly grateful!!! Are there possibly Tools/Option selections
that might cause this to happen? Or other things specific to a certain
computer?

Many, many, many thanks,

Marika :)

Dear Tom:

My face lit up when I saw that you had read my email!! Thank you!

The issue is that each week, when the macro is run, there will be a
different number of rows inserted - thus the relative complexity. The macro
(below) inserts x number of rows copied from a source file, places them at
the top of the data table (above all pre-existing, formerly inserted data),
Autofills a set of formulae on the right next to the NEW rows and then Copies
and Pastes As Values the formulae output (basically referencing a week, month
and year value the user inputs before running the macro - thus adding a
three-column date stamp so the data can be found by specific conditions
entered later).

Here's the code (the new inserted data starts in row 13; the hidden formulae
are in row 12):

Range("A13").Select
Selection.Insert Shift:=xlDown
Range("A13").Select

Range("E12:G12").AutoFill Destination:=Range("E12",
Range("G12").End(xlDown)(0))

Range("E13:G13").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A13").Select

EVERYTHING works fine on my computer. The middle step - AUtofill - doesn't
work on my client's computer (he has Excel 2002).

THANK YOU!!!!

Marika :) :)


"Tom Ogilvy" wrote:

Is there already existing data in E13:G13 when this line runs on the problem
machine? In otherwords, is there already existing formulas in these columns
for the existing data?

If not, I would need to see the code that inserts the rows to see what you
are using to determine how many rows to insert.

Perhaps you can make the change yourself. Assume the variable "numrows"
holds the number of rows you inserted. then you could do


Range("E12"G12").AutoFill Destination:=Range("E12").Resize(numrows + 1,3)

The above should work in almost any situation.

--
Regards,
Tom Ogilvy





"marika1981" wrote in message
...
I built a macro which Inserts Copied Cells (about 20 rows or so each time
its
used) at the TOP of a data table, right below the header row in columns A
through D and right above all the existing data already in the table. The
macro then appends formulae to the right of the inserted data in columns E
through G by using AutoFill and a hidden row above the data housing the
formulae that need to be copied below.

Here's the catch: It's essential that the macro only fills the BLANK CELLS
downward until it hits the formulae it inserted the last time it was run,
NOT
the whole column of data.

To so this, I used the following code:

Range("E12"G12").AutoFill Destination:=Range("E12",
Range("G12").End(xlDown)
(0))

On my computer it works fine: if I insert 12 rows, the macro copies the
hidden formulae down 12 rows, not overwriiting anything below it.
However,
on my client's computer, the macro consistently overwrites the entire
column.
Someone on this board suggested I delete the "(0)" at the end the code,
but
that unfortunately did nothing.

I'm so desperate to solve this - if anyone has any thoughts or
suggestions,
I'd be incredibly grateful!!! Are there possibly Tools/Option selections
that might cause this to happen? Or other things specific to a certain
computer?

Many, many, many thanks,

Marika :)

Range("A13").Select
Selection.Insert Shift:=xlDown
Range("A13").Select

selects A13 and inserts a single cell.

there isn't anything I can do with that, because it obviously isn't the code
you use to insert up to 20 rows.

--
Regards,
Tom Ogilvy

"marika1981" wrote in message
...
Dear Tom:

My face lit up when I saw that you had read my email!! Thank you!

The issue is that each week, when the macro is run, there will be a
different number of rows inserted - thus the relative complexity. The
macro
(below) inserts x number of rows copied from a source file, places them at
the top of the data table (above all pre-existing, formerly inserted
data),
Autofills a set of formulae on the right next to the NEW rows and then
Copies
and Pastes As Values the formulae output (basically referencing a week,
month
and year value the user inputs before running the macro - thus adding a
three-column date stamp so the data can be found by specific conditions
entered later).

Here's the code (the new inserted data starts in row 13; the hidden
formulae
are in row 12):

Range("A13").Select
Selection.Insert Shift:=xlDown
Range("A13").Select

Range("E12:G12").AutoFill Destination:=Range("E12",
Range("G12").End(xlDown)(0))

Range("E13:G13").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A13").Select

EVERYTHING works fine on my computer. The middle step - AUtofill -
doesn't
work on my client's computer (he has Excel 2002).

THANK YOU!!!!

Marika :) :)


"Tom Ogilvy" wrote:

Is there already existing data in E13:G13 when this line runs on the
problem
machine? In otherwords, is there already existing formulas in these
columns
for the existing data?

If not, I would need to see the code that inserts the rows to see what
you
are using to determine how many rows to insert.

Perhaps you can make the change yourself. Assume the variable "numrows"
holds the number of rows you inserted. then you could do


Range("E12"G12").AutoFill Destination:=Range("E12").Resize(numrows +
1,3)

The above should work in almost any situation.

--
Regards,
Tom Ogilvy





"marika1981" wrote in message
...
I built a macro which Inserts Copied Cells (about 20 rows or so each
time
its
used) at the TOP of a data table, right below the header row in
columns A
through D and right above all the existing data already in the table.
The
macro then appends formulae to the right of the inserted data in
columns E
through G by using AutoFill and a hidden row above the data housing
the
formulae that need to be copied below.

Here's the catch: It's essential that the macro only fills the BLANK
CELLS
downward until it hits the formulae it inserted the last time it was
run,
NOT
the whole column of data.

To so this, I used the following code:

Range("E12"G12").AutoFill Destination:=Range("E12",
Range("G12").End(xlDown)
(0))

On my computer it works fine: if I insert 12 rows, the macro copies
the
hidden formulae down 12 rows, not overwriiting anything below it.
However,
on my client's computer, the macro consistently overwrites the entire
column.
Someone on this board suggested I delete the "(0)" at the end the
code,
but
that unfortunately did nothing.

I'm so desperate to solve this - if anyone has any thoughts or
suggestions,
I'd be incredibly grateful!!! Are there possibly Tools/Option
selections
that might cause this to happen? Or other things specific to a
certain
computer?

Many, many, many thanks,

Marika :)

Well...if nothing has been copied, yes it inserts a single cell. However, if
a range has been copied prior to running the macro, that code performs the
Insert Copied Cells function (this part of the macro was written simply by me
recording the function) and it definitely works on all computers I've tried
running the macro on.

Sounds like this might be a little too tough to solve over the internet.
Thanks again for your help - if you have any other ideas about the AutoFill
step of the macro, I'd be grateful. Otherwise, I'll figure something else
out.

Thanks again :)
Marika :)





"Tom Ogilvy" wrote:

Range("A13").Select
Selection.Insert Shift:=xlDown
Range("A13").Select

selects A13 and inserts a single cell.

there isn't anything I can do with that, because it obviously isn't the code
you use to insert up to 20 rows.

--
Regards,
Tom Ogilvy

"marika1981" wrote in message
...
Dear Tom:

My face lit up when I saw that you had read my email!! Thank you!

The issue is that each week, when the macro is run, there will be a
different number of rows inserted - thus the relative complexity. The
macro
(below) inserts x number of rows copied from a source file, places them at
the top of the data table (above all pre-existing, formerly inserted
data),
Autofills a set of formulae on the right next to the NEW rows and then
Copies
and Pastes As Values the formulae output (basically referencing a week,
month
and year value the user inputs before running the macro - thus adding a
three-column date stamp so the data can be found by specific conditions
entered later).

Here's the code (the new inserted data starts in row 13; the hidden
formulae
are in row 12):

Range("A13").Select
Selection.Insert Shift:=xlDown
Range("A13").Select

Range("E12:G12").AutoFill Destination:=Range("E12",
Range("G12").End(xlDown)(0))

Range("E13:G13").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A13").Select

EVERYTHING works fine on my computer. The middle step - AUtofill -
doesn't
work on my client's computer (he has Excel 2002).

THANK YOU!!!!

Marika :) :)


"Tom Ogilvy" wrote:

Is there already existing data in E13:G13 when this line runs on the
problem
machine? In otherwords, is there already existing formulas in these
columns
for the existing data?

If not, I would need to see the code that inserts the rows to see what
you
are using to determine how many rows to insert.

Perhaps you can make the change yourself. Assume the variable "numrows"
holds the number of rows you inserted. then you could do


Range("E12"G12").AutoFill Destination:=Range("E12").Resize(numrows +
1,3)

The above should work in almost any situation.

--
Regards,
Tom Ogilvy





"marika1981" wrote in message
...
I built a macro which Inserts Copied Cells (about 20 rows or so each
time
its
used) at the TOP of a data table, right below the header row in
columns A
through D and right above all the existing data already in the table.
The
macro then appends formulae to the right of the inserted data in
columns E
through G by using AutoFill and a hidden row above the data housing
the
formulae that need to be copied below.

Here's the catch: It's essential that the macro only fills the BLANK
CELLS
downward until it hits the formulae it inserted the last time it was
run,
NOT
the whole column of data.

To so this, I used the following code:

Range("E12"G12").AutoFill Destination:=Range("E12",
Range("G12").End(xlDown)
(0))

On my computer it works fine: if I insert 12 rows, the macro copies
the
hidden formulae down 12 rows, not overwriiting anything below it.
However,
on my client's computer, the macro consistently overwrites the entire
column.
Someone on this board suggested I delete the "(0)" at the end the
code,
but
that unfortunately did nothing.

I'm so desperate to solve this - if anyone has any thoughts or
suggestions,
I'd be incredibly grateful!!! Are there possibly Tools/Option
selections
that might cause this to happen? Or other things specific to a
certain
computer?

Many, many, many thanks,

Marika :)

Your choice.

--
Regards,
Tom Ogilvy

"marika1981" wrote in message
...
Well...if nothing has been copied, yes it inserts a single cell. However,
if
a range has been copied prior to running the macro, that code performs the
Insert Copied Cells function (this part of the macro was written simply by
me
recording the function) and it definitely works on all computers I've
tried
running the macro on.

Sounds like this might be a little too tough to solve over the internet.
Thanks again for your help - if you have any other ideas about the
AutoFill
step of the macro, I'd be grateful. Otherwise, I'll figure something else
out.

Thanks again :)
Marika :)





"Tom Ogilvy" wrote:

Range("A13").Select
Selection.Insert Shift:=xlDown
Range("A13").Select

selects A13 and inserts a single cell.

there isn't anything I can do with that, because it obviously isn't the
code
you use to insert up to 20 rows.

--
Regards,
Tom Ogilvy

"marika1981" wrote in message
...
Dear Tom:

My face lit up when I saw that you had read my email!! Thank you!

The issue is that each week, when the macro is run, there will be a
different number of rows inserted - thus the relative complexity. The
macro
(below) inserts x number of rows copied from a source file, places
them at
the top of the data table (above all pre-existing, formerly inserted
data),
Autofills a set of formulae on the right next to the NEW rows and then
Copies
and Pastes As Values the formulae output (basically referencing a
week,
month
and year value the user inputs before running the macro - thus adding
a
three-column date stamp so the data can be found by specific
conditions
entered later).

Here's the code (the new inserted data starts in row 13; the hidden
formulae
are in row 12):

Range("A13").Select
Selection.Insert Shift:=xlDown
Range("A13").Select

Range("E12:G12").AutoFill Destination:=Range("E12",
Range("G12").End(xlDown)(0))

Range("E13:G13").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A13").Select

EVERYTHING works fine on my computer. The middle step - AUtofill -
doesn't
work on my client's computer (he has Excel 2002).

THANK YOU!!!!

Marika :) :)


"Tom Ogilvy" wrote:

Is there already existing data in E13:G13 when this line runs on the
problem
machine? In otherwords, is there already existing formulas in these
columns
for the existing data?

If not, I would need to see the code that inserts the rows to see
what
you
are using to determine how many rows to insert.

Perhaps you can make the change yourself. Assume the variable
"numrows"
holds the number of rows you inserted. then you could do


Range("E12"G12").AutoFill Destination:=Range("E12").Resize(numrows +
1,3)

The above should work in almost any situation.

--
Regards,
Tom Ogilvy





"marika1981" wrote in message
...
I built a macro which Inserts Copied Cells (about 20 rows or so
each
time
its
used) at the TOP of a data table, right below the header row in
columns A
through D and right above all the existing data already in the
table.
The
macro then appends formulae to the right of the inserted data in
columns E
through G by using AutoFill and a hidden row above the data
housing
the
formulae that need to be copied below.

Here's the catch: It's essential that the macro only fills the
BLANK
CELLS
downward until it hits the formulae it inserted the last time it
was
run,
NOT
the whole column of data.

To so this, I used the following code:

Range("E12"G12").AutoFill Destination:=Range("E12",
Range("G12").End(xlDown)
(0))

On my computer it works fine: if I insert 12 rows, the macro
copies
the
hidden formulae down 12 rows, not overwriiting anything below it.
However,
on my client's computer, the macro consistently overwrites the
entire
column.
Someone on this board suggested I delete the "(0)" at the end the
code,
but
that unfortunately did nothing.

I'm so desperate to solve this - if anyone has any thoughts or
suggestions,
I'd be incredibly grateful!!! Are there possibly Tools/Option
selections
that might cause this to happen? Or other things specific to a
certain
computer?

Many, many, many thanks,

Marika :)