Macro to copy all sheets from open workbook to end of another open workbook

[clabulis]

I've searched quite a bit for the solution to my issue, but I can't find one anywhere.

My code will open up an Excel file determined by the user entering the full path (example: C:\filepath\filename.ext) into a userform textbox, then clicking an "Import File" button. A message box pops up asking them to confirm. When confirmed, their specified file will open.

I would then like to copy all sheets from that opened Excel file to the end of the current open workbook containing the code. This workbook is called "TEMPLATE Large".

The code below works up until the line in red. At that point, the error states the following:

Run-time error '9':

Subscript out of range

I'm using Excel 2010 for anyone who thinks they can help.

Private Sub btnImportFile_Click()

Dim x As Integer


    Select Case MsgBox("Are you sure you want to import file?", vbYesNo, "Import File")
        Case Is = vbNo
            Exit Sub
        Case Is = vbYes
            Workbooks.Open (tbFilePath.Value)  [COLOR=#008000]'tbFilePath.Value is a file path that the user has previously entered into a userform textbox in the form of:  C:\filepath\filename.ext[/COLOR]
                For x = 1 To ActiveWorkbook.Sheets.Count
[COLOR=#ff0000]                    ActiveWorkbook.Sheets(x).Copy After:=Workbooks("TEMPLATE Large.xlsm").Sheets(Sheets.Count)[/COLOR]
                Next
        End Select
               
End Sub

Thanks!

 

[BiocideJ]

I'm using Excel 2010 for anyone who thinks they can help.

Challenge Accepted!!!

Really, your code is pretty close to correct, except when you reference Sheets.Count you are referencing the sheet count in the SOURCE workbook instead of the DESTINATION workbook. Instead of looping, however, you even more simply just copy the whole block at once as in the following example:

Private Sub btnImportFile_Click()
    Select Case MsgBox("Are you sure you want to import file?", vbYesNo, "Import File")
        Case vbNo
            Exit Sub
        Case vbYes
            Workbooks.Open(tbFilePath.Value).Sheets.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        End Select
               
End Sub

EDIT:
The ThisWorkbook object is the workbook that contains the code being run so according to your explanation, this should work fine, however ,you could also correctly use Workbooks("TEMPLATE Large.xlsm") in place of ThisWorkbook

 

[clabulis]

Code worked perfectly! Can't believe I overlooked some of that.

Thanks BiocideJ