VBA Using variable workbook and worksheet in a different sub

[Zoolou]

Here is the code I have right now

Public ReportFile as Workbook
Public ReportWorksheet as Worksheet
Sub VBA_Project()


Define_File
Define_Tab
Data_Cleanup


End Sub


Sub Define_File()


Dim ReportFilePath As Variant

ReportFilePath = Application.GetOpenFilename(filefilter:="excel Files,*.xl*;*xm*")

If ReportFilePath <> False Then
Workbooks.Open Filename:=ReportFilePath
End If

Dim ReportFile As Workbook
Set ReportFile = ActiveWorkbook

End Sub

sub define_tab()

'code to get to the desired worksheet'

Dim DataWorksheet As Worksheet
Set DataWorksheet = ActiveSheet


end sub

Sub Data_Cleanup()

ReportFile.Activate
DataWorksheet.select

'some more code'

end sub


I have tested the variables in the subs they are defined in and they worked perfectly fine. I am trying to run the macro using the first sub that references the others.

 

[AlphaFrog]

Don't declare the variables twice; both as public and within each procedure.

Just declare them once as public and that's it.

[color=darkblue]Public[/color] ReportFile [color=darkblue]As[/color] Workbook
[color=darkblue]Public[/color] DataWorksheet [color=darkblue]As[/color] Worksheet


[color=darkblue]Sub[/color] VBA_Project()
    
    Define_File
    define_tab
    Data_Cleanup
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]




[color=darkblue]Sub[/color] Define_File()
    
    [color=darkblue]Dim[/color] ReportFilePath [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    
    ReportFilePath = Application.GetOpenFilename(filefilter:="excel Files,*.xl*;*xm*")
    
    [color=darkblue]If[/color] ReportFilePath <> [color=darkblue]False[/color] [color=darkblue]Then[/color]
        [color=darkblue]Set[/color] ReportFile = Workbooks.Open(Filename:=ReportFilePath)
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]


[color=darkblue]Sub[/color] define_tab()
[color=green]'code to get to the desired worksheet'[/color]
    [color=darkblue]Set[/color] DataWorksheet = ActiveSheet
    
[color=darkblue]End[/color] Sub


Sub Data_Cleanup()
    
    ReportFile.Activate
    DataWorksheet.Select
    
    [color=green]'some more code'[/color]
    
[color=darkblue]End[/color] Sub

 

[Zoolou]

Worked perfectly. I can't believe it was that simple. I really appreciate the help.

Don't declare the variables twice; both as public and within each procedure.

Just declare them once as public and that's it.

[COLOR=darkblue]Public[/COLOR] ReportFile [COLOR=darkblue]As[/COLOR] Workbook
[COLOR=darkblue]Public[/COLOR] DataWorksheet [COLOR=darkblue]As[/COLOR] Worksheet


[COLOR=darkblue]Sub[/COLOR] VBA_Project()
    
    Define_File
    define_tab
    Data_Cleanup
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]




[COLOR=darkblue]Sub[/COLOR] Define_File()
    
    [COLOR=darkblue]Dim[/COLOR] ReportFilePath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    
    ReportFilePath = Application.GetOpenFilename(filefilter:="excel Files,*.xl*;*xm*")
    
    [COLOR=darkblue]If[/COLOR] ReportFilePath <> [COLOR=darkblue]False[/COLOR] [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]Set[/COLOR] ReportFile = Workbooks.Open(Filename:=ReportFilePath)
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


[COLOR=darkblue]Sub[/COLOR] define_tab()
[COLOR=green]'code to get to the desired worksheet'[/COLOR]
    [COLOR=darkblue]Set[/COLOR] DataWorksheet = ActiveSheet
    
[COLOR=darkblue]End[/COLOR] Sub


Sub Data_Cleanup()
    
    ReportFile.Activate
    DataWorksheet.Select
    
    [COLOR=green]'some more code'[/COLOR]
    
[COLOR=darkblue]End[/COLOR] Sub