I have some code that opens a file, if the file name contains X or Y, it does an X defined macro and a Y defined macro.
The issue I'm having, is how to pass the workbook name that I've defined between subroutines.
For instance my code is as follows:
Sub Openfilesatthestart() Dim wb1 As Workbook Dim wb2 As Workbook Dim Sh As Worksheet Dim sh2 As Worksheet Dim Sh3 As Worksheet Dim lastrow As Long Dim Sheet As Worksheet Dim PasteStart As Range Dim filetoopen As Variant Dim Title As String Dim filterindex As Integer Dim finfo As String ' Optimise the sheet Application.Calculation = xlCalculationManual Application.ScreenUpdating = False Application.DisplayStatusBar = False Application.EnableEvents = False ActiveSheet.DisplayPageBreaks = False ' Set our sheets Set Sh = ThisWorkbook.Sheets("Daily Volumes Apps") Set sh2 = ThisWorkbook.Sheets("Daily Volumes Setts") Set Sh3 = ThisWorkbook.Sheets("Daily Volumes Apprv") ' ** The below code will prompt you to open and a file and then it will copy sheet1 into the Daily Volumes Apps Sheet ' Set up our list of files finfo = "All Files (*.*),*.*" ' Title Title = "Please locate and open the latest Volumes Files" ' The below opens our file Set wb1 = ActiveWorkbook ' this is the workbook we are using Daily Volumes Apps filetoopen = Application.GetOpenFilename _ (Title:=Title, _ FileFilter:=finfo) ' the window we use to find the file If filetoopen = False Then MsgBox "No File Specified.", vbExclamation, "Cancelled" Exit Sub Else Set wb2 = Workbooks.Open(Filename:=filetoopen) End If If wb2.Name Like "*Opened*" Then MsgBox ("Month Opened") ElseIf wb2.Name Like "*Settled*" Then MsgBox ("Month Settled") ' clear all the sheets for new data 'sh2.Cells.Clear ' clear the Daily Volumes Apps sheet for new opened data ElseIf wb2.Name Like "*Approved*" Then MsgBox ("Month Approved") ElseIf wb2.Name Like "*Daily Volumes Details*" Then MsgBox ("Daily") Else: MsgBox ("Wrong File Selected") wb2.Close End 'stop all macros End If End Sub
Instead of the MSGboxs, I want the macro to take data from the file I open and copy and do further manipulation.
The problem is, I don't want to re-open the file again for each new case to get the workbook name (ie wb2 variable). I want to pass that variable name between subroutines. Confused with the syntax so far....