Problems with AutoFill xlDown macro on certain computers - DESPERA

[Guest]

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

 

[Chip]

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

 

[Guest]

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

 

[Tom Ogilvy]

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.

 

[Guest]

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]

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.

 

[Guest]

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]

Your choice.

--
Regards,
Tom Ogilvy

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