Display all worksheets in a sheet?

[Horby]

Hi,

Can I view all the worksheets in a sheet?

Display with some of the data of every sheet?

Example image

 

[Horby]

Simple example
test.xlsx downloaden

 

[Oeldere]

Below is the code I'm using to get all data in the consolidated sheet.

You can activate the macro using ALT + F8 and choose integratie_Oeldere_revisted_vs3()

Then choose run macro.

After that you can use a pivot table to analyze the data.

I now will upload the file and post the link here in this forum.

http://www.mijnbestand.nl/Bestand-4NCDILC7HZEA.xlsm

Sub integratie_Oeldere_revisted_vs3()

'I got a lot of help from AB33, to get this code working; thanks for that AB33.

Dim wsTest As Worksheet

'check if sheet "Consolidated" already exist

Const strSheetName As String = "Consolidated"
 
Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0
 
If wsTest Is Nothing Then
    Worksheets.Add.Name = strSheetName
End If

With Sheets("Consolidated")
    .UsedRange.ClearContents
    .Range("A1:C1").Value = Array("sheet", "Value", "Descripson")
    For Each sh In Sheets
        With sh
            If .Name <> "Consolidated" And .Name <> "Summary" And .Name <> "Output" And .Name <> "PivotTable" Then
                 LR = .Cells(.Rows.Count, 1).End(xlUp).Row
                If LR >= 2 Then
                   Rng = .Cells.Find("*", , , , xlByRows, xlPrevious).Row - 1
                   NR = Sheets("Consolidated").Cells.Find("*", , , , xlByRows, xlPrevious).Row + 1
                   If Rng > 0 Then
                    Sheets("Consolidated").Cells(NR, 1).Resize(Rng) = .Name
                    Sheets("Consolidated").Cells(NR, 2).Resize(Rng, 8) = .Range("A2").Resize(Rng, 8).Value
                  End If
               End If
            End If
        End With
    Next
    On Error Resume Next
    .Range("C2:C" & .Rows.Count).SpecialCells(4).EntireRow.Delete
    .Columns("A:Z").EntireColumn.AutoFit
End With
End Sub

 

[Horby]

Very excellent ..
Uploaded file again, how can I put it to these conditions?

test.xlsx downloaden

 

[Oeldere]

In the code I changed this line

.Range("K2:K" & .Rows.Count).SpecialCells(4).EntireRow.Delete

After that a non VBA solution

O2

=INDIRECT("K"&(Row()-2)*5+Column()-13)

Maybe another member can do that with VBA.

Advice:

You work with merged cells, you get in trouble with it sooner o later.

I now will upload the file.

http://www.mijnbestand.nl/Bestand-Z86P4AALU8IY.xlsm