• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Merging data from various worksheets

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

Attachments

  • New Query.xlsx
    9.5 KB · Views: 0
Check this....

Code:
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
 

Attachments

  • New Query.xlsm
    17.5 KB · Views: 1
Back
Top