Find column value in multiple sheet and copy Row to Summary

[tlchan]

Hi there,
I try to prepare master summary for various reports under 1 master sheet where Column A with respective sheet name, Column B the value to find from each column of each sheet as no of row in each sheet may not be the same. With the find value in column B, copy entire row to master sheet named "Summary". The code seems does not work as expected.


My expected result shown in summary sheet in red font. Could anyone help?

[omp001]

Hi.
try this code and see if it helps

Sub findcopyrow()
Dim WhatFor As String, LR As Long, ws As Worksheet, k As Range
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
With ws
If Not .Name = "Summary" _
And Not .Name = "ReportDate" Then
WhatFor = "Findvalue"
Set k = .Columns(1).Find(what:=WhatFor)
If Not k Is Nothing Then
.Cells(k.Row, 1).Resize(, 9).Copy
With Sheets("summary")
LR = .Range("A" & Rows.Count).End(xlUp).Row
.Cells(LR + 1, 2).PasteSpecial _
xlPasteValuesAndNumberFormats
.Cells(LR + 1, 1) = ws.Name
End With
End If
End If
End With
Next ws
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

[tlchan]

Thanks omp001 for your help. The code works great!

Now I wish to copy the summary sheet from other workbook (Master workbook_sample) and rename the summary sheet with sheet name from cell D2. Example of my code under Master workbook_sample.xls attached.

Any assistance is appreciated.

Thanks

[omp001]

Hi.
1. I considered that 'D2' of the active sheet holds the name to be given to the copied sheet when you'll run this macro
2. adjust in the macro the full path of the file to be opened

Sub OpenCopyClose()
Dim otherFile As Workbook, nameSh As String
Application.ScreenUpdating = False
nameSh = Format(ThisWorkbook.ActiveSheet.[D2].Value, "dd-mmm-yy")
Set otherFile = Workbooks.Open("C:\MyFolder\MyFileName.xls")
otherFile.Sheets("summary").Copy Before:=ThisWorkbook.Sheets(1)
ActiveSheet.Name = nameSh
otherFile.Close False
Application.ScreenUpdating = True
End Sub

[tlchan]

Thanks again omp001for your suggested code. Tested but only workable when amended the following



ActiveSheet.Name = nameSh

to


ActiveSheet.Name=Format(ThisWorkbook.ActiveSheet.[D2].Value, "dd-mmm-yy")


:hi: