Pause to select cell

[Lweiss]

I have a main spreadsheet that contains a macro that goes to 25 different
spreadsheet and copies information and paste to the main spreadsheet. I
would like to pause the macro after each paste to select the row for the next
spreadsheet. How can I do this?

 

[Jim Thomlinson]

There is no effective way to pause a macro as you have described it. Macro's
are like fireworks. Once the fuse is lit it executes all the way through.
There are a couple of options...

1. You could use a userform with a refedit control on it that the user will
need to use to fill in the cell selection.

2. You could use an inputbox designed to accept only ranges (probably the
best solution for you).

3. You could use event programming to catch a selection change and use that
to initiate your copy.

Sub test()
Dim lng As Long

For lng = 1 To 3
Application.InputBox("Please select a range.", Type:=8).Select
MsgBox Selection.Address
Next lng
End Sub

 

[Lweiss]

here is the macro

Windows("Weekly Modified Work Report Ariss.xls").Activate
Application.Goto Reference:="Data"
Selection.Copy
Windows("Weekly Modified Work Report.xls").Activate
ActiveSheet.Paste

I want to stop the macro here so that I can go to my main spreadsheet
"Weekly Modified Work Report" to select the cell to run the next macro:

Windows("Weekly Modified Work Report Camtac.xls").Activate
Application.Goto Reference:="Data"
Selection.Copy
Windows("Weekly Modified Work Report.xls").Activate
ActiveSheet.Paste

and so on for 20 spreadsheets.

What is the best way to do this?

 

[Jim Thomlinson]

How about this...

Sub test()

Workbooks("Weekly Modified Work Report Ariss.xls").Names("Data"). _
RefersToRange.Copy Destination:= _
Application.InputBox("Please select a range.", Type:=8)

Workbooks("Weekly Modified Work Report Camtac.xls").Names("Data"). _
RefersToRange.Copy Destination:= _
Application.InputBox("Please select a range.", Type:=8)

End Sub