Merge multiple sheets in to one

[balima]

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]

This has some code which may be of help.

http://www.mrexcel.com/board2/viewtopic.php?t=64734&highlight=merge+multiple+sheets

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

 

[balima]

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]

Welcome to the Board!

I just tweaked the code that you referenced above:

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]

Dear pennysaver

Great Job your code resolved the problem.

Thank You ! ray:

 

[Smitty]

Anytime

Smitty

 

[psamu]

Welcome to the Board!

I just tweaked the code that you referenced above:

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]

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...

 

[aditi_lic]

Here is a ready made excel file to merge multiple sheets to one
Merge Excel Sheets 2007 / 2010 / 2003 » Tutorial- PHP Script, HTML, Java Script, SQL, MS Excel

 

[aditi_lic]

Here is a ready made excel file to merge multiple sheets to one
Merge Excel Sheets 2007 / 2010 / 2003 » Tutorial- MS Excel- 2010, 2007