Hi all,
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:
Code
- 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....