Need to remove old month and enter the present month

Help Studio
, ,
1

Hi all!!!

I do have a Excel and in that excel at AS column i should enter the present Month

image

after entered the present month

image
image365×533 5.46 KB

Once i entered the present month if column AT & AU are Not same month as in AS column
then need to delete the AT & AU Columns so the present month will appear after deleted the old month in AT & AU

image
image471×688 15.6 KB

Once We Deleted

image

The final output should be as above like AS,AT & AU Columns

Here is the file :

MasterF.xlsx (202.9 KB)

please kindly let me know the posibble ways to achieve this and by using VBA.

Thank you!!!

2

Hi @vineelag

Try this Invoke VBA code:

Sub UpdateHeaders()
    Dim ws As Worksheet
    Dim headerCellAS As Range, headerCellAT As Range, headerCellAU As Range
    Dim currentMonth As String

    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "YourSheetName" with your actual sheet name

    ' Set the header cells in columns AS, AT, and AU
    Set headerCellAS = ws.Range("AS1")
    Set headerCellAT = ws.Range("AT1")
    Set headerCellAU = ws.Range("AU1")

    ' Get the current month abbreviation
    currentMonth = Format(Date, "mmm")

    ' Update the header cells with the current month
    headerCellAS.Value = currentMonth & "-23 MTD (Hours)"
    headerCellAT.Value = currentMonth & "-23 Hours"
    headerCellAU.Value = currentMonth & "-23 ($)"
End Sub

Hope it helps

3

Hi @vineelag

Input:

image
image877×687 9.03 KB

Output:

image
image692×675 7.79 KB

Attached the workflow for reference

BlankProcess10.zip (236.0 KB)

Hope it helps!!

4

@Parvathy

Try to open this file getting like this

image
image1102×583 50 KB

5

@vineelag

BlankProcess10.zip (236.8 KB)

Try this.

Regards,

6

Still getting The same

@Parvathy

7

@vineelag
Try this:
BlankProcess11.zip (220.9 KB)

Regards,

8

@Parvathy

No, its dosent open

9

@vineelag

Paste this code in a notepad file and pass the path in Invoke VBA activity and pass Method name as UpdateHeaders

Sub UpdateHeaders()
    Dim ws As Worksheet
    Dim headerCellAS As Range, headerCellAT As Range, headerCellAU As Range
    Dim currentMonth As String

    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "YourSheetName" with your actual sheet name

    ' Set the header cells in columns AS, AT, and AU
    Set headerCellAS = ws.Range("AS1")
    Set headerCellAT = ws.Range("AT1")
    Set headerCellAU = ws.Range("AU1")

    ' Get the current month abbreviation
    currentMonth = Format(Date, "mmm")

    ' Update the header cells with the current month
    headerCellAS.Value = currentMonth & "-23 MTD (Hours)"
    headerCellAT.Value = currentMonth & "-23 Hours"
    headerCellAU.Value = currentMonth & "-23 ($)"
End Sub
10

Hi @vineelag,

Please check if it’s working as expected by doing the following steps.

  1. In the assign activity right side put - Now.ToString(“MMM”) and in the left side create a month variable, say str_Month.

  2. Then in the excel application scope or using the workbook activity, use Read Cell value and the cell should be “AS2”. Store in a variable, say str_ASCellValue.

  3. In the if activity, give the condition as, not str_Month.ToUpper.Trim.Contains(str_ASCellValue.ToUpper.Trim), in then part you can do two write cell to “AT2” and the value will be str_Month+“-23” Hours and the second write cell to “AU2” and the value - str_Month+“-23” $

Please let me know if it works.

Cheers