Macro's strange behaviour

[eduardovieira]

Hello, until recently I had been using a macro like this and never have had a
problem. Now something has really gone crazy. The macro is this:
Sub ColarLink()
'
' Pastes links in the next spreadsheet
'
' Keyboard Shortcut: Ctrl+j
'
Selection.Copy
ActiveWindow.ActivateNext
ActiveSheet.Paste Link:=True

End Sub

Now. It works the first time. Then when I do it again it activates "trace
precedents and dependents" and doesn't paste the link properly.
I'm using this macro to paste cells from an xls spreadsheet to another xlsx
spreadsheet.

 

[grndprix]

Hello, until recently I had been using a macro like this and never have had a
problem. Now something has really gone crazy. The macro is this:
Sub ColarLink()
'
' Pastes links in the next spreadsheet
'
' Keyboard Shortcut: Ctrl+j
'
Selection.Copy
ActiveWindow.ActivateNext
ActiveSheet.Paste Link:=True

End Sub

Now. It works the first time. Then when I do it again it activates "trace
precedents and dependents" and doesn't paste the link properly.
I'm using this macro to paste cells from an xls spreadsheet to another xlsx
spreadsheet.

I have a similar problem with a macro performing nearly the same type of
operation. I am transposing selected cells from a CSV file to a worksheet.
Both files are open, and always have their own same names so that the
copy/paste ops can function easily. The macro is run daily, and may work for
weeks successfully. When it fails, it get "stuck" after the first copy/paste
op. This is obvious since the target file is displayed, with the "pasted"
cells still highlighted. All open Excel files are locked up at that point,
and VB simply shows the last step executed. If I restart Excel and step into
the macro, single steping through it, it'll complete successfully. If I
rerun it at that point, it'll run fine, again, until it fails the next time,
somewhere down the road. On the most recent failure, a run time error was
identified re an illegal subscript. There are no subscripts ! The entire
macro is really simple, nothing but cuts and pastes between the two
worksheets. ?????