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
Display More
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....