Automate excel process - perform using macros

karthirm90

New Member
Joined
Aug 8, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
I am trying to do macro automation for the following 3 tasks and I'm stuck in all 3 tasks, kindly request to help.

  1. Calculate ageing of tickets using assigned date of ticket
  2. Fill blank cells for which ageing calculation wasn't done due to an incorrect date format issue
  3. Refresh pivot tables
Ageing calculation - Have to find age of tickets, assigned date is given.

  • I have implemented the below macro code but I don't know how to dynamically code on finding total no. of rows present, here i'm hardcoding it to 872.
  • Also, this one doesn't fill with numbers ( for ageing) for all cells, it leaves few rows in between and i don't know what is wrong. Is there a better way to implement ?
  • For example - it fills rows from 1 to 20, then leaves 21 to 60 for some strange reason which I'm unable to find out and then fills again from 61 to 90. Not sure if i'm missing something. Please help
VBA Code:
Sub AgeingCalculation()
Dim xWb As Workbook
Dim xWs As Worksheet
Dim xRow As Integer
Dim K As Long
On Error Resume Next
Set xWs = Worksheets("Raw Data") 
xWs.Range("I1").Value = "Current Date"
xWs.Range("I2:I872").Value = Now
Rows(1).Font.Bold = True
xWs.Range("J1").Value = "Ageing"
For K = 2 To 872
    xWs.Cells(K, 10).Value = DateDiff("d", Cells(K, 3), Cells(K, 9))
Next K
End Sub

Fill the blank cells with a default value of 15 days as all these tickets are older than 2 weeks and ageing calculation couldn't be done due to incorrect date format issue.

  • Problem here is if rows 1 to 5 have values, 6th and 7th rows are blank then the below logic doesn't work. It works only if all the rows are blank but in real time i will never get the use case
  • For example - row 1 to 20 may have values, 21 to 25 might be blank. Again 26 to 75 may have values, 76 to 90 might be blank.
  • I want to fill the rows which are blank with default value of >15 days but its not coming correctly

VBA Code:
Sub Replace_Blank_With_Text()
    Dim Range1, Range2 As Range, cell As Range
    Dim Value_1 As String
    Dim xWb As Workbook
    Dim xWs As Worksheet
    On Error Resume Next
    Set xWs = Worksheets("Sheet5")
    Value_1 = ">15 days"
    Set Range1 = xWs.Range("J2:J837")
    Set Range2 = xWs.Range("K2:K837")
    For Each cell In Range1
    If Range1.Text = "" Then
    Range2.Value = Value_1
    End If
    Next cell
    End Sub

Refresh pivot tables

  • I have a sheet called as raw data, after doing task 1 (ageing calculation) and task 2 (fill the blank cells with default value) this can be accomplished.
  • Tried the below logic but it doesn't work at all - not refreshing pivot tables - PivotTable2 and PivotTable3 are names of pivot tables whch are present in sheet called as PIVOT

VBA Code:
Sub PivotTableUpdate()
    Dim PT As PivotTable
    For Each ws In ActiveWorkbook.Worksheets
      For Each PT In ActiveWorkbook.PivotTables
        PT.RefreshTable
      Next PT
    Next ws
    End Sub

  • Tried to hardcode logic like below also but doesn't make a difference, please help

VBA Code:
Sub PivotTableUpdateCalc()
    Worksheets("PIVOT").PivotTables("PivotTable2").PivotCache.Refresh
    Worksheets("PIVOT").PivotTables("PivotTable3").PivotCache.Refresh
    End Sub
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Coyotex3

Active Member
Joined
Dec 12, 2021
Messages
312
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
For the first part of your code, do all of the columns have data until row 872? if so maybe you can add the following to your code, and make it dynamic by changing column to look in?

VBA Code:
Dim K as Long
Dim LR as Long

LR = Range("J" & Rows.Count).End(xlUp).Row                                                                          ' this code is looking at column J, change this value to whichever column you prefer
For K = 2 To LR
 xWs.Cells(K, 10).Value = DateDiff("d", Cells(K, 3), Cells(K, 9))
Next K
 

karthirm90

New Member
Joined
Aug 8, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
For the first part of your code, do all of the columns have data until row 872? if so maybe you can add the following to your code, and make it dynamic by changing column to look in?

VBA Code:
Dim K as Long
Dim LR as Long

LR = Range("J" & Rows.Count).End(xlUp).Row                                                                          ' this code is looking at column J, change this value to whichever column you prefer
For K = 2 To LR
 xWs.Cells(K, 10).Value = DateDiff("d", Cells(K, 3), Cells(K, 9))
Next K
Thank you so much, let me try. Some of the columns don't have data due to an incorrect date format (1/1/1900).
That is why I asked the 2nd part - fill blank cells with default value of >15 days. Can you please tell me if 2nd part of my code is correct as it doesn't fill values if there are some columns which have values already and only need to fill the blank ones. Any other better way to implement ?
At last, I need to refresh pivot tables which is not happening. Please suggest or help
 

karthirm90

New Member
Joined
Aug 8, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
I was able to find solutions for part 1 and part 2.
Part 1 had issues where the data format was incorrect, so I corrected using part 2 code itself by assigning a default value like below

VBA Code:
For K = 2 To LR
    xWs.Cells(K, 10).Value = DateDiff("d", Cells(K, 3), Cells(K, 9))
    If xWs.Cells(K, 10).Value = 44817 Or xWs.Cells(K, 10).Value = "" Then xWs.Cells(K, 10).Value = Value_1
Next K

But don't know how to update pivot tables which is present in a sheet called as PIVOT. Source of pivot is in a sheet called as Data. Please help me.
 

Coyotex3

Active Member
Joined
Dec 12, 2021
Messages
312
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
I was able to find solutions for part 1 and part 2.
Part 1 had issues where the data format was incorrect, so I corrected using part 2 code itself by assigning a default value like below

VBA Code:
For K = 2 To LR
    xWs.Cells(K, 10).Value = DateDiff("d", Cells(K, 3), Cells(K, 9))
    If xWs.Cells(K, 10).Value = 44817 Or xWs.Cells(K, 10).Value = "" Then xWs.Cells(K, 10).Value = Value_1
Next K

But don't know how to update pivot tables which is present in a sheet called as PIVOT. Source of pivot is in a sheet called as Data. Please help me.
Does this modification get them to update?

VBA Code:
Sub PivotTableUpdateCalc()
    With Sheets("Pivot")
        .PivotTables("PivotTable2").PivotCache.Refresh
        .PivotTables("PivotTable3").PivotCache.Refresh
    End With
End Sub
 

karthirm90

New Member
Joined
Aug 8, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Does this modification get them to update?

VBA Code:
Sub PivotTableUpdateCalc()
    With Sheets("Pivot")
        .PivotTables("PivotTable2").PivotCache.Refresh
        .PivotTables("PivotTable3").PivotCache.Refresh
    End With
End Sub
  1. This code seems to work but one problem I face is "pivot tables are not updated if I have to delete the entire raw-data sheet (source sheet for pivots) and fill the data again".
  2. Because this is a task done by support team daily - so they will delete the entire raw-data sheet and put new values.
So my task is to write a macro refresh pivot tables which is present in sheet called "PIVOT" once the raw-data sheet is updated.
Can you please tell me how to write macro code for the below
  • Delete/refresh the pivot tables at the start each time because some cache issue i believe is happening as pivot tables are not updating at times properly even though the data is updated in raw-data sheet
  • Then provide the source sheet and range as well (A1:K6000) in code itself because every time we delete the data in raw-sheet this is not picked properly
  • Update the pivot tables in sheet called PIVOT_DATA based on the source sheet (raw-data)
 

Coyotex3

Active Member
Joined
Dec 12, 2021
Messages
312
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  1. This code seems to work but one problem I face is "pivot tables are not updated if I have to delete the entire raw-data sheet (source sheet for pivots) and fill the data again".
  2. Because this is a task done by support team daily - so they will delete the entire raw-data sheet and put new values.
So my task is to write a macro refresh pivot tables which is present in sheet called "PIVOT" once the raw-data sheet is updated.
Can you please tell me how to write macro code for the below
  • Delete/refresh the pivot tables at the start each time because some cache issue i believe is happening as pivot tables are not updating at times properly even though the data is updated in raw-data sheet
  • Then provide the source sheet and range as well (A1:K6000) in code itself because every time we delete the data in raw-sheet this is not picked properly
  • Update the pivot tables in sheet called PIVOT_DATA based on the source sheet (raw-data)
Honestly, I'm at a lost as to how to proceed here. Are you able to share a sample of what the data looks likes?
 

karthirm90

New Member
Joined
Aug 8, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Honestly, I'm at a lost as to how to proceed here. Are you able to share a sample of what the data looks likes?
Hi ,
please find the below screenshot (just a sample), in real-time we will have more than 1000 rows at times

  1. This data is being copied from outlook email and pasted into excel - lets say this is done on Wednesday , September 14
  2. Once it is pasted, I've written some logic to find the current date, ageing etc.
  3. After that users will click on a macro button - Pivot refresh , based on that it should refresh pivot tables (pivot table2 and pivot table3) present in sheet called as PIVOT (refer 2nd image)
  4. On Thursday, September 15, there will be new data. So, they will delete entire data present in excel sheet, copy from outlook and paste in excel, at last refresh pivot table again.
    1. Whatever logic you gave doesn't refresh at that time - its still showing earlier data and not the new data. Here i would like to refresh pivot based on newly copied data (could be more or less rows), provide the source sheet name (raw_data) and range for pivot (A1:K1000).
    2. Please help me for that.


1663176847535.png


1663177051719.png
 

karthirm90

New Member
Joined
Aug 8, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Can someone please help me as I dont know how to proceed. Kindly assist.
 

Coyotex3

Active Member
Joined
Dec 12, 2021
Messages
312
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Hi, Karthirm90, did not have much time to take a look at this yesterday.

Quite frankly, I'm not well versed in VBA and this might be more than I can accomplish. I don't know how to go about refreshing the pivot tables when the original source data is deleted. I'm not sure if it can be done, and if it can be done, I do not know how to do it.

You might get more help by starting a new thread as some of the members look for unanswered questions to answer. Your latest Pivot Table refresh request is a bit different than what you originally asked, therefore you might be able to post a new thread requesting how to update a pivot table after the source data is deleted?

Sorry that I could not be of further assistance.
 

Forum statistics

Threads
1,181,423
Messages
5,929,816
Members
436,697
Latest member
sunnypl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top