Log In
Register
Home
May 8, 2018
Offline
Dear Mynda/Purfleet or anyone that may assist
See attached document. There are two sheets, namely Data and Criteria. Please look at my Criteria and kindly assist with writing some VBA Code to make this work.
I know I can simply run a Pivot Table to make this work but I do not want to overwrite the existing data with new data then refresh the Pivot whereas I can just run a Macro from my Personal Workbook and saved on the ribbon.
Regards,
Amien
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Online
Hi Amien,
A pivot from that data will never work, structure is simply wrong.
Mixing source data with a visual is a bad idea, any report that will be needed will need a significant effort.
Best way is to use power query or even vba to reorganize data into a normal tabular structure, this will give you much more flexibility to build many more different reports based on the same data.
May 8, 2018
Offline
Hi Catalin
Thanks for the answer. However, I am asking for some VBA code and the steps I need is in Sheet 2 of my workbook.
Thanks.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Online
Try these codes:
Sub HideUnhide()
Dim i As Long, HideRng As Range, HideStatus As Boolean
i = 1
Do Until Len(ActiveSheet.Cells(1, i).Value) = 0
If ActiveSheet.Cells(1, i).Value Like "??? ####" Then
If HideRng Is Nothing Then
Set HideRng = ActiveSheet.Cells(1, i)
Else
Set HideRng = Union(HideRng, ActiveSheet.Cells(1, i))
End If
End If
i = i + 1
Loop
'hide or unhide
HideStatus = HideRng.Cells(1).EntireColumn.Hidden
HideRng.EntireColumn.Hidden = Not HideStatus
End Sub
Sub AddSubtotals()
Dim i As Long, DataRng As Range, LastRow As Long
i = 1
LastRow = ActiveSheet.Cells.Find("*", ActiveSheet.Cells(1), , , xlByRows, xlPrevious).Row
Do Until Len(ActiveSheet.Cells(1, i).Value) = 0
If ActiveSheet.Cells(1, i).Value Like "FY ####" Then
ActiveSheet.Cells(LastRow + 1, i).FormulaR1C1 = "=SUM(R[-" & LastRow - 1 & "]C:R[-1]C)"
End If
i = i + 1
Loop
End Sub
For freezing rows, i suggest starting the code recorder, you will get the base code needed.
1 Guest(s)
