Merge multiple sheets in to one

**balima** · Dec 11th, 2003, 03:53 PM

Hello everybody!

I'm a new excel user and I need help to combine multiple sheets from one workbook in to one sheet.

I actually came across a macro by Nehpets12 that does the job nicely but I need the copying to start at cell A12.

Sub Merge()
Dim ws As Worksheet
ActiveSheet.UsedRange.Offset(0).Clear
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> ActiveSheet.Name Then
ws.UsedRange.Copy
Range("A65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End If
Next
End Sub

Thanks in Advance

**santeria** · Dec 11th, 2003, 03:57 PM

This has some code which may be of help.

http://www.mrexcel.com/board2/viewto...ultiple+sheets

Also points out, I think, some limitations that need to be considered.

**balima** · Dec 11th, 2003, 05:47 PM

The code that you referred me as a nice feature that allows me customize the start range.
How can I include that feature in the first code?
Cause having to select each sheet would be time consuming since I will be creating at least 10 sheets per day.

Thanks again

**Smitty** · Dec 11th, 2003, 06:19 PM

Welcome to the Board!

I just tweaked the code that you referenced above:

Code:

Sub Merge()
    Dim ws As Worksheet
        ActiveSheet.UsedRange.Offset(0).Clear
            For Each ws In ActiveWorkbook.Worksheets
                If ws.Name <> ActiveSheet.Name Then
                    ws.UsedRange.Offset(11).Copy
                        With Range("A65536").End(xlUp).Offset(1, 0)
                            .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                            False, Transpose:=False
                            .PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
                            False, Transpose:=False
                        End With
                End If
            Next
End Sub

Hope that helps,

Smitty

**balima** · Dec 11th, 2003, 06:45 PM

Dear pennysaver

Great Job your code resolved the problem.

Thank You !

**Smitty** · Dec 11th, 2003, 06:47 PM

Anytime

Smitty

**psamu** · Sep 22nd, 2009, 01:00 PM

Originally Posted by Smitty

Welcome to the Board!

I just tweaked the code that you referenced above:

Code:

Sub Merge()
    Dim ws As Worksheet
        ActiveSheet.UsedRange.Offset(0).Clear
            For Each ws In ActiveWorkbook.Worksheets
                If ws.Name <> ActiveSheet.Name Then
                    ws.UsedRange.Offset(11).Copy
                        With Range("A65536").End(xlUp).Offset(1, 0)
                            .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                            False, Transpose:=False
                            .PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
                            False, Transpose:=False
                        End With
                End If
            Next
End Sub

Hope that helps,

Smitty

I have 31 sheets and few sheets with different data. I want only first 31 sheets need to be copied, above code works, but problem every sheets copied and If any of sheets have rows hidden or filtered, then that portion wont be copied. How can I copy every cell starting from row 9 thru until row is not equal to blank? (Starting from row 9, but if any row a column is blank then do not copy also.

**alvinf** · Oct 18th, 2012, 06:24 AM

hey guys im kinda new to macros...can you please tell me how do i intiate the macro is it on the new excel sheet to consolidate.....can you guys please help me out if you guys have a video il be really gratefull......need to do consolidation it takes hours to do....please help...