VBA Loop not working correctly

I have an issue pulling in multiple sheets from one workbook into another workbook in Excel. I have been around the posts, and can't seem to find a good solution. I have two-subs that work together. The first is set to a button that the end user will get the source file with the raw data (this file name will be different every time). This part seems to work just fine:

Please Login or Register  to view this content.

Once this source file is open, there will be between 10-20 sheets with data that I need to pull into the other workbook "18AWM036_IMG_BondEdge_ReportBook_WM111_manual.xlsm"
Those sheets in the source file will always have changing names and the number of sheets will vary, so this will need to work given those criteria. Each sheet in the source file will also vary, so I made the 'Copy' range vast. Once copied I need those source file sheets copied into the other workbook at the very end. Here is the code, which doesn't work quite right. Trying to see where my issue is (I have a background in Java, but not VBA):
[CODE]
Sub readExcelData(sTheSourceFile)

Application.ScreenUpdating = False

Dim src As Workbook, x As Long
Set src = Workbooks.Open(sTheSourceFile, False, True)
For x = 1 To Sheets.Count
src.Sheets(x).Select
Sheets(x).Range("A1", "ZZ500").Copy
Workbooks.Open ("18AWM036_IMG_BondEdge_ReportBook_WM111_manual.xlsm")
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste
src.Activate
Next x

End Sub

If you want to copy each sheet wholesale, the better way to do it is to copy the sheet object rather than the sheet's content.

You don't want to open the file each pass in the loop.

You should type all variables, even Sub parameters.

It is a best practice in VBA to explicitly qualify object references, with few exceptions.

The biggest difference between Java and VBA is that VBA incorporates an object model specific to the application that is running the code.

I have not tested this code due to the need to replicate your environment but please try this and let me know how it works.

Please Login or Register  to view this content.

Glad to help!

If a member helped you solve your problem, consider adding to their reputation by clicking Add Reputation below their name.