How to compile multiple worksheets into 1 worksheet?

[pyro77]

Hey guys,
with the solution I've got yesterday (http://www.mrexcel.com/forum/showthread.php?t=353658), I've been asked to write another macro to compile the worksheets into 1 worksheet.

Basically, the data in the generated worksheet will be placed side by side to each other, place 1 empty column in between them.

It is a whole new macro that would be able to detect how many month worksheets there are so that it will stop when it reaches the last worksheet

Here's a screenshot of 1 sheet (sorry for blanking out some stuff, can't be too careful, but you get the basic idea),

And here's a screenshot of what the macro should put out.

Notice the empty column Q? There should be an empty column after every month. No need for the bold headings and colouring right now, that's not important.


Any help is appreciated.
Thanks

 

[pyro77]

Ok, I've found some codes on the web for merging workbooks.
Here's what I have

Sub CopyRangeFromMultiWorksheets()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long
    Dim CopyRng As Range

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Delete the sheet "Compiled" if it exist
    Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Worksheets("Compiled").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    'Add a worksheet with the name "Compiled"
    Set DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "Compiled"

    'loop through all worksheets and copy the data to the DestSh
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> DestSh.Name Then

            'Find the last row with data on the DestSh
            'Last = LastRow(DestSh)

            'Fill in the range that you want to copy
            Set CopyRng = sh.Range("A1:Q117")

            'Test if there enough rows in the DestSh to copy all the data
            If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
                MsgBox "There are not enough rows in the Destsh"
                GoTo ExitTheSub
            End If

            'This example copies values/formats, if you only want to copy the
            'values or want to copy everything look at the example below this macro
           
            CopyRng.Copy DestSh.Cells(Last + 1, "A")
            

            'Optional: This will copy the sheet name in the H column
           ' DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value = sh.Name

        End If
    Next sh

ExitTheSub:

    Application.Goto DestSh.Cells(1)

    'AutoFit the column width in the DestSh sheet
    DestSh.Columns.AutoFit

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Credit: http://www.rondebruin.nl/copy2.htm

I made some modifications, so anything commented out is something I felt wasn't necessary.
Thing is, it only copies 1 worksheet from the workbook, the first one. It doesn't seem to go through to the next sheet.

Also, I think this code will place the next sheet below the data of the first one.
Something to do with CopyRng.Copy DestSh.Cells(Last + 1, "A")?

 

[jindon]

Modify as you like

Sub test()
Dim ws As Worksheet, LastC As Long
For Each ws In Worksheets
    If ws.Name <> "Summary" Then
        LastC = Sheets("Summary").Cells.SpecialCells(11).Column
        ws.Range("a:p").Copy Sheets("Summary").Cells(1, LastC + 2)
    End If
Next
Application.CutCopyMode = False
End Sub

 

[pyro77]

Thanks jindon but I got the above code I got to work somewhat. It works but it doesn't format it right.

The data are on top of each other instead of next to each other, starting from the last month to the earliest month below. I need it to be next to each other with the earliest month on the starting on the left and a blank column after every month.

It's somewhere in this code snippet, I'm just not familiar with cell assignments.

            CopyRng.Copy
            With DestSh.Cells(Last + 1, "A")
                .PasteSpecial xlPasteValues
                .PasteSpecial xlPasteFormats
                Application.CutCopyMode = False
            End With

 

[jindon]

Sorry, I don't want to read somebody else's code...

Wait for someone else to come in.

 

[pyro77]

Anybody else can help?

 

[pyro77]

Not sure if this would help but it was in a seperate module from the zip file in the example from the website stated above.

Option Explicit

'Common Functions required for all routines:

Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(what:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function


Function LastCol(sh As Worksheet)
    On Error Resume Next
    LastCol = sh.Cells.Find(what:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
    On Error GoTo 0
End Function

 

[pyro77]

Anybody? Really need your help here.

 

[pyro77]

Please help me.

 

[1chigo]

Thing is, it only copies 1 worksheet from the workbook, the first one. It doesn't seem to go through to the next sheet.

you can fix the problem by replacing

'Find the last row with data on the DestSh
'Last = LastRow(DestSh)

with this one:

 Last = DestSh.UsedRange.Rows.Count

you also may find this link useful:
http://www.mrexcel.com/td0058.html

CMIIW