Reference a worksheet in vba using inputbox

Question

I got this code using the macro recorder but I need to run this macro pulling data from diferent worksheets, What I'm trying to acomplish is to launch the inputbox to tell the formula where to look for the data, my worksheets names are 1,2,3,4 and so on meaning each corresponds to a day of the month. In this example the VLOOKUP formula is referencing worksheet 5 ('5') can you please help me to figure out the code to replace the 5 (in this case) for any other number I set using the inputbox? I've tried something like:Dim myNum As String
myNum = Application.InputBox("Enter Worksheet Number", Type:=1) but cannot get it work this the sample code:

   Sub GetMPRE_Data()
'
' GetMPRE_Data Macro
' Gets MPRE data to add to MLEA total
'
' Keyboard Shortcut: Ctrl+Shift+G
'
ActiveCell.FormulaR1C1 = "Staffed MPRE"
Range("K10").Select
ActiveCell.FormulaR1C1 = "Non-Prod MPRE"
Range("L10").Select
ActiveCell.FormulaR1C1 = "Staffed Time"
Range("M10").Select
ActiveCell.FormulaR1C1 = "Staffed Time Decimal"
Range("N10").Select
ActiveCell.FormulaR1C1 = "Non-Productive"
Range("O10").Select
ActiveCell.FormulaR1C1 = "Non-Productive Decimal"
Range("J11").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-9],'5'!R[-9]C[-9]:R[89]C[-5],2,FALSE)"
Range("K11").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-10],'5'!R[-9]C[-10]:R[90]C[-6],4,FALSE)"
Range("L11").Select
ActiveCell.FormulaR1C1 = "=RC[-7]+RC[-2]"
Range("L11").Select
Selection.NumberFormat = "[h]:mm:ss"
Range("M11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*24"
Range("M11").Select
Selection.NumberFormat = "0.0"
Range("N11").Select
ActiveCell.FormulaR1C1 = "=RC[-8]+RC[-3]"
Range("N11").Select
Selection.NumberFormat = "[h]:mm:ss"
Range("O11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*24"
Range("O11").Select
Selection.NumberFormat = "0.0"
Range("J11:O11").Select
Selection.AutoFill Destination:=Range("J11:O30"), Type:=xlFillDefault
Range("J11:O30").Select
Range("R28").Select
ActiveWindow.SmallScroll Down:=-12
Range("J11:J30").Select
With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
Range("J11").Select
End Sub

Any help will be appreciated.

What cell are you starting on? The first code line references ActiveCell. Is it J10 on the active worksheet? — user4039065

Unknown Unknown · Accepted Answer · 2015-02-25T06:36:22

I've removed your .Select statements and resolved the formulas to their xlA1 equivalents. You should proof those carefully as I was counting the alphabet (columns) backwards on my fingers.

Sub GetMPRE_Data()
    ' GetMPRE_Data Macro
    ' Gets MPRE data to add to MLEA total
    ' Keyboard Shortcut: Ctrl+Shift+G
    Dim vWS As Variant
    vWS = InputBox("Select a worksheet", "Workshgeet Slector", Default:=ActiveSheet.Name)
    Debug.Print vWS
    If Len(vWS) Then
        With ActiveSheet
            .Range("J10").Resize(1, 6) = _
              Array("Staffed MPRE", "Non-Prod MPRE", "Staffed Time", "Staffed Time Decimal", "Non-Productive", "Non-Productive Decimal")
            .Range("J11").Formula = "=VLOOKUP(A11,'" & vWS & "'!$A$2:E100, 2, FALSE)"
            .Range("K11").Formula = "=VLOOKUP(A11,'" & vWS & "'!$A$2:E101, 4, FALSE)"
            .Range("L11").Formula = "=SUM(E11, J11)"
            .Range("L11").NumberFormat = "[h]:mm:ss"
            .Range("M11").Formula = "=L11*24"
            .Range("M11").NumberFormat = "0.0"
            .Range("N11").Formula = "=SUM(F11, K11)"
            .Range("N11").NumberFormat = "[h]:mm:ss"
            .Range("O11").Formula = "=M11*24"
            .Range("O11").NumberFormat = "0.0"
            With .Range("J11:O30")
                .FillDown
                With .Interior
                    .Pattern = xlNone
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
            End With
        End With
    End If
End Sub

The VLOOKUP formulas are constructed from concatenated strings using the variant that the input box returned. If you get a File ► Open dialog, then the worksheet name is incorrect. You should be able to write a short loop that verified that the name supplied exists if you want to apply some error control on the worksheet designation process.

Reference a worksheet in vba using inputbox

1 Answers