Need urgently to find a macro in excel report across sheets [Solved/Closed]

Question

Need urgently to find a macro in excel report across sheets [Solved/Closed]

Report

moisdois

Posts: 4
Registration date: Monday July 6, 2015
Status: Member
Last seen: July 7, 2015

- Jul 6, 2015 at 05:40 AM
moisdois

Posts: 4
Registration date: Monday July 6, 2015
Status: Member
Last seen: July 7, 2015

- Jul 7, 2015 at 12:34 PM

Hi,

Anyone a solution?

I need a macro that will do the following:

1. Search for a word, a number, ... across all the worksheets in the workbook ( this I can do by the simple "find" function)

2. But then... display the results in a separate new worksheet, where the whole LINE of the found string will be copied, one after the other, giving me a "report".

For example, If I have the word "TEXACO" as supplier in different sheets, with next box the date, next box the date of invoice, next box details etc, so when i press the search "TEXACO" i will get automatically lined up in a new sheet ( a report) one line under the other all the info found in the different sheets with in first box texaco, and next to it all the info from that sheet, then down , the same info found from next sheet etc..

Anyone can help me with that ?

Kind Regards,

Sam

Need urgently to find a macro in excel report across sheets
Excel - Macro to format report - How-To - Excel
How to Use Macros in Excel? - How-To - Excel
Automatically run macro in excel when cell changes - How-To - Excel
Can someone please write me a macro for excel ✓ - Forum - Excel
Excel Macro Copy data to next sheet using < ✓ - Forum - Excel

3 replies

Answer 2 / 3

TrowaD

Posts: 2693
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: January 18, 2021

455
Jul 6, 2015 at 10:40 AM

Hi Sam,

A few questions:

1. How did you name your "report" sheet?
2. In which column can the search word be found?

Best regards,
Trowa

Report

moisdois

Posts: 4
Registration date: Monday July 6, 2015
Status: Member
Last seen: July 7, 2015

Jul 6, 2015 at 10:47 AM

I did not name any report sheet yet, i would like after a search result, to CREATE AND DISPLAY the results in a new sheet.

I have now a workbook called "finance", with about 20 worksheets inside , each worksheet named difefrently, and in each worksheet about 6 columns with data ( name, date, amount, invoice nr, data, ...) and then about 100 lines with data.

What i want to do is to be able to search across the whole workbook for a certain string ( which sometimes it will find in a worksheet in the first column, sometimes in the second column,.. ) and once found ( it will usually find multiple times ) to have the results TRANSFERRED in a new sheet automatically, where it takes the whole LINE of the found data and copies it in the new sheet )

Like this when i search for invoices of , let say , a certain date, and it finds 17 answers corresponsing ( in different worksheets in the workbook) it will take those 17 dates with all the info left and right ON THAT LINE, and put those 17 lines nicely one after another in a new sheet, thus creating a report

Answer 3 / 3

TrowaD

Posts: 2693
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: January 18, 2021

455
Jul 7, 2015 at 11:18 AM

Hi Sam,

Good to see you like it.

The code below has been amended to add the sheet name at the end of the pasted row.
And as a bonus the width of the columns will be adjusted according to the source.

Here is the code:

Sub RunMe()
Dim sh As Worksheet
Dim sValue As String
Dim sCell As Range
Dim lRow As Integer

sValue = InputBox("For which value would you like to create a report:", "Search value")

If sValue = vbNullString Then
    MsgBox "You forgot to enter a search value. Run the code again to give it another try."
    Exit Sub
End If

Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "Report for " & sValue

For Each sh In Worksheets
    If sh.Name <> "Report for " & sValue Then
        Sheets(sh.Name).Select
        Set sCell = Sheets(sh.Name).UsedRange.Find(sValue)
        If Not sCell Is Nothing Then
            firstAddress = sCell.Address
            Do
                lRow = Sheets("Report for " & sValue).Range("A" & Rows.Count).End(xlUp).Row + 1
                sCell.EntireRow.Copy
                Sheets("Report for " & sValue).Range("A" & lRow).PasteSpecial
                Sheets("Report for " & sValue).Cells(lRow, Columns.Count).End(xlToLeft).Offset(0, 1).Value = sh.Name
                Set sCell = Sheets(sh.Name).UsedRange.FindNext(sCell)
            Loop While Not sCell Is Nothing And sCell.Address <> firstAddress
        End If
    End If
Next sh

Sheets("Report for " & sValue).Range("A" & lRow).PasteSpecial Paste:=xlPasteColumnWidths
    
Application.CutCopyMode = False
    
End Sub

Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.

Report

moisdois

Posts: 4
Registration date: Monday July 6, 2015
Status: Member
Last seen: July 7, 2015

Jul 7, 2015 at 12:34 PM

Thank you so much ! Works perfectly ! U have really been so much of help !

Ask a question

I did not name any report sheet yet, i would like after a search result, to CREATE AND DISPLAY the results in a new sheet.

I have now a workbook called "finance", with about 20 worksheets inside , each worksheet named difefrently, and in each worksheet about 6 columns with data ( name, date, amount, invoice nr, data, ...) and then about 100 lines with data.

What i want to do is to be able to search across the whole workbook for a certain string ( which sometimes it will find in a worksheet in the first column, sometimes in the second column,.. ) and once found ( it will usually find multiple times ) to have the results TRANSFERRED in a new sheet automatically, where it takes the whole LINE of the found data and copies it in the new sheet )

Like this when i search for invoices of , let say , a certain date, and it finds 17 answers corresponsing ( in different worksheets in the workbook) it will take those 17 dates with all the info left and right ON THAT LINE, and put those 17 lines nicely one after another in a new sheet, thus creating a report
Thank you so much ! Works perfectly ! U have really been so much of help !

TrowaD · Accepted Answer · 2015-07-06T11:44:34-0400

Hi Sam,

Try the code below, which will ask you for a value to search for. The code will then loop through all the sheets looking for this value. Each row containing the search value will then be copied to a newly created sheet called "Report for [Search value]".

Give it a go and hopefully you will like the result.

Here is the code:

Sub RunMe()
Dim sh As Worksheet
Dim sValue As String
Dim sCell As Range

sValue = InputBox("For which value would you like to create a report:", "Search value")

If sValue = vbNullString Then
    MsgBox "You forgot to enter a search value. Run the code again to give it another try."
    Exit Sub
End If

Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "Report for " & sValue

For Each sh In Worksheets
    If sh.Name <> "Report for " & sValue Then
        Sheets(sh.Name).Select
        Set sCell = Sheets(sh.Name).UsedRange.Find(sValue)
        If Not sCell Is Nothing Then
            firstAddress = sCell.Address
            Do
                sCell.EntireRow.Copy Sheets("Report for " & sValue).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                Set sCell = Sheets(sh.Name).UsedRange.FindNext(sCell)
            Loop While Not sCell Is Nothing And sCell.Address <> firstAddress
        End If
    End If
Next sh
    
End Sub

Best regards,
Trowa

Looks awesome, gonna test it further, but first glance looks GREAT, you're genius!

Just a pitty that the columns in the report are resized small so i have to enlarge each time, but i guess there is nothing to do about that.

BTW , is there a way that on each row placed in the report there should also be added automatically 1 column at the end with the name of the worksheet where it was taken from so i know where it comes from ( as i get now indeed what i wanted! all texaco bills, but dont know good from WHERE it was taken from..)

So if it would mention at the end of each row the name of the worksheet then it would really be perfect ..

But THANKS SO MUCH ANYWAY !!

Need urgently to find a macro in excel report across sheets [Solved/Closed]

3 replies

Your reply

Subscribe To Our Newsletter!