Consolidate data from two sheets of same workbook

[indianhp]

Hi Team,

I have a worksheet which is having two data sheets getting data from SQL Query. Now Issue is that I need to combine both data into one sheet. I have tried to create macro which copy data from both worksheets one by one and copy it to one. But it does not work properly. I need to just combine both tables. I have attached an example sheet for the same.

Is there any function that can help to combine this data or macro. I have tried to modify SQL query but its not working. So I think only excel function or macro can do this.

[FDibbins]

Hi and welcome to the forum

What would your expected outcome look like?

[oeldere]

@indianhp

with the great help of other forummembers (Nilem and AB33) in the link below. I was able to made this code for your sheet.

http://www.excelforum.com/excel-prog...50#post3285950

Sub integratie_Oeldere()

Dim wsTest As Worksheet

'check if sheet "ouput" already exist

Const strSheetName As String = "Consolidated"
 
Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0
 
If wsTest Is Nothing Then
    Worksheets.Add.Name = strSheetName
End If

With Sheets("Consolidated")
    .UsedRange.ClearContents
    .Range("A1:E1").Value = Array("sheet", "Task", "Type", "Key Patient", "Key Doctor")
    For Each sh In Sheets
        With sh
            If .Name <> "Consolidated" Then
                Rng = .Cells(Rows.Count, 1).End(xlUp).Row - 1
                If Rng > 0 Then
                    Sheets("Consolidated").Cells(Rows.Count, 1).End(xlUp)(2).Resize(Rng) = .Name
                    Sheets("Consolidated").Cells(Rows.Count, 2).End(xlUp)(2).Resize(Rng, 4) = .Range("A2").Resize(Rng, 4).Value
                End If
            End If
        End With
    Next
    .Columns("A:Z").EntireColumn.AutoFit
End With
End Sub

[indianhp]

Hi oeldere,

Its working great as expected thanks for such a quick response. Actually I found its amazing that i also have a column in sheet which shows that from which i get the data. So, for any audit i can directly search that in perticular sheet from which that data belongs...

Thank you very much..

[oeldere]

Thanks for the reply.

Glad I could help.