How do I pass a workbook reference between modules?

M

Mr Denove

Active Member
Joined
Jun 8, 2007
Messages
446
I have a Control code which calls both of these modules, however I need to keep wb2 as a particular workbook.

Can anyone please provide the correct code (syntax) as to how I pass the wb2 from my code into the next module?
The workbook names will change every week hence the need for a flexible code.

Code: 
Sub OPEN_BOTH_FILES()
    Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook
    Dim Ret2, Ret3
    Dim LastRow As Long
    Dim LastRowX As Long
    Dim LastRowY As Long
    Dim LastRowZ As Long
    Dim FileName As Variant
    
Application.ScreenUpdating = False
     
Application.DisplayAlerts = False

Application.DisplayStatusBar = False

ActiveSheet.DisplayPageBreaks = False

Application.CutCopyMode = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False
 
    Set wb1 = ThisWorkbook
    
     
    '**************************************************************************************
    'OPEN THE MI DATA FILE wb2,Ret2
    '**************************************************************************************
    
        ChDir "\\Intranet.barcapint.com\dfs-emea\BWGroup\BWData\Kyc\FATCA\BAU\MI\"
    Ret2 = Application.GetOpenFilename("Excel Files (*.xlsx*), *.xlsx*", _
    , "Please select file")
    If Ret2 = False Then End
    
    Dim strPath As String
    strPath2 = Ret2

    Dim strFile As String
    strFile = Right(strPath2, Len(strPath2) - InStrRev(strPath2, "\"))
    FileName = CreateObject("Scripting.FileSystemObject").GetBaseName(strFile)
    
    Set wb2 = Workbooks.Open(Ret2, Local:=True)
    
    
    MsgBox "Now Open Source File"
   '**************************************************************************************
    'OPEN THE SOURCE DATA FILE wb3,Ret3
    '**************************************************************************************
    
        ChDir "\\Intranet.barcapint.com\dfs-emea\BWGroup\BWData\Kyc\FATCA\BAU\MI\"
    Ret3 = Application.GetOpenFilename("Excel Files (*.csv*), *.csv*", _
    , "Please select file")
    If Ret3 = False Then End
    
    Set wb3 = Workbooks.Open(Ret3, Local:=True, ReadOnly:=True)
    
    DataFile = FileName & ".xslx"
    
    
With wb3
     wsName = wb1.Sheets("Previous MI").Range("A188")
     ws1name = wsName & " Full extract"
End With

Set DataFile = wb2
With DataFile
     
      LastRowX = DataFile.Sheets("Extract").Range("A" & _
          DataFile.Sheets("Extract").Rows.Count).End(xlUp).Row
     
     LastRowY = DataFile.Sheets("Post-May").Range("A" & _
          DataFile.Sheets("Post-May").Rows.Count).End(xlUp).Row

     DataFile.Sheets("Post-May").Range("A2:BU" & LastRowX).ClearContents
     DataFile.Sheets("Extract").Range("A2:BU" & LastRowX).ClearContents
     LastRowZ = wb3.Sheets(ws1name).Range("A" & _
          wb3.Sheets(ws1name).Rows.Count).End(xlUp).Row
     
     wb3.Sheets(ws1name).Range("A1:BU" & LastRowZ).Copy Destination:=DataFile.Sheets("Extract").Range("A1")
End With
    
    wb3.Close SaveChanges:=False
    
'************************************************************************
' Extract Sheet - Add Formula
'************************************************************************
With DataFile
    
    wb2.Sheets("Extract").Range("BT1").Value = "Reportable?"
    wb2.Sheets("Extract").Range("BU1").Value = "Source System"
    
    wb2.Sheets("Extract").Range("BT2:BT" & LastRowZ).Formula = "=IF(ISBLANK(BH2),""No Status"",VLOOKUP(BH2,Reportable!$1:$52,2,0))"
    wb2.Sheets("Extract").Range("BU2:BU" & LastRowZ).Formula = "=IF(MID(B2,FIND("":"",B2)+1,3)=""T12"",""ADIR"",IF(MID(B2,FIND("":"",B2)+1,3)=""T13"",""TS2"",IF(MID(B2,FIND("":"",B2)+1,3)=""59-"",""Wealthmaster"",IF(MID(B2,FIND("":"",B2)+1,3)=""46-"",""WECCO or Scars"",IF(MID(B2,FIND("":"",B2)+1,3)=""26-"",""Avaloq"",IF(MID(B2,FIND("":"",B2)+1,3)=""40-"",""OSCARS"",IF(MID(B2,FIND("":"",B2)+1,3)=""43-"",""Sharedeal"",IF(MID(B2,FIND("":"",B2)+1,3)=""65-"",""Paladign"",IF(MID(B2,FIND("":"",B2)+1,1)=""T"",""ADIR"",0)))))))))"
    wb2.Sheets("Extract").Range("BT2:BU" & LastRowZ).Copy
    wb2.Sheets("Extract").Range("BT2:BU" & LastRowZ).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
    
 Application.CutCopyMode = False
     
End With

With wb1
wb1.Sheets("Previous MI").Range("A220").Value = strFile
End With

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True
     
Application.DisplayAlerts = True

Application.DisplayStatusBar = True

ActiveSheet.DisplayPageBreaks = True

Application.CutCopyMode = True

Application.EnableEvents = True
    
    
End Sub

Code: 
Sub AdjustPtSourceA()

    Dim PT As PivotTable
    
    'This is where wb2 is required
     
    
    
    With wb2
        Sheets("Post-May").AutoFilterMode = False
        Set PT = Sheets("Pivot Post-May").PivotTables("PivotTable1")
        PT.PivotCache.SourceData = Sheets("Post-May").Range("A1").CurrentRegion.Address(, , xlR1C1, True)
    End With
    
    With wb2
        Set PT2 = Sheets("Pivot Post-May").PivotTables("PivotTable2")
        PT.PivotCache.SourceData = Sheets("Post-May").Range("A1").CurrentRegion.Address(, , xlR1C1, True)
    End With
    
    With wb2
        Set PT = Sheets("Extract").PivotTables("PivotTable1")
        PT.PivotCache.SourceData = Sheets("Pivot").Range("A1").CurrentRegion.Address(, , xlR1C1, True)
    End With

End Sub

as always, thanks in advance.
Stuart
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
It looks like it would make more sense for the control code to open and set wb2 and then pass it to both routines. Passing it only involves adding a parameter to the routine like this:

Code: 
Sub AdjustPtSourceA(ByVal wb2 as Excel.Workbook)

then the calling code simply uses either:

Code: 
AdjustPtSourceA wb2

or:

Code: 
Call AdjustPtSourceA(wb2)
 
Upvote 0
Hi Rory, good of you to reply.
To explain a bit further wb1 is the main control sheet, in module 1 it opens wb2 and wb3. These are declared in first module. It then imports the data from wb3 to wb2 in a different module.
I then need to manipulate the data in wb2 and then with formula I have import the data from wb2 into wb1.
Can you possibly show me in the code how I do the reference you suggest.
Thank you in advance.
Stuart
 
Upvote 0
Still not clear to me, I'm afraid. You said you have a control code that calls both of those codes. What is the control code?
 
Upvote 0
This is the main control code located in a module in wb1

Code: 
Sub Main_Code_For_Report()

Call OPEN_BOTH_FILES
Call Filtered_After
Call AdjustPtSourceA

End Sub
 
Upvote 0
I'd change the code as I mentioned to open the workbooks in the Main code routine - especially as you're using End currently if the files aren't opened. Something like this:

Code: 
Sub Main_Code_For_Report()
    Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook
    Dim Ret2, Ret3

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .DisplayStatusBar = False
        
        ActiveSheet.DisplayPageBreaks = False
        
        .CutCopyMode = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With

    Set wb1 = ThisWorkbook
    
     
    '**************************************************************************************
    'OPEN THE MI DATA FILE wb2,Ret2
    '**************************************************************************************
    
        ChDir "\\Intranet.barcapint.com\dfs-emea\BWGroup\BWData\Kyc\FATCA\BAU\MI\"
    Ret2 = Application.GetOpenFilename("Excel Files (*.xlsx*), *.xlsx*", _
    , "Please select file")
    If Ret2 = False Then Exit Sub
    
    strPath2 = Ret2

    Dim strFile As String
    strFile = Right(Ret2, Len(Ret2) - InStrRev(Ret2, "\"))
    FileName = CreateObject("Scripting.FileSystemObject").GetBaseName(strFile)
    
    Set wb2 = Workbooks.Open(Ret2, Local:=True)
    
    
    MsgBox "Now Open Source File"
   '**************************************************************************************
    'OPEN THE SOURCE DATA FILE wb3,Ret3
    '**************************************************************************************
    
        ChDir "\\Intranet.barcapint.com\dfs-emea\BWGroup\BWData\Kyc\FATCA\BAU\MI\"
    Ret3 = Application.GetOpenFilename("Excel Files (*.csv*), *.csv*", _
    , "Please select file")
    If Ret3 = False Then Exit Sub
    
    Set wb3 = Workbooks.Open(Ret3, Local:=True, ReadOnly:=True)
    
    Call OPEN_BOTH_FILES(wb1, wb2, wb3)

    wb3.Close savechanges:=False
    
    With Application
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
        .DisplayStatusBar = True
        
        ActiveSheet.DisplayPageBreaks = True
        
        .CutCopyMode = True
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Call Filtered_After
    Call AdjustPtSourceA(wb2)

End Sub
Sub OPEN_BOTH_FILES(wb1 As Excel.Workbook, wb2 As Excel.Workbook, wb3 As Excel.Workbook)
    Dim LastRow As Long
    Dim LastRowX As Long
    Dim LastRowY As Long
    Dim LastRowZ As Long
    Dim FileName As Variant
    
 
With wb3
     wsName = wb1.Sheets("Previous MI").Range("A188")
     ws1name = wsName & " Full extract"
End With

Set DataFile = wb2
With DataFile
     
      LastRowX = .Sheets("Extract").Range("A" & _
          .Sheets("Extract").Rows.Count).End(xlUp).Row
     
     LastRowY = .Sheets("Post-May").Range("A" & _
          .Sheets("Post-May").Rows.Count).End(xlUp).Row

     .Sheets("Post-May").Range("A2:BU" & LastRowX).ClearContents
     .Sheets("Extract").Range("A2:BU" & LastRowX).ClearContents
     LastRowZ = wb3.Sheets(ws1name).Range("A" & _
    wb3.Sheets(ws1name).Rows.Count).End(xlUp).Row
     
     wb3.Sheets(ws1name).Range("A1:BU" & LastRowZ).Copy Destination:=DataFile.Sheets("Extract").Range("A1")
End With
        
'************************************************************************
' Extract Sheet - Add Formula
'************************************************************************
With wb2.Sheets("Extract")
    
    .Range("BT1").Value = "Reportable?"
    .Range("BU1").Value = "Source System"
    
    .Range("BT2:BT" & LastRowZ).Formula = "=IF(ISBLANK(BH2),""No Status"",VLOOKUP(BH2,Reportable!$1:$52,2,0))"
    .Range("BU2:BU" & LastRowZ).Formula = "=IF(MID(B2,FIND("":"",B2)+1,3)=""T12"",""ADIR"",IF(MID(B2,FIND("":"",B2)+1,3)=""T13"",""TS2"",IF(MID(B2,FIND("":"",B2)+1,3)=""59-"",""Wealthmaster"",IF(MID(B2,FIND("":"",B2)+1,3)=""46-"",""WECCO or Scars"",IF(MID(B2,FIND("":"",B2)+1,3)=""26-"",""Avaloq"",IF(MID(B2,FIND("":"",B2)+1,3)=""40-"",""OSCARS"",IF(MID(B2,FIND("":"",B2)+1,3)=""43-"",""Sharedeal"",IF(MID(B2,FIND("":"",B2)+1,3)=""65-"",""Paladign"",IF(MID(B2,FIND("":"",B2)+1,1)=""T"",""ADIR"",0)))))))))"
    .Range("BT2:BU" & LastRowZ).Copy
    .Range("BT2:BU" & LastRowZ).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                                                SkipBlanks:=False, Transpose:=False
    
 Application.CutCopyMode = False
     
End With
wb1.Sheets("Previous MI").Range("A220").Value = wb2.Name
    
End Sub

Sub AdjustPtSourceA(wb2 As Excel.Workbook)

    Dim PT As PivotTable
    
    With wb2.Sheets("Post-May")
        .AutoFilterMode = False
        Set PT = .PivotTables("PivotTable1")
        PT.PivotCache.SourceData = .Range("A1").CurrentRegion.Address(, , xlR1C1, True)
        Set PT = .PivotTables("PivotTable2")
        PT.PivotCache.SourceData = .Range("A1").CurrentRegion.Address(, , xlR1C1, True)
    End With
    
    With wb2
        Set PT = .Sheets("Extract").PivotTables("PivotTable1")
        PT.PivotCache.SourceData = .Sheets("Pivot").Range("A1").CurrentRegion.Address(, , xlR1C1, True)
    End With

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Copying and pasting between two workbooks
Replies
1
Views
698
rlv01
R
E
  • Question Question
Filtering values from data source and pasting it into a second workbook
2
Replies
17
Views
1K
kevin9999
K
L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
594
RoryA
RoryA
Z
  • Question Question
VBA code to create a new workbook from multiple sheets of current workbook
Replies
9
Views
4K
Micron
M
S
  • Question Question
Copy range between open workbooks
Replies
4
Views
648
Shadkng
S

Forum statistics

Threads
1,224,599
Messages
6,179,831
Members
452,946
Latest member
JoseDavid

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top