• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot Macro with Dynamic range of cells

Hi , Have macro code in place "summary.xlsb" that creates a report by fetching data from various files inside the folder and producing a report . User can click the summary button in the summary sheet to produce the results. The number of rows is not fixed in the summary and depends on the input files placed. There is a need to place pivot button adjacent /near Summary button ( should be able to move the buttons) .

Request your kind help in creating a pivot for each of the summary sheet in the folder .

month , country in row labels and sums of the systems like ( payment , collections , core banking etc ) in the column labels. Macro code is placed here

Private Sub Demo()
Const E = ";Extended Properties=""Excel 12.0;HDR=Yes"""
Dim oCn As Object, P$, F$, V, C%, R&, K$, VA
Me.UsedRange.Offset(1).Clear
P = ThisWorkbook.Path & "\"
F = Dir(P & "?? *.xlsx")
If F = "" Then Beep: Exit Sub Else [E2].Value = " Wait …"
P = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & P
Set oCn = CreateObject("ADODB.Connection")
With CreateObject("Scripting.Dictionary")
While F > ""
oCn.Open P & F & E
With oCn.Execute("SELECT month,currency,[gl account],revenue FROM [Sheet1$]")
V = .GetRows
.Close
End With
oCn.Close
C = -(F Like "*EBBS*")
F = vbTab & Left(F, 2) & vbTab
For R = 0 To UBound(V, 2)
K = V(0, R) & F & V(1, R) & vbTab & V(2, R)
If .Exists(K) Then VA = .Item(K) Else ReDim VA(1)
VA(C) = VA(C) + V(3, R)
.Item(K) = VA
Next
F = Dir
Wend
R = .Count
Set oCn = Nothing
[A2].Resize(R).Value = Application.Transpose(.Keys)
[A2].Resize(R).TextToColumns Tab:=True
[E2:F2].Resize(R).Value = Application.Index(.Items, 0)
.RemoveAll
End With
With [A2:F2].Resize(R).Columns
.Item("E:G").NumberFormat = Cells(7).NumberFormat
.Item(8).NumberFormat = Cells(8).NumberFormat
.Item(7).Formula = "=F2-E2"
.Item(8).Formula = "=IF(F2=0,""-"",E2/F2)"
.Item(9).Formula = "=IF(E2=F2,""R"",""Not r"")&""econcilled"""
.Item("G:I").Formula = .Item("G:I").Value
End With
End Sub
 

Attachments

  • Country system files.zip
    95.9 KB · Views: 1
Back
Top