VBA Codes of Pivot Table

User avatar
PRADEEPB270
3StarLounger
Posts: 354
Joined: 27 Oct 2013, 15:11
Location: Gurgaon INDIA

VBA Codes of Pivot Table

Post by PRADEEPB270 »

Refer my attachment.

My data will be in Column B to H and Required format through VBA Codes as shown in Column J to P.

It should be in next sheet.

Look up the each vendor code in column B at once and their GSTR1 and GSTR3B whose date is maximum one ( newest one ) i.e.highlight in yellow colour and put up the Period against maximum date.

Can it be possible through VBA Codes or Pivot Table ?
Regards

Pradeep Kumar Gupta
INDIA

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA Codes of Pivot Table

Post by HansV »

Here is a macro:

Code: Select all

Sub CreateList()
    Dim wshS As Worksheet
    Dim wshT As Worksheet
    Dim s As Long
    Dim m As Long
    Dim a()
    Dim n As Long
    Application.ScreenUpdating = False
    Set wshS = Worksheets("Sheet1")
    Set wshT = Worksheets.Add(After:=wshS)
    wshT.Range("B5").Resize(1, 7).Value = Array("Vendor", "Name", "GSTIN", "GSTR1", "Period", "GSTR3B", "Period")
    m = wshS.Range("B" & Rows.Count).End(xlUp).Row
    For s = 5 To m
        If wshS.Range("B" & s).Value <> wshS.Range("B" & s - 1).Value Then
            n = n + 1
            ReDim Preserve a(1 To 7, 1 To n)
            a(1, n) = wshS.Range("B" & s).Value
            a(2, n) = wshS.Range("C" & s).Value
            a(3, n) = wshS.Range("D" & s).Value
            If wshS.Range("E" & s).Value = "GSTR1" Then
                a(4, n) = wshS.Range("F" & s).Value
                a(5, n) = wshS.Range("G" & s).Value
            Else
                a(6, n) = wshS.Range("F" & s).Value
                a(7, n) = wshS.Range("G" & s).Value
            End If
        Else
            If wshS.Range("E" & s).Value = "GSTR1" Then
                If wshS.Range("F" & s).Value > a(4, n) Then
                    a(4, n) = wshS.Range("F" & s).Value
                    a(5, n) = wshS.Range("G" & s).Value
                End If
            Else
                 If wshS.Range("F" & s).Value > a(6, n) Then
                    a(6, n) = wshS.Range("F" & s).Value
                    a(7, n) = wshS.Range("G" & s).Value
                End If
           End If
        End If
    Next s
    wshT.Range("B6").Resize(n, 7).Value = Application.Transpose(a)
    wshT.Range("B6").Resize(1, 7).EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

User avatar
PRADEEPB270
3StarLounger
Posts: 354
Joined: 27 Oct 2013, 15:11
Location: Gurgaon INDIA

Re: VBA Codes of Pivot Table

Post by PRADEEPB270 »

Thanks Hans Sir.

Perfect solution provided.I salute to you sir always.
Regards

Pradeep Kumar Gupta
INDIA

User avatar
PRADEEPB270
3StarLounger
Posts: 354
Joined: 27 Oct 2013, 15:11
Location: Gurgaon INDIA

Re: VBA Codes of Pivot Table

Post by PRADEEPB270 »

but Hans date is appearing in GSTR1 as 02-09-2021 ( mmddyy )and it should be as 09-02-2021 ( dd/mm/yy).

However in GSTR3B is ok.
Regards

Pradeep Kumar Gupta
INDIA

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA Codes of Pivot Table

Post by HansV »

It works OK on my computer. Working around this will take some time.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA Codes of Pivot Table

Post by HansV »

That was less difficult than I thought. Does this work better?

Code: Select all

Sub CreateList()
    Dim wshS As Worksheet
    Dim wshT As Worksheet
    Dim s As Long
    Dim m As Long
    Dim t As Long
    Application.ScreenUpdating = False
    Set wshS = Worksheets("Sheet1")
    Set wshT = Worksheets.Add(After:=wshS)
    wshT.Range("B5").Resize(1, 7).Value = Array("Vendor", "Name", "GSTIN", "GSTR1", "Period", "GSTR3B", "Period")
    m = wshS.Range("B" & Rows.Count).End(xlUp).Row
    t = 5
    For s = 5 To m
        If wshS.Range("B" & s).Value <> wshS.Range("B" & s - 1).Value Then
            t = t + 1
            wshT.Range("B" & t).Value = wshS.Range("B" & s).Value
            wshT.Range("C" & t) = wshS.Range("C" & s).Value
            wshT.Range("D" & t) = wshS.Range("D" & s).Value
            If wshS.Range("E" & s).Value = "GSTR1" Then
                wshT.Range("E" & t) = wshS.Range("F" & s).Value
                wshT.Range("F" & t) = wshS.Range("G" & s).Value
            Else
                wshT.Range("G" & t) = wshS.Range("F" & s).Value
                wshT.Range("H" & t) = wshS.Range("G" & s).Value
            End If
        Else
            If wshS.Range("E" & s).Value = "GSTR1" Then
                If wshS.Range("F" & s).Value > wshT.Range("E" & t).Value Then
                    wshT.Range("E" & t) = wshS.Range("F" & s).Value
                    wshT.Range("F" & t) = wshS.Range("G" & s).Value
                End If
            Else
                 If wshS.Range("F" & s).Value > wshT.Range("G" & t).Value Then
                    wshT.Range("G" & t) = wshS.Range("F" & s).Value
                    wshT.Range("H" & t) = wshS.Range("G" & s).Value
                End If
           End If
        End If
    Next s
    wshT.Range("B5").Resize(1, 7).EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

User avatar
PRADEEPB270
3StarLounger
Posts: 354
Joined: 27 Oct 2013, 15:11
Location: Gurgaon INDIA

Re: VBA Codes of Pivot Table

Post by PRADEEPB270 »

Yes,now it is perfect.

Codes are working fine.

Great thank you for your efforts.
Regards

Pradeep Kumar Gupta
INDIA