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

erdow

New Member
Joined
May 30, 2021
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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...
 

erdow

New Member
Joined
May 30, 2021
Messages
28
Office Version
  1. 365
Platform
  1. Windows
Optimization for S as a constant :​
VBA Code:
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
Thank you so much Marc..
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

erdow

New Member
Joined
May 30, 2021
Messages
28
Office Version
  1. 365
Platform
  1. Windows
Simplify i + 1

VBA Code:
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
Dante Amor thank you so much, i am using this version and works fine. If you can show to how can i exclude one or more specific sheets from consolidation i will be gratefull...
 
Upvote 0

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
15,831
Office Version
  1. 2013
Platform
  1. Windows
how can i exclude one or more specific sheets
Try this:

VBA Code:
Sub ConsolidateSheets()
  Dim i As Long, n As Long
  For i = 1 To Sheets.Count - 1: n = i + 1
    Select Case Sheets(i).Name
      Case "Sheet1", "Sheet2", "etc" 'exclude one or more specific sheets
      Case Else
      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)"
    End Select
  Next
  Range("B2:K" & n).Value = Range("B2:K" & n).Value
End Sub
 
Upvote 0
Solution

erdow

New Member
Joined
May 30, 2021
Messages
28
Office Version
  1. 365
Platform
  1. Windows
Try this:

VBA Code:
Sub ConsolidateSheets()
  Dim i As Long, n As Long
  For i = 1 To Sheets.Count - 1: n = i + 1
    Select Case Sheets(i).Name
      Case "Sheet1", "Sheet2", "etc" 'exclude one or more specific sheets
      Case Else
      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)"
    End Select
  Next
  Range("B2:K" & n).Value = Range("B2:K" & n).Value
End Sub
Perfect :) You made my task so easy... Thank you so much again and again and again.....
 
Upvote 0

Forum statistics

Threads
1,186,627
Messages
5,958,873
Members
438,379
Latest member
impuce

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top