Extracting data from multiple worksheets, from differing number of rows

[leeboh]

Hi All,

I am working on a large project to determine all of the different fields that appear within a particular software system. I have a worksheet for every screen, with each worksheet detailing the Field names, along with other information.

I am looking to create a Master worksheet if it were (or summary worksheet) that shows all of the fields that appear across all of the different worksheets (screens).

The tricky part here is:

All of the Field names defined, start from Cell B7, but the total number of fields, i.e. the total rows the Field names spans, varies from worksheet to worksheet. And to make matters more complex, at the end of the listed fields, there are 2 blanks and then there is additional information continued, such as Links and Buttons information (which is not needed for the Master/Summary worksheet, but is needed in the individual worksheet it currently exists in).

Any thoughts of a formula on this?

Additionally, I also need to state which worksheet (Screen Name) the data was taken from. I can then later run a query of sorts to show duplicates as 1 result, detailing the multiple worksheets (Screen Names) in which they appear.

Any help would be greatly appreciated, this would solve a massive roadblock I'm facing with a large project at work.

Many thanks,



Lee

 

[Fluff]

Hi & welcome to the board.
How about this

Sub ListFields()

    Dim Ws As Worksheet
    Dim MstSht As Worksheet
    Dim NxtRw As Long
    
    Set MstSht = Sheets("[COLOR=#0000ff]Master[/COLOR]")
    For Each Ws In Worksheets
        If Not Ws.Name = "[COLOR=#0000ff]Master[/COLOR]" Then
            NxtRw = MstSht.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
            With Ws.Range("B7", Ws.Range("B7").End(xlDown))
                .Copy MstSht.Range("A" & NxtRw)
                MstSht.Range("B" & NxtRw).Resize(.Rows.Count).Value = Ws.Name
            End With
        End If
    Next Ws

End Sub

Changing the sheet name in blue to match your sheet name

 

[leeboh]

Hi,

Thank you for your quick response!

It seems to work relatively well, although it struggles where there is only 1 field record to return - it seems to pull in a few additional rows, blanks included.

Additionally, I'm having an issue where a worksheet which was no relevant data - the code has pulled over 1million rows of blank data into the Master, listing the worksheet name each time.

It's close, but not quite there yet.

Thanks,
Lee

 

[Fluff]

When you say

Additionally, I'm having an issue where a worksheet which was no relevant data

does this mean that B7 will be blank?, or is it more a matter of certain sheets should not be checked?

 

[leeboh]

In 99% of cases, yes that would be correct.

 

[leeboh]

99% of cases *where there is no relevant data*

 

[Fluff]

In that case try

Sub ListFields()

    Dim Ws As Worksheet
    Dim MstSht As Worksheet
    Dim NxtRw As Long
    
    Set MstSht = Sheets("Master")
    For Each Ws In Worksheets
        If Not Ws.Name = "Master" Then
            NxtRw = MstSht.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
            If Len(Ws.Range("B7")) > 0 Then
                If Len(Ws.Range("B8")) = 0 Then
                    Ws.Range("B7").Copy MstSht.Range("A" & NxtRw)
                    MstSht.Range("B" & NxtRw).Value = Ws.Name
                Else
                    With Ws.Range("B7", Ws.Range("B7").End(xlDown))
                        .Copy MstSht.Range("A" & NxtRw)
                        MstSht.Range("B" & NxtRw).Resize(.Rows.Count).Value = Ws.Name
                    End With
                End If
            End If
        End If
    Next Ws

End Sub

for the 1%, is there any way of determining if there is anything to be copied?

 

[leeboh]

For now, as the impact is minimal - I have just added text 'None' in cells B7. This bit of code works incredibly well - thank you so much for your efforts, massively impressed

 

[Fluff]

Glad to help & thanks for the feedback

 

[leeboh]

Just in case an issue does come up, is there a way I can exclude a worksheet in the code, from returning data to the Master worksheet?