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"
Set wb2 = Workbooks.Open(Filename:=FileToOpen)
For Each Sheet In wb2.Sheets
Set PasteStart = PasteStart.Offset(.Rows.Count)