Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Merge multiple sheets in to one

This is a discussion on Merge multiple sheets in to one within the Excel Questions forums, part of the Question Forums category; Hello everybody! I'm a new excel user and I need help to combine multiple sheets from one workbook in to ...

  1. #1
    New Member
    Join Date
    Dec 2003
    Location
    Mali
    Posts
    3

    Default Merge multiple sheets in to one

    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

  2. #2
    Board Regular santeria's Avatar
    Join Date
    Oct 2003
    Location
    Tallahassee
    Posts
    1,844

    Default Re: Merge multiple sheets in to one

    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.

    "Don't Ruin an Apology with an Excuse"...

  3. #3
    New Member
    Join Date
    Dec 2003
    Location
    Mali
    Posts
    3

    Default Re: Merge multiple sheets in to one

    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

  4. #4
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,208

    Default Re: Merge multiple sheets in to one

    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

  5. #5
    New Member
    Join Date
    Dec 2003
    Location
    Mali
    Posts
    3

    Default Re: Merge multiple sheets in to one

    Dear pennysaver

    Great Job your code resolved the problem.

    Thank You !

  6. #6
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,208

    Default Re: Merge multiple sheets in to one

    Anytime

    Smitty

  7. #7
    Board Regular
    Join Date
    Jan 2007
    Posts
    353

    Default Re: Merge multiple sheets in to one

    Quote Originally Posted by Smitty View Post
    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.

  8. #8
    New Member
    Join Date
    Oct 2012
    Posts
    1

    Default Re: Merge multiple sheets in to one

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

  9. #9
    New Member
    Join Date
    Feb 2013
    Posts
    25

    Default Re: Merge multiple sheets in to one

    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

  10. #10
    New Member
    Join Date
    Feb 2013
    Posts
    25

    Thumbs up Re: Merge multiple sheets in to one

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

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com