How to Import data in a specific sheet from another workbook (sheet number must be variable)

How to import data in a specific sheet from another workbook (sheet number must be variable)

Requirement:

The users needs to import data from another workbook -- this is a simple task, but in this case the user wants to use the same code to get data from whichever worksheet the user specifies by its number.

If the user hardcodes the sheet:

Code:

Set OldQzSheet = OldQzbook.Worksheets(5)

The below works exactly as intended ... but the user does not always want Sheet 5, sometimes it'll be Sheet 6 or 10 or etc.

So the user changed the hardcoded sheet to an input box:

Code:

Set OldQzSheet = OldQzbook.Worksheets(OldQzSheetNumber)
OldQzSheetNumber = InputBox("Enter number of the sheet you want to import.")

Then get sa "subscript out of range" error.

The complete spin (sans all the copying) is as follows:

Code:

Sub importGradedQZ()Dim filter As String
Dim caption As String
Dim OldQz As Variant
Dim OldQzbook As Workbook
Dim QuizScorebook As Workbook
Dim EmailCheck As String
Dim TempSht As String
Set QuizScorebook = Application.ActiveWorkbook
' Open the previously graded Quizbook
filter = "Text files (*.xlsm),*.xlsm"
caption = "Please Select an input file "
OldQz = Application.GetOpenFilename(filter, , caption)
' If there is no file, then bail
If OldQz = False Then Exit Sub
Set OldQzbook = Application.Workbooks.Open(OldQz)


Dim NuQzSheet As Worksheet
Set NuQzSheet = QuizScorebook.ActiveSheet
Dim OldQzSheet As Worksheet
' Set OldQzSheet = OldQzbook.Worksheets(5)


Dim OldQzSheetNumber As Variant
Set OldQzSheet = OldQzbook.Worksheets(OldQzSheetNumber)
' pick the sheet with the coveted data to copy over
OldQzSheetNumber = InputBox("Enter number of the sheet you want to import.")
' If there is no number, then bail
If OldQzSheetNumber = False Then Exit Sub
'
'
' magic!

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/148880-import-data-in-a-specific-sheet-from-another-workbook-sheet-number-must-be-variable

Solution:

Code:

Dim OldQzSheetNumber As Integer
On Error Resume Next
OldQzSheetNumber = InputBox("Enter number of the sheet you want to import.")
If OldQzSheetNumber = 0 Then    'If user clicks the Cancel button
    MsgBox "You didn't provided a sheet number.", vbExclamation
    Exit Sub
Else
    Set OldQzSheet = OldQzbook.Worksheets(OldQzSheetNumber)
    On Error GoTo 0
    If OldQzSheet Is Nothing Then
        MsgBox "Sheet with index number " & OldQzSheetNumber & " was not found in the workbook.", vbExclamation, "Sheet now found!"
        Exit Sub
    End If
End If

Obtained from the OzGrid Help Forum.

Solution provided by sktneer.