Splitting Workbooks with Multiple Sheets into Multiple Workbooks

[jarvisaurus]

I have 3 workbooks e.g. named A,B,C with multiple sheets 1-30. For each sheet in each workbook, I would like to move those sheets e.g. Workbooks(A).Sheet(1), Workbooks(B).Sheet(1), Workbooks(C).Sheet(1) into one workbook named Sheet(1).

The # of sheets in each workbook can vary.

Is this possible?

I tried the combining a code from some I found:

Sub MoveOPPSheetsNB()
Dim SrcBook As Workbook
Dim FSO As Object, f As Object, ff As Object, f1 As Object, nwb As Workbook
Dim NumSht As Integer
Dim BegSht As Integer
Application.ScreenUpdating = False
Set FSO = CreateObject("Scripting.FileSystemObject")
Set f = FSO.Getfolder("C:\Documents and Settings\USER\Desktop\test2") 'change this path
Set ff = f.Files
 
For Each f1 In ff
Set SrcBook = Workbooks.Open(f1)
For i = 2 To Sheets.Count
SrcBook.Sheets(i).Move
ChDir "C:\Documents and Settings\USER\Desktop\test2"
SrcBook.Save
SrcBook.Close
Next i
Next
End Sub

I got the following error:

"method of 'Sheets' of object '_Workbook' Failed"

Any help is greatly appreciated. Thanks.