How to import data in a specific sheet from another workbook (sheet number must be variable)
Category: [Excel] Demo Available
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!
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.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to create new workbook by copying rows from multiple sheets based on value in column A |
| How to copy/paste between workbooks with relative referencing |
| How to create VBA code for a workbook to work on week days only and specific range of time |
| How to re-name each sheet in workbook with a pre-defined name |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.