User Prompt to Select Worksheet to Import from Selected Workbook

[wdp74]

Hello all,

I have a macro that allows the user to select a workbook and specific sheet to import (sheet names are not consistent). However, running into the coding I have that errors when a sheet number is greater than 9 (sheet10, 11, 12 etc..). This is the code after the user has selected the workbook to open. Help!

With ActiveWorkbook
For i = 1 To .Worksheets.Count
msg = msg & "(" & i & ") " & .Sheets(i).Name & vbCrLf
Next i

response = InputBox(msg, "Type the number to select the wsFile worksheet to import")

If response = Null Then Exit Sub

For x = 1 To ActiveWorkbook.Worksheets.Count
If InStr(response, x) > 0 Then
Dim wsFile As Worksheet
Set wsFile = ActiveWorkbook.Sheets(x)

 

[GWteB]

Hello wdp74, welcome to the MrExcel message board!

Within your code, replace this

If InStr(response, x) > 0 Then

by this

If x = CLng(response) Then

and it should be fine.

 

[wdp74]

It worked perfect, thank you so much!