Prompt User to Select Sheet to Copy

[sunstache32]

As part of a larger macro, I have a working piece of code that opens a new workbook, copies the first sheet of that workbook, pastes the sheet into a new sheet of the original workbook, then closes the opened workbook. I want to change this to allow the user to choose which sheet to copy (instead of just the first). Is it possible to do this? Here is the code that copies the first sheet:

Dim wb_mainFile As Workbook
Dim strMainFile As String

strMainFile = Range("G4").value

'G4 is the cell that has the path for the workbook to open

Set wb_mainFile = Workbooks.Open(strMainFile)

ThisWorkbook.Activate
wb_mainFile.Sheets(1).Copy _

After:=wb3.Sheets(wb3.Sheets.Count)

ActiveSheet.Name = "Sheet3"
wb_mainFile.Close

 

[Eric W]

How about:

Dim wb_mainFile As Workbook
Dim strMainFile As String
Dim x As Integer, str1 As String


    strMainFile = Range("G4").Value


'G4 is the cell that has the path for the workbook to open


    Set wb_mainFile = Workbooks.Open(strMainFile)


    str1 = "Choose the number of a sheet:" & Chr(10) & Chr(10)
    
    ctr = 1
    For Each sh In wb_mainFile.Worksheets
        str1 = str1 & ctr & "    " & sh.Name & Chr(10)
        ctr = ctr + 1
    Next sh
    
    x = InputBox(str1, "Enter the sheet")
    If x <> "" And x < ctr Then
        ThisWorkbook.Activate
        wb_mainFile.Sheets(x).Copy After:=wb3.Sheets(wb3.Sheets.Count)
    End If


    ActiveSheet.Name = "Sheet3"
    wb_mainFile.Close

 

[sunstache32]

It works up until the part where you enter the number of the sheet you want. After entering the sheet number, the workbook is opened, but it doesn't copy the desired sheet (or any other sheets) and a type mismatch error is returned.

 

[Eric W]

Sorry about that. I tested most of it, and added a few lines later. Try this:

Sub CopySheet()
Dim strMainFile As String
Dim x As Integer, xstr as string, str1 As String

    strMainFile = Range("G4").Value

'G4 is the cell that has the path for the workbook to open

    Set wb_mainFile = Workbooks.Open(strMainFile)

    str1 = "Choose the number of a sheet:" & Chr(10) & Chr(10)
    
    ctr = 1
    For Each sh In wb_mainFile.Worksheets
        str1 = str1 & ctr & ".    " & sh.Name & Chr(10)
        ctr = ctr + 1
    Next sh
    
    xstr = InputBox(str1, "Enter the sheet")
    On Error goto BadNumber:
    x = CInt(xstr)

    If x > 0 And x < ctr Then
        ThisWorkbook.Activate
        wb_mainFile.Sheets(x).Copy After:=wb3.Sheets(wb3.Sheets.Count)
    End If

    ActiveSheet.Name = "Sheet3"
    wb_mainFile.Close
    
    Exit Sub

BadNumber:
    MsgBox "That's not a valid number"
End Sub

 

[sunstache32]

That works perfectly! I like that code a lot actually, thank you so much!

 

[Eric W]

I'm happy it worked. Thanks for the feedback.

 

[youngbella]

Hello sir @Eric W , I'm sorry to interrupt but I tried this code. and after I enter the sheet number, it said "that's not a valid number". But I already put the right sheet no.

Perhaps you can enlighten me a bit on this error?

 

[Eric W]

You should only get that error message if you enter a value that isn't a number. If you type the word "one", or use the letter "l" (lower case L), for a 1, or some other combination of character and numeric data.