How to open file (xcel workbook), copy the used sheets, then paste them into into my current (Active)open xcel work book, starting from sheet2

The following code will allow me to open xcel file, copy the contents of the first sheet, and paste into  into my current open workbook starting at sheet2. If the input file has multiple used sheets then how do I get it to paste all the used sheets into my current open workbook starting form sheet#. And how do I retain the sheet name of the input file. I am new to VBA and need to write a utility this week. If I can't get this solved soon then I will pay someone to write it for me. I would then at least know how to write proper VBA code.

Private Sub CommandButton2_Click()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim Sheet As Worksheet
Dim PasteStart As Range

Set wb1 = ActiveWorkbook
Set PasteStart = [Sheet2!A1]

FileToOpen = Application.GetOpenFilename(Title:="Please choose a Reference file")

If FileToOpen = False Then
    MsgBox "No File Specified.", vbExclamation, "ERROR"
    Exit Sub
     Set wb2 = Workbooks.Open(Filename:=FileToOpen)

     For Each Sheet In wb2.Sheets
        With Sheet.UsedRange
            .Copy PasteStart
            Set PasteStart = PasteStart.Offset(.Rows.Count)
        End With
     Next Sheet
End If

End Sub
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
\\\\\\\\\\see if this helps. It might need some ramending but I'll check back later
Option Explicit

' Module    : Data
' Author    : Roy Cox (royUK)
' Website   : for more examples and Excel Consulting
' Date      : 19/11/2011
' Purpose   : Combine data from several workbooks
' Disclaimer: Disclaimer; This code is offered as is with no guarantees. You may use it in your
'             projects but please leave this header intact.


Sub CombineData()
    Dim oWbk As Workbook, wb2 As Workbook
    Dim oSht As Worksheet, MainSht As Worksheet
    Dim rRng As Range, rToCopy As Range, rNextCl As Range
    Dim lCount As Long
    Dim bHeaders As Boolean
    Dim FileToOpen

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
        Set MainSht = Sheets(2)
        '   On Error GoTo exithandler

        FileToOpen = Application.GetOpenFilename(Title:="Please choose a Reference file")

        If FileToOpen = False Then
            MsgBox "No File Specified.", vbExclamation, "ERROR"
            Exit Sub
            Set wb2 = Workbooks.Open(Filename:=FileToOpen)
        End If
        For Each oSht In wb2.Worksheets
            With oSht
                Set rRng = MainSht.Range("A1").CurrentRegion
                If rRng.Cells.Count = 0 Then
                    'no data in master sheet
                    bHeaders = False
                Else: bHeaders = True
                End If

                'A1 must be within the data, if not amend the Range below
                Set rToCopy = oSht.Range("A1").CurrentRegion
                If Not bHeaders Then
                    Set rNextCl = MainSht.Cells(2, 1)
                    MainSht.Cells(1, 1).Value = "Sheet name"
                    bHeaders = True
                Else: Set rNextCl = MainSht.Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0)
                    'headers exist so don't copy
                    Set rToCopy = rToCopy.Offset(1, 0)
                    On Error Resume Next
                    rNextCl.CurrentRegion.Columns(1).SpecialCells(xlCellTypeBlanks).Value = oSht.Name
                    On Error GoTo 0
                End If
                rToCopy.Copy rNextCl
            End With
        Next oSht
        wb2.Close False
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.