Trying to set cursor using VBA

[hatzonick]

Newby here. Please be gentle! When I run my excel macro and keep the cursor in the workbook that the macro is running in, the macro is able to set the cursor position to where I need it. I use:

Workbooks("TwsDdeNick_prod.xlsm").Worksheets("BasicOrders").Range("A10").Activate

Now, when I run this macro and then move the cursor to another workbook before the macro is finished I get the following message when the macro is attempting to move the cursor in the workbook where the macro was run:

Run-time error ‘1004’:
Activate method of Range class failed

How can I be able to move my cursor freely between workbooks but have the macro operate independently of what workbook I’m currently in? Thanks so much in advance.

 

[Teeroy]

Hi and welcome to the forum.

It can be tricky to go to a cell in another Workbook using Activate; it is better to us Goto. Try;

Application.Goto (Workbooks("TwsDdeNick_prod.xlsm").Worksheets("BasicOrders").Range("A10"))

The workbook "TwsDdeNick_prod.xlsm" must be open for this to work.

 

[hatzonick]

Hi and welcome to the forum.

It can be tricky to go to a cell in another Workbook using Activate; it is better to us Goto. Try;

Application.Goto (Workbooks("TwsDdeNick_prod.xlsm").Worksheets("BasicOrders").Range("A10"))

The workbook "TwsDdeNick_prod.xlsm" must be open for this to work.

It is open along with other workbooks in the same instance of excel.

 

[Teeroy]

So did the Goto line work for you?

 

[hatzonick]

Looks like it works! Thank you very much!

 

[Teeroy]

You're welcome.