Macro will work on one computer but not on another

[Gillian]

I have recently set up three Excel spreadsheets for formatting data imported
from a text file generated in a DOS program. Once the data is pasted into
the master spreadsheet it is renamed with a date and a set of two or three
macros that manipulate the data, inserting/hiding columns, adding up,
subtotals, formatting, etc.

The files are held on a shared drive on our network.

On my computer (using Excel 2000 on WinXP) each of the four files and their
macros work perfectly. On my colleague's computer (using the same version of
Excel and XP) only two of the macros will work. On the other two we get a
Debug message. When we look at the macro using F8 to step through we can see
that the only functions to work are those where text is typed in. For
example, the first few rows of the first macro are this:

Range("A1").Select
Selection.EntireRow.Insert
ActiveCell.FormulaR1C1 = "Supplier"
Range("B1").Select
ActiveCell.FormulaR1C1 = "SageRef"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Item"
Range("D1").Select

What happens, though, is:
No row is inserted
All the above text is written into A1 instead of moving to B1, C1, D1,
and so on.
So of course the macro soon fails altogether.

I am completely stumped!
Any suggestions very gratefully received.

 

[PCLIVE]

Not sure, but I would simplify your code a little.

Rows("1:1").Insert Shift:=xlDown
Range("A1").Value = "Supplier"
Range("B1").Value = "SageRef"
Range("C1").Value = "Item"
Range("D1").Select


HTH,
Paul

 

[Gillian]

Thanks,
I'll try that.
Incidentally, I didn't write the actual code - that's what Excel put in for
me when I did a simple 'Record Macro' and it recorded my keystrokes.

 

[bj]

when you step through it does it show B1,C1 etc being selected?
it sounds as though his computer may have a problem with "select"
do other macros work OK?

 

[Gillian]

The step through process shows that it refuses to budge out of cell A1.
But yes, other macros work perfectly well and they have a very similar set
of instructions except for the first 'insert row' bit.
Also, exactly the same errors happen on another pc in another office as well!
Could it be an MS update issue?

Gillian

 

[bj]

go to the computer which has the problem
record a macro for the first couple of steps and see what it says and if it
works.
if it works copy the selection and insert lines and paste into the old macro.
See if it works now?

 

[Gillian]

I have finally tracked down the problem, although I still don't understand
why it should happen on one pc but not on another. I was at the computer
where the error was happening and trying once more to step through the macro
before trying your suggestion.

At first I thought it was working as actually went through the first 20 or
so commands. It fell over at the section of macro shown below.

When I stopped the debugger and tried to run it again, that's when it
refused to move out of cell A1 as initially reported in this post and Excel
collapsed to the extent that I had to use the Task Manager to shut it down.

The guilty part of the macro is:

Columns("Y:Y").Select
Selection.Cut
Range("U1").Select
ActiveSheet.Paste

It failed at the paste option, with a run time error.

I just changed the macro to:

Columns("Y:Y").Select
Selection.Copy
Range("U1").Select
ActiveSheet.Paste

And everything works fine. Why? I don't know. But at least it works now.

Thanks for your suggestions.

 

[Gillian]

I have 'solved' this problem... see the post submitted today in response to bj.
Regards,
Gillian

 

[bj]

If you want column Y to be removed you might want to add

Columns("Y:Y").Select
Application.CutCopyMode = False
Selection.Clear

it is an oddity. I could not make your code fail on my computer 2003 XP

 

[Susan]

another thing to check, too (even though you've solved it)......
on YOUR computer, open vb editor & look at Tools, Options, and choose
the General tab.
to the (R) you'll see an "Error Trapping" frame. write down which
option your computer has selected. go check the other computers.
if they're not set the same, that may be why it errored out on theirs,
but not on yours. this is an option that's individual to each
computer and is not carried along by macros.
hth
susan

 

[bj]

what version of EXCEL dop you Have? 2003 Xl does not appear to have this option

 

[Peo Sjoblom]

Yes it does, press alt + F11, then do tools>options, select the general tab