[SOLVED] Problems with AutoFill xlDown macro on certain computers - DESPERA

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >