VBA: Entry Into Input Box - Still picks up in 3rd sub

[jamescooper]

Hello I input myValue in the first sub of the module. Then later on in the 3rd sub, myValue is used again to save the file. How do I carry this through so it remembers it please? I want it to remember just this variable please. Many thanks.

Sub Revenue_Macro()


Dim myValue As Variant
Dim myValue2 As Variant
Application.ScreenUpdating = True
Application.DisplayAlerts = False
Dim FP As String, FN As String
Dim Last_Row As Long
Dim rng As Range


Dim Found As Boolean
Dim z As Byte
Dim Items() As String


Application.Calculation = xlManual
Application.CutCopyMode = False


Items = Split("1710,1711,1712,1713,1801,1802,1803,1804,1805,1806,1807,1808,1809", ",")
Found = False
Do


myValue = InputBox("Enter the railway period, e.g. 1804", ["Enter Period"])

Sub Pivot_Table_2()


Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String
Dim PF As PivotField
Dim PI As PivotItem
Dim FN As String
Dim FP As String


Application.Calculation = xlManual


Last_Row = Sheets("Calculations").Range("A" & Rows.Count).End(xlUp).Row


'Determine the data range you want to pivot
  SrcData = ActiveSheet.Name & "!" & Range("A2:AE" & Last_Row).Address(ReferenceStyle:=xlR1C1)
   
    ActiveWorkbook.PivotCaches.create(SourceType:=xlDatabase, SourceData:= _
        "Calculations!R2C1:R100000C31", Version:=6).CreatePivotTable TableDestination _
        :="Errors!R8C6", TableName:="PivotTable2", DefaultVersion:=6


        
Set pvt = ActiveSheet.PivotTables("PivotTable2")
    
  'Add item to the Report Filter
  
    pvt.PivotFields("Point 1").Orientation = xlRowField
    pvt.PivotFields("Point 1 Stanox Code").Orientation = xlRowField
    pvt.PivotFields("Point 2").Orientation = xlRowField
    pvt.PivotFields("Point 2 Stanox Code").Orientation = xlRowField
    pvt.PivotFields("Mileage").Orientation = xlRowField
    pvt.RowAxisLayout xlTabularRow
    pvt.ColumnGrand = False
    pvt.RowGrand = False
    pvt.RepeatAllLabels xlRepeatLabels
    
    pvt.PivotFields("Point 1").Subtotals(1) = False
    pvt.PivotFields("Point 1 Stanox Code").Subtotals(1) = False
    pvt.PivotFields("Point 2").Subtotals(1) = False
    pvt.PivotFields("Point 2 Stanox Code").Subtotals(1) = False
      
On Error GoTo Err1
    
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Mileage")
        .PivotItems("#N/A").Visible = True
  
        For Each PI In .PivotItems
            If PI.Name <> "#N/A" Then
                PI.Visible = False
            End If
        Next
  
    End With


Err1:


With Worksheets("Errors").Range("F1:J100000")
 .Font.Size = 8
End With


Worksheets("Errors").Range("F1:J100000").Columns.AutoFit


Range("F1") = "Mileage Errors"
Range("F1").Font.Size = 10
Range("F1").Font.Bold = True
Range("F3") = "The following list is mileages between 2 stations that do not exist in the look-up. To include these, go to the mileage tab and enter them at the bottom of the list. If there are no mileage errors there will be no filter on column J."
Range("F3").WrapText = True
Range("F3").Font.Size = 9
Range("F3:J6").Merge


    Rows("3:3").Select
    Rows("3:3").EntireRow.AutoFit
    Range("F3:J6").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    
Range("A1") = "Service Direction Errors"
Range("A1").Font.Size = 10
Range("A1").Font.Bold = True
Range("A3") = "The following list is the service direction that do not exist in the look-up. To include these, go to the look-up tab and enter them at the bottom of the list - columns AC to AQ. If there are no service direction errors there will be no filter on column D."
Range("A3").WrapText = True
Range("A3").Font.Size = 9
Range("A3:D6").Merge


    Rows("3:3").Select
    Rows("3:3").EntireRow.AutoFit
    Range("A3:D6").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    
FP = "C:\Users\Sharing best practice\Revenue\1801\" & FN


FN = Sheets("Look-up").Range("L4")
ActiveWorkbook.SaveAs fileName:=FP, FileFormat:=50


Application.WindowState = xlMaximized


Sheets("Forecast").Select




End Sub

 

[Fluff]

Move this

Dim myValue As Variant

to the very top of the module (ie before any subs), also if the 2 subs are in different modules you may need to change Dim to Public

 

[jamescooper]

Thanks Fluff,

Really appreciate that, I was going around so many different searches trying to find the answer.

 

[Fluff]

Glad to help & thanks for the feedback