How to use the cell selected by an inputbox and use it to loop through all files/workbooks

[patrickpperron]

Hello,

I am attempting to figure out how to prompt the user to type in the cell label he/she wants from an inputbox and then automatically use that same cell address in all the files I want to loop through.

I want to prompt the user only once, and not every time the code loops through each file. I would want this code to loop through any number of files. These files are: Prio001.xlsx, Prio002.xlsx, Prio003.xlsx, etc....

Hence "Prio*.xlsx"

I am writing this code in a masterfile named "master.xlsm".

I was wondering if I could use a For Each loop; any help would be greatly appreciated. I hope this is enough to give you a feel of what I'm trying to achieve here.


sub looptest()

Dim FNum As Long
Dim FilesInPath As String
Dim MyPath As String


MyPath = ActiveWorkbook.Path


If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If


FilesInPath = Dir(MyPath & "Prio*.xl*")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If


Set Hardness = Application.InputBox(prompt:="Select a cell...", Title:="Select a Cell from All Prio Files for Hardness", Default:=Selection.Address(2, 1), Type:=8)

For Each FNum In FilesInPath

FNum.Activate
FNum.Range(Target.Address).Select


Next FNum
End Sub

 

[Fluff]

Hiya
UNTESTED, but I think it's the sort of thing you're after

Sub looptest()

    Dim FNum As Long
    Dim FilesInPath As String
    Dim MyPath As String
    Dim FilesInPath
    Dim Hardness As Range


    MyPath = ActiveWorkbook.Path

    If Right(MyPath, 1) <> "\" Then
        MyPath = MyPath & "\"
    End If

    FilesInPath = Dir(MyPath & "Prio*.xl*")
    If FilesInPath = "" Then
        MsgBox "No files found"
        Exit Sub
    End If

    Set Hardness = Application.InputBox(prompt:="Select a cell...", Title:="Select a Cell from All Prio Files for Hardness", Default:=Selection.Address(2, 1), Type:=8)

    Do While FilesInPath <> ""
        Workbooks.Open MyPath & MyFile
        Hardness.Select
        .
        .
        .
        Workbooks(FilesInPath).Close Savechanges:=False
        FilesInPath = Dir
    Loop
    
End Sub