Pivot Sheet with previous month in vba code macro

Help Studio
1

Hi,
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

2

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

3

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

4

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

pivot_monthyearDynamic
pivot_monthyearDynamic1366×768 47.7 KB

SheetnameAndPivot
SheetnameAndPivot2036×922 72.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

5

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.

{SAP_VENHOLDPath,SAP_VENHOLDWorkSheetName}

image
image1339×643 35.5 KB

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.

6

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

error
error2036×922 65 KB

error2
error22036×922 91.8 KB

UsedSheetLike
UsedSheetLike2036×922 63.9 KB

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

7

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 _

8

@kirankumar.mahanthi1 thanks for help

1 Like
9

Your welcome @Sayali_Rokade1

10

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