I am struggling to pass a
srcFile variable from one sub to another. I would like to do the following:
1) load source file from my local path in loadFile() sub
2) Set srcFile as my source data workbook
3) I would like to call processWorkbook in my Main program so that it will take my srcFile, pass it to the Sub and do some actions on each worksheet of that file.
Apologies if I did not express myself clearly.
Sub Main() Call loadFile Call processWorkbook(srcFile) End Sub ' _____________________________________________________ Sub loadFile() Dim wrk As Worksheet Dim trg As Worksheet Dim Path As String Dim srcFile As Workbook Set wrk = Workbooks("Banks.xlsm").Sheets("Control") Set trg = Workbooks("Banks.xlsm").Sheets("Output") trg.Cells.ClearContents Path = wrk.Cells(1, 2).Value 'file path Set srcFile = Workbooks.Open(Path, ReadOnly:=False) End Sub ' _____________________________________________________ Sub processWorkbook(wrk) Dim sht As Worksheet For Each sht In wrk.Sheets Call anotherSub Next sht End Sub