Macro to compile 6 active sheets into one workbook on 6 tabs

[Ejohnson]

I took a macro to open multiple files and adapted it to open text files into multiple workbooks. However, now that I have the open files, I can't figure out the macro to compile the active one sheet workbooks into a single workbook without limiting the macro to specific workbook names. Here is the portion I am having problems with.

Workbooks.Add
Windows("030325s.wri").Activate
Sheets("030325s").Select
Sheets("030325s").Move Before:=Workbooks("Book8").Sheets(1)
Windows("030325gw.wri").Activate
Sheets("030325gw").Select
Sheets("030325gw").Move Before:=Workbooks("Book8").Sheets(2)
Windows("030325as.wri").Activate
Sheets("030325as").Select
Sheets("030325as").Move Before:=Workbooks("Book8").Sheets(3)
Windows("030325ie.wri").Activate
Sheets("030325ie").Select
Sheets("030325ie").Move Before:=Workbooks("Book8").Sheets(4)
Windows("030325zp.wri").Activate
Sheets("030325zp").Select
Sheets("030325zp").Move Before:=Workbooks("Book8").Sheets(5)
End Sub

It opens a new workbook then moves the sheets to the new workbook. Is there a way to do this and use the active workbooks which are already open?

 

[tusharm]

Re: Macro to compile 6 active sheets into one workbook on 6

The code below assumes it is located in a workbook that contains just code (i.e., no data).

Option Explicit

Sub testIt()
    Dim newWB As Workbook, aWB As Workbook
    Set newWB = Workbooks.Add
    For Each aWB In Application.Workbooks
        If aWB Is newWB Or aWB Is ThisWorkbook Then
        Else
            aWB.Sheets(1).Move before:=newWB.Sheets(1)
            End If
        Next aWB
    End Sub