consolidate sheets vertical to horizontal (but only certain cells, yellow filled)

[erdow]

Hi to all experts,

I have a workbook with multiple sheets with same structure. What i want to do is consolidate only the specific cells with same header **using vba**(from vertical to horizontal)
Report is between A1:AN96 veritically, i need a1 and then horizotally AL6-AL20-AL24-AL26-................
If it is difficult to get only yellow cells then you can get costumer name to A1 and then AL&AM (i just need yearly data column not monthlys) next to it (b1-c1-d1-......) and the other costumers after the other (a2-a3-a4....) and i can filter according to my needs..
There ise a sample file on the link
I need to consolidate yellow cells..
Thanks a lot in advance...

 

[DanteAmor]

There ise a sample file on the link

You didn't put the link.

 

[erdow]

You didn't put the link.

sample consolidate.xls

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl

sorryy

 

[erdow]

You didn't put the link.

Dante Amor didnt solve it yet?
İts important for me, I would be grateful if you could solve it ?

 

[Marc L]

Hi, according to your attachment for starters you must paste this VBA demonstration to the Consolidate worksheet module :​

Sub Demo1()
    Const E = """)"
    Dim S$, R&
    S = """" & Chr$(39):  S = "=INDIRECT(" & S & """&$A2&" & S & "!$"
    UsedRange.Offset(1).Clear
    Application.ScreenUpdating = False
    For R = 1 To Index - 1:  Cells(R + 1, 1).Value2 = Sheets(R).Name:  Next
    Range(Replace("B2:C#,E2:E#,G2:G#,J2:J#", "#", R)).NumberFormat = "#,##0_W"
    Range(Replace("D2:D#,I2:I#", "#", R)).NumberFormat = "#,##0.00_W"
    Range(Replace("F2:F#,H2:H#,K2:K#", "#", R)).NumberFormat = "0.00%_W"
    Range("B2:K" & R).Formula = Array(S & "AL$6" & E, S & "AL$20" & E, S & "AL$24" & E, S & "AL$26" & E, S & "AM$26" & E, _
                                     S & "AL$30" & E, S & "AM$30" & E, S & "AL$40" & E, S & "AL$63" & E, S & "AM$63" & E)
    Application.ScreenUpdating = True
End Sub

 

[Marc L]

Optimization for S as a constant :​

Sub Demo1()
    Const E = """)", S = "=INDIRECT(""'""&$A2&""'!$"
    Dim R&
    UsedRange.Offset(1).Clear
    Application.ScreenUpdating = False
    For R = 1 To Index - 1:  Cells(R + 1, 1).Value2 = Sheets(R).Name:  Next
    Range(Replace("B2:C#,E2:E#,G2:G#,J2:J#", "#", R)).NumberFormat = "#,##0_W"
    Range(Replace("D2:D#,I2:I#", "#", R)).NumberFormat = "#,##0.00_W"
    Range(Replace("F2:F#,H2:H#,K2:K#", "#", R)).NumberFormat = "0.00%_W"
    Range("B2:K" & R).Formula = Array(S & "AL$6" & E, S & "AL$20" & E, S & "AL$24" & E, S & "AL$26" & E, S & "AM$26" & E, _
                                     S & "AL$30" & E, S & "AM$30" & E, S & "AL$40" & E, S & "AL$63" & E, S & "AM$63" & E)
    Application.ScreenUpdating = True
End Sub

 

[Marc L]

(removed)

 

[Marc L]

A variation :​

Sub Demo1v()
  Const F = "#;='#'!$AL$6;='#'!$AL$20;='#'!$AL$24;='#'!$AL$26;='#'!$AM$26;='#'!$AL$30;='#'!$AM$30;='#'!$AL$40;='#'!$AL$63;='#'!$AM$63"
    Dim L&, V, R&
        UsedRange.Offset(1).Clear
   With Application
       .ScreenUpdating = False
    For L = 1 To Index - 1:  Rows(L + 1).Columns("A:K").Formula = .Index(Split(Replace(F, "#", Sheets(L).Name), ";"), 1, 0):  Next
        V = [{"B2:R#,E2:E#,G2:G#,J2:J#","#,##0_W";"D2:D#,I2:I#","#,##0.00_W";"F2:F#,H2:H#,K2:K#","0.00%_W"}]
    For R = 1 To UBound(V):  Range(Replace(V(R, 1), "#", L)).NumberFormat = V(R, 2):  Next
       .ScreenUpdating = True
   End With
End Sub

 

[DanteAmor]

Dante Amor didnt solve it yet?
İts important for me, I would be grateful if you could solve it

I already checked your file. If your sheet "consolidate", it is at the end of all sheets.
Select the consolidate sheet and run the following macro:

Sub ConsolidateSheets()
  Dim i As Long
  For i = 1 To Sheets.Count - 1
    Range("A" & i + 1).Value = Sheets(i).Name
    Range("B" & i + 1 & ":E" & i + 1 & ",G" & i + 1 & ",I" & i + 1 & ":J" & i + 1).Formula = "=VLOOKUP(R1C,'" & Sheets(i).Name & "'!C9:C38,30,0)"
    Range("F" & i + 1 & ",H" & i + 1 & ",K" & i + 1).Formula = "=VLOOKUP(R1C[-1],'" & Sheets(i).Name & "'!C9:C39,31,0)"
  Next
  Range("B2:K" & i + 1).Value = Range("B2:K" & i + 1).Value
End Sub

 

[DanteAmor]

Simplify i + 1

Sub ConsolidateSheets()
  Dim i As Long, n As Long
  For i = 1 To Sheets.Count - 1: n = i + 1
    Range("A" & n).Value = Sheets(i).Name
    Range(Replace("B#:E#,G#,I#:J#", "#", n)).Formula = "=VLOOKUP(R1C,'" & Sheets(i).Name & "'!C9:C38,30,0)"
    Range(Replace("F#,H#,K#", "#", n)).Formula = "=VLOOKUP(R1C[-1],'" & Sheets(i).Name & "'!C9:C39,31,0)"
  Next
  Range("B2:K" & n).Value = Range("B2:K" & n).Value
End Sub