myValue from Inputbox carries into next sub

[jamescooper]

Hello, how do I ensure myValue when entered in the inputbox in the first sub below, carries through to the section without being asked again?

Many thanks.

Sub D42_Forecast_Macro()

Dim myValue As Variant


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

End Sub

Public Sub Copy_Cells(myValue As String)

    InputBox myValue
    Dim destcell As Range, r As Long
    Dim fileSpec As String, folderPath As String, fileName As String
    
    cellValue1 = Workbooks("Forecast v.s. D42 Statement Template.xlsx").Sheets("Revenue").Range("U6")
      
    fileSpec = "C:\Users\jamesco\OneDrive for Business\PMO - Schedule 4 - Sharing best practice\2. CrossCountry Trains\Revenue\" & myValue & "\" & cellValue1 & ""

End Sub

 

[Leith Ross]

Hello jamescooper,

Declare your variable "myValue" as Public at the top of the Module. It will retain it's value until the "D42_Forecast" is run again.

Public myValue As Variant


Sub D42_Forecast_Macro()


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


End Sub


Public Sub Copy_Cells(myValue As String)


    Dim destcell As Range, r As Long
    Dim fileSpec As String, folderPath As String, fileName As String
    
    cellValue1 = Workbooks("Forecast v.s. D42 Statement Template.xlsx").Sheets("Revenue").Range("U6")
      
    fileSpec = "C:\Users\jamesco\OneDrive for Business\PMO - Schedule 4 - Sharing best practice\2. CrossCountry Trains\Revenue\" & myValue & "\" & cellValue1 & ""


End Sub

 

[jamescooper]

Hello jamescooper,

Declare your variable "myValue" as Public at the top of the Module. It will retain it's value until the "D42_Forecast" is run again.

Public myValue As Variant


Sub D42_Forecast_Macro()


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


End Sub


Public Sub Copy_Cells(myValue As String)


    Dim destcell As Range, r As Long
    Dim fileSpec As String, folderPath As String, fileName As String
    
    cellValue1 = Workbooks("Forecast v.s. D42 Statement Template.xlsx").Sheets("Revenue").Range("U6")
      
    fileSpec = "C:\Users\jamesco\OneDrive for Business\PMO - Schedule 4 - Sharing best practice\2. CrossCountry Trains\Revenue\" & myValue & "\" & cellValue1 & ""


End Sub

Thanks when I call my 2nd sub,

Call Copy_Cells

It doesn't seem to do anything, do I still have to put

Dim myvalue as Variant in both modules?

Thanks.

 

[jamescooper]

Managed to get it to work, Many thanks!

Remove
Dim myvalue as Variant in both modules!

 

[Leith Ross]

Hello jamescooper,

Sorry about that, I should have mentioned that point in my previous post. Excellent troubleshooting! Glad I could help.

 

[Norie]

All you need to do is remove this from the original code.

    InputBox myValue