How to make a script that will make an additional column "to the right" of the full area?

Kral, Jan 1 Reputation point
2022-11-04T11:44:43.707+00:00

Dear Sir or Madam,

I have a macro/script (on Sharepoint) that adds one column with new tracking data through VLOOKUP in Excel worksheet every week (supported by Power Automate). The column of new data is always inserted to column "F", so the older columns from "F" to "R" are moving to "G" and "S", because there is a new column "F" with brand new generated data.

Is there any way to make the code to generate data in the every next column on the right? So it will look chronological in time. And every week there will be a brand new column with new data next to the full area of data etc. For example next week, the column "T" will have the data, next week same for column "U" and so on.

I asked the same question on this site and was moved to the script section: https://answers.microsoft.com/en-us/msoffice/forum/all/how-to-make-a-script-that-will-make-an-additional/31f11948-cb14-442a-97ab-d863425eee3d

Big thanks in advance!

257272-tempsnip.png

Code:

function main(workbook: ExcelScript.Workbook) {

let selectedSheet = workbook.getActiveWorksheet();

// Insert at range F:F on selectedSheet, move existing cells right

selectedSheet.getRange("F:F").insert(ExcelScript.InsertShiftDirection.right);

// Auto fill range

selectedSheet.getRange("G1:H1").autoFill("F1:H1", ExcelScript.AutoFillType.fillDefault);

// Set range F2 on selectedSheet

selectedSheet.getRange("F2").setFormulaLocal("=VLOOKUP(A2,'[Global_Supplier_Risk_Tracking.xlsx](PAVE, RISK)_Supplier_Tracking'!$A:$T,20,NEPRAVDA)");

// Auto fill range

selectedSheet.getRange("F2").autoFill();

let europe_financial_risk_overview = workbook.getWorksheet("Europe financial risk overview");

// Set range B2 on europe_financial_risk_overview

europe_financial_risk_overview.getRange("B2").setFormulaLocal("=COUNTIFS(List1!$F:$F,'Europe financial risk overview'!A2,List1!C:C,\"Europe\")");

// Auto fill range

europe_financial_risk_overview.getRange("B2").autoFill("B2:B6", ExcelScript.AutoFillType.fillDefault);

let kontingen_n__tabulka6 = workbook.getPivotTable("Kontingenční tabulka6");

// Refresh kontingen_n__tabulka6

kontingen_n__tabulka6.refresh();

let global_financial_risk_overview = workbook.getWorksheet("Global financial risk overview");

// Set range B2 on global_financial_risk_overview

global_financial_risk_overview.getRange("B2").setFormulaLocal("=COUNTIF(List1!$F:$F,'Global financial risk overview'!A2)");

// Auto fill range

global_financial_risk_overview.getRange("B2").autoFill();

let kontingen_n__tabulka7 = workbook.getPivotTable("Kontingenční tabulka7");

// Refresh kontingen_n__tabulka7

kontingen_n__tabulka7.refresh();

}

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
7,037 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
2,427 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,409 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Oskar Shon 866 Reputation points MVP
    2022-11-08T18:00:41.47+00:00

    Is there any way to make the code to generate data in the every next column on the right? So it will look chronological in time. And every week there will be a brand new column with new data next to the full area of data etc. For example next week, the column "T" will have the data, next week same for column "U" and so on.

    Yes. You should count how many columns you use and assignee it to variable 

    dim last_col&: last_col = Cells(1, Columns.Count).End(xlToLeft).Column

    Then use this variable to your code.

    Regards.

    0 comments