Excel 2013-Merge worksheets

Hello Please find attached the sample data with 2 worksheets
sheet1 & 2.
How to merge 2 sheets for each PEL LINE ITEM#.
So in the merged sheet I will have
3 lines for PEL#002, 11 LINES FOR PEL#003.
Thanks all
TEST.xlsx
Star79Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
You can run this macro and it will automatically create Sheets with the name of merge..

Option Explicit

Sub mergedata()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Dim ws As Worksheet
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lr As Long, Lr1 As Long

    Set ws = Sheets("Sheet1")
    Set ws1 = Sheets("Sheet2")

    On Error Resume Next
    Sheets("Merge").Delete

    On Error GoTo 0
    Sheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Merge"
    Set ws2 = ActiveSheet

    lr = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row

    ws.Range("A1:E" & lr).Copy ws2.Range("A1")


    lr = ws1.Cells(Cells.Rows.Count, "A").End(xlUp).Row
    Lr1 = ws2.Cells(Cells.Rows.Count, "A").End(xlUp).Row
    ws1.Range("A2:E" & lr).Copy ws2.Range("A" & Lr1)
    ws2.Range("D:D").ColumnWidth = 51.11
    ws2.Cells.EntireColumn.AutoFit
    ws2.Cells.EntireRow.AutoFit

    Lr1 = ws2.Cells(Cells.Rows.Count, "A").End(xlUp).Row


    ws2.Sort.SortFields.Add Key:=Range("A2:A" & Lr1), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ws2.Sort
        .SetRange Range("A1:E" & Lr1)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    Application.DisplayAlerts = True

    Application.ScreenUpdating = True

End Sub

Open in new window


Your workbook as well for your reference...
Saurabh...
TEST.xlsm
Star79Author Commented:
This is great Saurabh,
Iam not very familiar with macros.Can you tell me where to enter the macro code
Saurabh Singh TeotiaCommented:
In order to run the macros..Copy this macro...then go to your workbook..press alt+f11 which will open the vb editor page..In their go to insert on the top and click on insert new module..

Once you insert the new module..paste the code their..then come back to your excel workbook..and press alt+f8 and run the macro name mergedata

This will do what you are looking for...
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rob HensonFinance AnalystCommented:
Why not just Copy & Paste one lot of data below the other, or both onto a new sheet, and then Sort by PEL number???

Thanks
Rob H
Star79Author Commented:
Saurab the solution worked well.The file atatched has some modifications to it.The sheet1 and 2 has some extra columns can we still merge them
TEST.xlsx
Star79Author Commented:
Rob Iam open for any other ideas as well, the idea of copy paste and then sort would work as well but for my top scenario please let me know
Saurabh Singh TeotiaCommented:
Use this code...

Also Rob is suggesting the same thing which i'm doing in the code already provided to you which is copy and paste the data from both worksheets to a new worksheet and then sorting the data basis of A Column...

Sub mergedata()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Dim ws As Worksheet
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lr As Long, Lr1 As Long

    Set ws = Sheets("Sheet1")
    Set ws1 = Sheets("Sheet2")

    On Error Resume Next
    Sheets("Merge").Delete

    On Error GoTo 0
    Sheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Merge"
    Set ws2 = ActiveSheet

    lr = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row

    ws.Range("A1:f" & lr).Copy ws2.Range("A1")


    lr = ws1.Cells(Cells.Rows.Count, "A").End(xlUp).Row
    Lr1 = ws2.Cells(Cells.Rows.Count, "A").End(xlUp).Row
    ws1.Range("A2:f" & lr).Copy ws2.Range("A" & Lr1)
    ws2.Range("D:D").ColumnWidth = 51.11
    ws2.Cells.EntireColumn.AutoFit
    ws2.Cells.EntireRow.AutoFit

    Lr1 = ws2.Cells(Cells.Rows.Count, "A").End(xlUp).Row


    ws2.Sort.SortFields.Add Key:=Range("A2:A" & Lr1), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ws2.Sort
        .SetRange Range("A1:f" & Lr1)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    Application.DisplayAlerts = True

    Application.ScreenUpdating = True

End Sub

Open in new window


Saurabh...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.