How to run Macro on selected rows

CantGetRight

New Member
Joined
Jul 21, 2015
Messages
19
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

CalcSux78

Well-known Member
Joined
Oct 15, 2013
Messages
1,120
Code:
....

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

New Member
Joined
Jul 21, 2015
Messages
19
Thanks very much CalcSux78. Not sure if I need to change this to resolved and give you credit somehow?

Best,
Mark
 

Watch MrExcel Video

Forum statistics

Threads
1,123,295
Messages
5,600,789
Members
414,405
Latest member
Zaurb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top