Data from one tab and ordering in another

[iksotof]

Hello

Is it possible to have some code to take the data shown in the below example from sheet 1 and sheet 2 and order it altogather ina continpis list as in sheet with tthe extra columns added to the right? This si a mock up example but in reality there could be many tabs from which the data is taken. Any help is immensely appreciated.


thank you Darren.

[HansV]

Will the last sheet already be present, or should the code create it?

[Rudi]

Hi,

Give this macro a try.
Attached is a working example of your file.

Code: Select all

Sub CombineData()
Dim sh As Worksheet
    Application.ScreenUpdating = False
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Combined").Delete
    On Error GoTo 0
    Sheets.Add Before:=Sheets(1)
    ActiveSheet.name = "Combined"
    For Each sh In Sheets
        If sh.name <> "Combined" Then
            sh.Range("A1").CurrentRegion.Offset(1).Copy Sheets("Combined").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If
    Next sh
    Sheets(2).Rows(1).Copy
    Sheets(1).Range("A1").PasteSpecial
    Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Resize(, 3).Value = Array("Sum If", "Bulk Look Up", "Match")
    Range("A1").Copy
    Range("A1").CurrentRegion.Rows(1).PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
    Columns.AutoFit
    Range("A1").Select
    Application.ScreenUpdating = True
End Sub

[iksotof]

Thank you Hans


Yes the last tab will always be there.


Kind regards Darren.

[HansV]

Have you tried Rudi's macro?

[iksotof]

Hi

Sorry, I had overlooked Rudi's solution, this does work. Thank you very much. However in the example that I provided, I omitted to include that there is an extra sheet from which I don't want the data combined, a little like in this example whereby sheet 1 is not to be included. It will always be the first sheet and it should laways be called sheet1 , though i can gaurantee. Is there a way to run the combine macro and not include sheet1?


Thank you Darren.

[HansV]

You could change the line

Code: Select all

        If sh.name <> "Combined" Then

to

Code: Select all

        If sh.name <> "Combined" And sh.name <> "Sheet1" Then

If you're not certain the name will always be "Sheet1", you can use

Code: Select all

Sub CombineData()
    Dim sh As Worksheet
    Dim strName As String
    Application.ScreenUpdating = False
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Combined").Delete
    On Error GoTo 0
    strName = Sheets(1).name
    Sheets.Add Before:=Sheets(1)
    ActiveSheet.name = "Combined"
    For Each sh In Sheets
        If sh.name <> "Combined" And sh.name <> strName Then
            sh.Range("A1").CurrentRegion.Offset(1).Copy Sheets("Combined").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If
    Next sh
    Sheets(2).Rows(1).Copy
    Sheets(1).Range("A1").PasteSpecial
    Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Resize(, 3).Value = Array("Sum If", "Bulk Look Up", "Match")
    Range("A1").Copy
    Range("A1").CurrentRegion.Rows(1).PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
    Columns.AutoFit
    Range("A1").Select
    Application.ScreenUpdating = True
End Sub

[iksotof]

Hi Hans


thank you for this, is has not quite worked with the new sheet created not havin all the headings required column header, some have copied into the sheet1. Also it threw up the error "run-time error'1004' application defined or object defined error". I attach an example to show this. Any further guidance is most welcomed.


Thank you again.

[HansV]

Does the attached version work better for you?

Book1.xlsm

[iksotof]

Thanks Hans


that's great.