Query multiple sheets and create report

[tbutori]

Hello,

I am new to the forum but have used many of the suggestions posted. Thanks to everyone for sharing your knowledge!

I have a workbook with over 100 worksheets. 90 of the worksheets contain equipment check sheets with a section at the bottom of each sheet where follow-up required can be entered. The column positions of each sheet is the same but the row(s) can be anywhere on the sheet. I would like to have all of these follow-ups from each of the 90 sheets transferred to on one report worksheet named "Follow-up".

I don't know how to write VBA code. I tried to use the VLookup function by adding a formula in column A that will return the value of 1 if there is follow-up information in that row. Using the VLookup for every row on every sheet and make it dynamic in case more rows are needed will take forever.
I have watched videos that will transfer the data from multiple sheets to one sheet but the data must be in a table and it must start at the same cell on every sheet. I don't want to change these sheets into tables and the starting cell can be anywhere on the sheet.
I thought Power Query might be the answer but I am new to that as well and have only used it a few times.

I would post an example but I'm not sure how to do that.
Thanks for any help you can provided.

 

[Logit]

.
It would be best to post a sample of your workbook for review. You don't have to included all of the data, just a sufficient amount to understand where the values are located and how they might
vary from sheet to sheet.

So long as the value to be located is identical on each sheet - spelled the same - there is either a VBA or Formula method to accomplish your goal.

You'll need to post your sample workbook on a website like DROPBOX or similar. This Forum does not allow for posting of workbooks.

 

[tbutori]

.
It would be best to post a sample of your workbook for review. You don't have to included all of the data, just a sufficient amount to understand where the values are located and how they might
vary from sheet to sheet.

So long as the value to be located is identical on each sheet - spelled the same - there is either a VBA or Formula method to accomplish your goal.

You'll need to post your sample workbook on a website like DROPBOX or similar. This Forum does not allow for posting of workbooks.

Thanks Logit. I have posted the workbook to: https://www.dropbox.com/s/o1lqvd8jm...eance Service Order Master 05-08-18.xlsm?dl=0
There is no sensitive data in the workbook. All of the follow-up information is located between worksheets "a" and "b" in columns B:K with the same headings, just located in different rows.

 

[tbutori]

Logit - I just uploaded a more current version: https://www.dropbox.com/preview/Example Workbook.xlsm?role=personal

 

[Logit]

.
Bad link

 

[tbutori]

Sorry Logit... I am new to a lot of things here. I just set up the Dropbox account. I shared the link: https://www.dropbox.com/s/bs23h72wruvsfiv/Excel Example.xlsm?dl=0
Can you see it now?

 

[Logit]

.
Is it this section on the sheets that you want copied over to the FOLLOW UP sheet ?



[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

79​

[/td][td][/td][td=bgcolor:#D8D8D8][/td][td=bgcolor:#D8D8D8][/td][td=bgcolor:#D8D8D8][/td][td=bgcolor:#D8D8D8]Follow-Up Required[/td][td=bgcolor:#D8D8D8][/td][td=bgcolor:#D8D8D8][/td][td=bgcolor:#D8D8D8][/td][td=bgcolor:#D8D8D8][/td][td=bgcolor:#D8D8D8][/td][td=bgcolor:#D8D8D8][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

80​

[/td][td][/td][td=bgcolor:#D8D8D8]

Location​

[/td][td=bgcolor:#D8D8D8]

​

[/td][td=bgcolor:#D8D8D8][/td][td=bgcolor:#D8D8D8]Notes[/td][td=bgcolor:#D8D8D8][/td][td=bgcolor:#D8D8D8][/td][td=bgcolor:#D8D8D8]

Date Diagnosed​

[/td][td=bgcolor:#D8D8D8][/td][td=bgcolor:#D8D8D8]

VA Status​

[/td][td=bgcolor:#D8D8D8]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

81​

[/td][td]

1​

[/td][td]Building 40 AHU-40[/td][td]Recommend program change with freeze stat activation.[/td][td][/td][td][/td][td][/td][td][/td][td]

2/14/2018​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

82​

[/td][td]

1​

[/td][td]Building 40 AHU-40[/td][td]N duct static transducer located outside rm 215. Adjust drawings.[/td][td][/td][td][/td][td][/td][td][/td][td]

2/15/2018​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

83​

[/td][td]

1​

[/td][td]Building 40 AHU-40[/td][td]S duct static transducer is located outside rm 229. Adjust drawings.[/td][td][/td][td][/td][td][/td][td][/td][td]

2/15/2018​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

84​

[/td][td]

1​

[/td][td]Building 40 AHU-40[/td][td]Heater #2 1/3 valve actuator not responding & steam valve leaking. Repair.[/td][td][/td][td][/td][td][/td][td][/td][td]

2/22/2018​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

85​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[tbutori]

Yes, for all 90 sheets.

 

[Logit]

.
This macro, paste into a Routine Module, will copy the "Follow-Up Required" section from each of the sheets containing same. It copies the 6 rows underneath
the title "Follow-Up Required" and pastes the data into the "Follow-Up" sheet.

One problem with your workbook. The LOCATION in the Follow-Up Required table in each of the sheets is dependent upon formulas. In order for that data to be
copied/pasted to the Follow-Up sheet as well, you will need to manually enter the Location data into those cells as the user is entering the other information.
Presently, because of the formulas, the location data is not being transferred as desired.

Option Explicit


Sub SrchCpyPaste()
    Dim Rws As Long, Rng As Range, ws As Worksheet, sh As Worksheet, c As Range, x As Integer
    Set ws = Worksheets("Follow-up")  'specify sheet name here to paste to
    x = 3   'begins pasting in Sheet 1 on row 2
    Application.ScreenUpdating = 0
    For Each sh In Sheets
        If sh.Name <> ws.Name Then
            With sh
                Rws = .Cells(Rows.Count, "B").End(xlUp).Row 'searches Col D all sheets
                Set Rng = .Range(.Cells(1, "B"), .Cells(Rws, "B"))
                For Each c In Rng.Cells
                    'If c.Value = "Follow-Up Required" Then  'searches for term NO
                    '    c.EntireRow.Copy Destination:=ws.Cells(x, "A")
                    '    x = x + 1
                    'End If
                    
                   If Not IsError(c) Then
                        If c.Value = "Follow-Up Required" Then  'searches for term NO
                            c.Resize(7).EntireRow.Copy Destination:=ws.Cells(x, "A")
                            x = x + 7
                        End If
                    End If
                    
                Next c
            End With
        End If
    Next sh
End Sub

 

[tbutori]

Thanks so much Logit! VBA is amazing.
I did paste the module in and it does work... thanks again.
Just wondering if we can tweak it a little?
I don't need the header to come if for every sheet - one header on the Follow-up sheet will work.
Also, can I make it dynamic so if more rows are added on a sheet, they will transfer as well?

Location Notes Date Diagnosed Status

Then fill in under that one heading at the next available row?
It doesn't look like the code is dependent on my "Check" formula with the value 1 if there is data. Can I remove that formula?
I will have them fill in the location manually so that value will transfer.

Thanks again. I really appreciate your help.