How to run Macro on selected rows

[CantGetRight]

Hi there,

First post on the site but frequent visitor but I can't seem to find a solution to the problem I'm facing.

I have a file with a macro that will open up files based on the contents of column A. The purpose of this is so that all of my indirect formulas referencing these files don't produce a ref error. Once this macro runs these errors turn into values and the files are closed. At which point I copy and paste as values.

My request is that this code be altered to allow it to only open the files of the selected rows. Below is a sample of the data


A
1 File Name
2 Test Name1
3 Test Name2
4 Test Name3




Sub OpenWorkBooksandRefreshFormulasInitial()

'
Application.ScreenUpdating = False
Application.DisplayAlerts = False

'safety prompt
Dim Sure As Integer
Sure = MsgBox("This macro will direct to the initials folder and open all files shown in column A, refresh formulas and close them. Please paste values afterwards as any change made on this file afterwards will cause the formulas to return an error. If this happens run the macro again.", vbYesNo)
If Sure = vbYes Then

Set MasterWB = ThisWorkbook

Dim filename As String


On Error Resume Next

For Each r In Range("A2", Range("A" & Rows.Count).End(xlUp))


On Error Resume Next

Workbooks.Open filename:= _
"\\Madeupfilepath\" & r.Value & ".xlsm", Password:="madeuppassword", UpdateLinks:=0

Next


MasterWB.Activate

On Error Resume Next

Calculate

On Error Resume Next

Dim xWB As Workbook

On Error Resume Next

For Each xWB In Application.Workbooks
If Not (xWB Is Application.ActiveWorkbook) Then
xWB.Close savechanges:=False
End If
Next


Application.DisplayAlerts = True
Application.ScreenUpdating = True

End If

Call CloseAllOtherWBs

End Sub

 

[CalcSux78]

....

Set MasterWB = ThisWorkbook
Dim filename As String
On Error Resume Next

[COLOR=#008000]'For Each r In Range("A2", Range("A" & Rows.Count).End(xlUp)) [/COLOR][COLOR=#ff0000]Delete this line[/COLOR][COLOR=#0000ff]
For Each r in Selection      [/COLOR][COLOR=#008000]'This will loop thru each cell within the active selection

[/COLOR]
On Error Resume Next[COLOR=#ff0000] 'you can remove this line... Error handling is already set to resume next
[/COLOR]
    Workbooks.Open filename:= _
        "\\Madeupfilepath\" & r.Value & ".xlsm", Password:="madeuppassword", UpdateLinks:=0

Next

....

Just modify your For loop to look at each cell within selection (Blue text within quote)

 

[CantGetRight]

Thanks very much CalcSux78. Not sure if I need to change this to resolved and give you credit somehow?

Best,
Mark