Select a workbook in Excel

[nberryman]

I have a macro that updates a sheet in a workbook. It works fine in the workbook but I need to send it to a friend who then needs to run it on a number of workbooks all with different names. I don't think her skills will allow me to talk her through the copy/paste process.

How can I select a workbook and then have the macro in my update workbook run on the newly opened workbook and not the one I will be sending out.

I keep getting an error.

Hope this is clear Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[SteveBausch]

Probably best to post the code. Whatever I say here is speculative. Really need to look at the code.

We would be looking for any "ThisWorkbook" references. Those need to go. Replace them all with "ActiveWorkbook". Of course, others may have other ideas, but this is what I am suggesting.

Put the code in an empty workbook, mail it to her.
She would have the target book active, go to Tools-Macros. She would run the macro from that dialog. Shouldn't be too daunting, the Macros dialog is a user-oriented dialog.

Like I said, post the code. We will take it from there.

Steve

 

[nberryman]

The code is below, as I say it works if I copy and paste the code into the workbook I want to change but if I run it from another workbook I get a Subscript out of range error

Thanks for your help



Sub Update_Summary()
'
' Update_Summary Macro
'
Sheets("Summary 03&quot.Select
ActiveSheet.Unprotect ("Louise&quot

'
Range("D3&quot.Select
ActiveCell.FormulaR1C1 = "=SUM(April:March!R[8]C[33])"
Range("E3&quot.Select
ActiveCell.FormulaR1C1 = "=SUM(April:March!R[8]C[33])"
Range("F3&quot.Select
ActiveCell.FormulaR1C1 = "=SUM(April:March!R[8]C[35])"
Range("F3&quot.Select
Selection.AutoFill Destination:=Range("F3:M3&quot, Type:=xlFillDefault
Range("F3:M3&quot.Select
Range("B3:M3&quot.Select
ActiveWindow.SmallScroll ToRight:=3
Selection.AutoFill Destination:=Range("B3:M100&quot, Type:=xlFillDefault
Range("B3:M100&quot.Select
Range("A1&quot.Select

ActiveSheet.Protect ("Louise&quot


MsgBox ("Summary Sheet Updated&quot


End Sub
Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[SteveBausch]

Sub Update_Summary()
'
' Update_Summary Macro
'
You may want to have her manually unprotect the sheet, and manually ensure the correct sheet is selected
If you do that, you can remove/comment the next two lines
Sheets("Summary 03&quot.Select Does this exist?
ActiveSheet.Unprotect ("Louise&quotIs this the correct password?

'
Range("D3&quot.Select
ActiveCell.FormulaR1C1 = "=SUM(April:March!R[8]C[33])"
Range("E3&quot.Select
ActiveCell.FormulaR1C1 = "=SUM(April:March!R[8]C[33])"
Range("F3&quot.Select
ActiveCell.FormulaR1C1 = "=SUM(April:March!R[8]C[35])"
Range("F3&quot.Select
Selection.AutoFill Destination:=Range("F3:M3&quot, Type:=xlFillDefault
Range("F3:M3&quot.Select
Range("B3:M3&quot.Select
ActiveWindow.SmallScroll ToRight:=3
Selection.AutoFill Destination:=Range("B3:M100&quot, Type:=xlFillDefault
Range("B3:M100&quot.Select
Range("A1&quot.Select

Get rid of this, if the other two lines were deleted/commented out
ActiveSheet.Protect ("Louise&quot Is this the correct password?


MsgBox ("Summary Sheet Updated&quot


End Sub

 

[nberryman]

I think I will go with the manual unprotect as you say

Many thanks

Neil Berryman
IT Trainer
neil_berryman@btopenworld.com