Pivot Sheet with previous month in vba code macro

i have created macro and in that i created one sheet “HOLD_Register_Nov21” but nov21 is always changing that means i want previous month date always so in pivot how to pass date year dynamic

Welcome to the UiPath Forum @Sayali_Rokade1

you can put the year using write cell in an excel cell and then in the macro simply read that specific cell and use that in creating new sheet

hi @rahulsharma
actually i used pivot and in that pivot range i want this .will share my macro and pivot code in macro

Hi all,
Share you my macro and file also some screenshot please help me on this
macro.txt (5.7 KB)

Report.xlsx (8.1 KB)
now if m running for jan dynamically it will pick for Dec like wise i want in macro

Hi @Sayali_Rokade1 ,

You have to pass both the arguments Excel file path and dynamic sheet name in the Execute macro → Macro parameters section.

  1. Use assign activity below to make dynamic work sheet for the current month.

SAP_VENHOLDWorkSheetName = “HOLD_Register_”+Now.ToString(“MMMyy”)

  1. In Execute Macro Activity pass the macro parameters like below.


Your Macro code start with the below line

Sub VENHOLD_EXCELPath(SAP_VENHOLDPath As String, SAP_VENHOLDWorkSheetName As String)

Pass the worksheet name SAP_VENHOLDWorkSheetName dynamically in your macro code. Please try and let us know. thanks.

HI @kirankumar.mahanthi1 thanks for your response i used above steps but still facing error in pivot

i have used same sheet name in pivot range but in yellow block of pivot showing error

Hi @Sayali_Rokade1 ,

We are passing dynamic variable so we should not keep it in double quotes.
Try like below and let us know. if you still faces issue please share with me input excel with the pivot which you created manually or with macro share it to me i will write vb code and share it with you so that we can use invoke Code activity instead of execute macro. thanks.

SAP_VENHOLDWorkSheetName&"!R1C24:R1048576C25", Version:=6).CreatePivotTable _

@kirankumar.mahanthi1 thanks for help

1 Like

Your welcome @Sayali_Rokade1

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.