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

[joe_pilgrim]

Hello VBA Express Community!

I am new, not just to the forum but to VBA as well. While I have been searching online for some VBA code, I believe this scenario mirrors to what I am currently facing.

I have a "Report" workbook with only one worksheet named "Summary". In this worksheet, column A and column B contain the data that needs to be searched across the "Master" workbook that holds all the data in various worksheets that needs to be copied/pasted. When search is found from within worksheet of "Master" workbook, copy respective rows unto "Summary" worksheet of "Report" workbook in this order:

= D3 (worksheet from Master workbook) to D3 (Summary worksheet of Report workbook)

= E3 (worksheet from Master workbook) to G3 (Summary worksheet of Report workbook)

= F3 (worksheet from Master workbook) to H3 (Summary worksheet of Report workbook)

= G3 (worksheet from Master workbook) to O3 (Summary worksheet of Report workbook)

= H3 (worksheet from Master workbook) to P3 (Summary worksheet of Report workbook)

= I3 (worksheet from Master workbook) to U3 (Summary worksheet of Report workbook)

= J3 (worksheet from Master workbook) to V3 (Summary worksheet of Report workbook)

= K3 (worksheet from Master workbook) to W3 (Summary worksheet of Report workbook)

= L3 (worksheet from Master workbook) to X3 (Summary worksheet of Report workbook)

= M3 (worksheet from Master workbook) to Y3 (Summary worksheet of Report workbook)

= N3 (worksheet from Master workbook) to Z3 (Summary worksheet of Report workbook)

This same process will continue till all matches have been found. Also, if no match is found to skip that particular search and move to the next one.

I hope I have provided you with some useful information, otherwise please let me know. Thank you for your time and patience mate!

Cheers!

JP :hi:

[pmeenak]

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?

My question is not related to this thread. I wish to know, how you are able to attach an xls sheet with the thread? Could you tell me? I tried, but not able to attach a file.