VBA String Issue

[flipcube]

Hi all,

I don't understand why the below code is not working. I'm Creating a program that formats Pivot Tables based on Fiscal Month. Sheet2 contains a table of FiscalMonth start and end dates. The issue I am having is that my Create_Pivot Subroutine, for some reason, does not "see" the value in FiscalMonth. Below is the part of Create_Pivot () that is giving me the problem. The if FiscalMonth = "September" statement is ignored and the subroutine ends. I'm new to VBA and do not fully understand why it's not working.

Private Sub CommandButton1_Click()
'This Subroutine determines the current Fiscal Month

Dim Today As Date
Dim FiscalMonth As String

Today = Date

If Today > Sheet2.Cells(12, 1) And Today <= Sheet2.Cells(12, 2) Then
FiscalMonth = "September"
Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=1 Create_Pivot
End If
End Sub

--------------

Private Sub Create_Pivot()

If FiscalMonth = "September" Then

--------------

any help is much appreciated.

Thanks

 

[VoG]

Try like this

Private Sub CommandButton1_Click()
'This Subroutine determines the current Fiscal Month

Dim Today As Date
Dim FiscalMonth As String

Today = Date

If Today > Sheet2.Cells(12, 1) And Today <= Sheet2.Cells(12, 2) Then
FiscalMonth = "September"
Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=1
Call Create_Pivot(FiscalMonth)
End If
End Sub


Private Sub Create_Pivot(FiscalMonth As String)
If FiscalMonth = "September" Then

 

[flipcube]

VoG,

I get a Compile Error: Argument not optional. I looked at the help menu, but its Greek to me.

 

[flipcube]

VoG,

I take that back...worked like a charm. I forgot to add (FiscalMonth) to the Call Create_Pivot.

Thanks a bunch...why did it not work the original way?

 

[VoG]

Did you apply both suggestions (in red)

Private Sub CommandButton1_Click()
'This Subroutine determines the current Fiscal Month

Dim Today As Date
Dim FiscalMonth As String

Today = Date

If Today > Sheet2.Cells(12, 1) And Today <= Sheet2.Cells(12, 2) Then
FiscalMonth = "September"
Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=1
Call Create_Pivot(FiscalMonth)
End If
End Sub


Private Sub Create_Pivot(FiscalMonth As String)
If FiscalMonth = "September" Then

 

[VoG]

why did it not work the original way?

You declared FiscalMonth within a Sub. That value won't be available to other subs unless you either

Dim it at the top of the module, before any Subs

Or, as I did, pass it as a parameter.