Merging data from various worksheets

[Sarfraz Khan]

Dear sir,
I have workbook and 90 worksheets datewise in that workbook, there are amounts in H column and txn no in G column i want those all amounts in new sheet no 91. What would the best formula instead of copy paste.

 

[Sarfraz Khan]

Dear sir,
I have workbook and 90 worksheets datewise in that workbook, there are amounts in H column and txn no in G column i want those all amounts in new sheet no 91. What would the best formula instead of copy paste.

 

[Deepak]

I am not sure about formula but if agree then it's easy with VBA.

 

[Sarfraz Khan]

Can you please help me with the VBA code.

 

[Deepak]

Can you please help me with the VBA code.

Ok. I am moving the thread to vba.

 

[Deepak]

Check this....

Sub get_data()
Dim ws As Worksheet, cFind As Range

For Each ws In ThisWorkbook.Worksheets

If ws.Name <> "I want Result like this." Then

    Set cFind = ws.Cells.Find("txn #")
       
        If Not cFind Is Nothing Then
           
            With cFind.CurrentRegion
                .Offset(1).Resize(.Rows.Count - 1).Copy
            End With

            With Sheet4.Range("k5").CurrentRegion
                .Offset(.Rows.Count).PasteSpecial xlPasteValues
            End With
       
        End If
End If

Application.CutCopyMode = False
Set cFind = Nothing

Next

End Sub