Macro issue

[Imajica]

I understand that my request is a little vague, but bear with me...

I often run a report that gets exported into an Excel file. I then have to format it in a certain way. This formatting requires a lot of steps including filtering/sorting, etc. It would be great if there would be a way to create a macro that I could run that would do it all for me. So I recorded a macro of all the steps that I took. I then run the macro and it works great so long as I run it on the same report. I found that if I save the macro and try to use it on another report (same report, but run a month later) it runs, but the formatting is messed up. It seems that the new report sometimes has more rows or less rows than the report when the macro was originally recorded. So it messes up. Mainly because when I set a filter to do things, because the new version of the report has either more or less rows, it messes up. Is there a way to record a macro or some other way so that every month, I do not have to manually run through about 20 steps?


Thanks

 

[NdNoviceHlp]

Hi Imafica. There is a way... post your code here. You will probably have to provide some more specific info but I'm sure there will be members that will be able to help you adjust a working code to be more flexible. HTH. Dave

 

[Imajica]

I had not saved the script as I could not get it to work properly initially... So I just redid my editing on a copy of an old report. It has been a while since I have done it... so the interface seems to be different than what I remember. I recorded everything and saved the script (using O365). Everything seemed to work fine. I then closed the file, and saved it as a different name. I then attempted to see if the script worked. I copied the original report to a 2nd copy. Went to the automate tab and chose the script I just did. It did not even work on the same file I just did. It did not error out or anything. It did a few things that I had recorded, but all in all. It created the 2nd tab at the bottom. Did some stuff on the original tab, but mostly it seemed to have deleted everything except one line item and the new "print" tab was empty, but it did get the row height right, but nothing else.

This is the first time I ever had this kind of issue. The script would always work assuming I used the same data. I am not sure how to share the data as the file contains confidential information (names, phone numbers, account numbers, etc.)

Below is the script that was generated. I am not sure how to simply post the .osts file here so i did a copy and paste of the script that was generated.

function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();
  // Auto fit the columns of all cells on selectedSheet
  selectedSheet.getRange().getFormat().autofitColumns();
  // Toggle auto filter on selectedSheet
  selectedSheet.getAutoFilter().apply(selectedSheet.getRange());
  // Apply values filter on selectedSheet
  selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 6, { filterOn: ExcelScript.FilterOn.values, values: ["PL10: Plot installments", "PL10: Plot installments 2012", "PL10: Plot installments 2016", "PL10: Plot installments 2017", "PL10: Plot installments 2018", "PL10: Plot installments 2019", "PL10: Plot installments 2020", "PL10: Plot installments 2021", "PL10: Plot installments 2022", "PL10: Plot installments 2023", "PLOT: Cemetery Plots", "PLOT: Cemetery Plots 2023", "SFC: Shaar Family Campaig 2016", "SFC: Shaar Family Campaig 2017", "SFC: Shaar Family Campaig 2018", "SFC: Shaar Family Campaig 2019", "SFC: Shaar Family Campaign 2020", "SFC: Shaar Family Campaign 2021", "SFC: Shaar Family Campaign 2022", "SFC: Shaar Family Campaign 2023", "SFC: Shaar Family Campaign 2024", "SFC: Shaar Family Campaign 2025", "SFC: Shaar Family Campaign 2026", "SFC: Shaar Family Campaign 2027", "="] });
  // Delete range 2:968 on selectedSheet
  selectedSheet.getRange("2:968").delete(ExcelScript.DeleteShiftDirection.up);
  // Clear auto filter on selectedSheet
  selectedSheet.getAutoFilter().clearCriteria();
  // Set format for range G:N on selectedSheet
  selectedSheet.getRange("G:N").setNumberFormatLocal("_($* #,##0.00_);_($* (#,##0.00);_($* \"\"-\"\"??_);_(@_)");
  // Set range H1:K1 on selectedSheet
  selectedSheet.getRange("H1:K1").setValues([["1 Year","2 Year","3 Year","4 Year"]]);
  // Insert cut cells from B:B on selectedSheet to A:A on selectedSheet.
  selectedSheet.getRange("A:A").insert(ExcelScript.InsertShiftDirection.right);
  selectedSheet.getRange("B:B").moveTo(selectedSheet.getRange("A:A"));
  // Set font color to "#FFFFFF" for range 1:1 on selectedSheet
  selectedSheet.getRange("1:1").getFormat().getFont().setColor("#FFFFFF");
  // Set fill color to #000000 for range 1:1 on selectedSheet
  selectedSheet.getRange("1:1").getFormat().getFill().setColor("#000000");
  // Set font bold to true for range 1:1 on selectedSheet
  selectedSheet.getRange("1:1").getFormat().getFont().setBold(true);
  // This action currently can't be recorded.
  // Replace all " Total" with "" on range A:A on selectedSheet
  selectedSheet.getRange("A:A").replaceAll(" Total", "", {completeMatch: false, matchCase: false});
  // Set range B5 on selectedSheet
  selectedSheet.getRange("B5").setFormulaLocal("=B4");
  // Paste to range C5 on selectedSheet from range B5 on selectedSheet
  selectedSheet.getRange("C5").copyFrom(selectedSheet.getRange("B5"), ExcelScript.RangeCopyType.all, false, false);
  // Paste to range D5 on selectedSheet from range B5 on selectedSheet
  selectedSheet.getRange("D5").copyFrom(selectedSheet.getRange("B5"), ExcelScript.RangeCopyType.all, false, false);
  // Paste to range E5 on selectedSheet from range B5 on selectedSheet
  selectedSheet.getRange("E5").copyFrom(selectedSheet.getRange("B5"), ExcelScript.RangeCopyType.all, false, false);
  // Set row level: 3 on selectedSheet
  selectedSheet.showOutlineLevels(3, 0);
  // Set row level: 3 on selectedSheet
  selectedSheet.showOutlineLevels(3, 0);
  // Set row level: 3 on selectedSheet
  selectedSheet.showOutlineLevels(3, 0);
  // Set row level: 2 on selectedSheet
  selectedSheet.showOutlineLevels(2, 0);
  // This action currently can't be recorded.
  // Apply values filter on selectedSheet
  selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 2, { filterOn: ExcelScript.FilterOn.values, values: ["0"] });
  // Clear ExcelScript.ClearApplyTo.contents from range C5:C789 on selectedSheet
  selectedSheet.getRange("C5:C789").clear(ExcelScript.ClearApplyTo.contents);
  // Clear auto filter on selectedSheet
  selectedSheet.getAutoFilter().clearCriteria();
  // Apply values filter on selectedSheet
  selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 3, { filterOn: ExcelScript.FilterOn.values, values: ["0"] });
  // Clear ExcelScript.ClearApplyTo.contents from range D18:D789 on selectedSheet
  selectedSheet.getRange("D18:D789").clear(ExcelScript.ClearApplyTo.contents);
  // Clear auto filter on selectedSheet
  selectedSheet.getAutoFilter().clearCriteria();
  // Apply values filter on selectedSheet
  selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 4, { filterOn: ExcelScript.FilterOn.values, values: ["0"] });
  // Clear ExcelScript.ClearApplyTo.contents from range E7:E787 on selectedSheet
  selectedSheet.getRange("E7:E787").clear(ExcelScript.ClearApplyTo.contents);
  // Clear auto filter on selectedSheet
  selectedSheet.getAutoFilter().clearCriteria();
  // Set font bold to false for range A:A on selectedSheet
  selectedSheet.getRange("A:A").getFormat().getFont().setBold(false);
  // Add a new worksheet with name "Print"
  let print = workbook.addWorksheet("Print");
  // Set row level: 2 on selectedSheet
  selectedSheet.showOutlineLevels(2, 0);
  // This action currently can't be recorded.
  // Auto fit the columns of range A:A on print
  print.getRange("A:A").getFormat().autofitColumns();
  // Auto fit the columns of range A:A on print
  print.getRange("A:A").getFormat().autofitColumns();
  // Auto fit the columns of range A:N on print
  print.getRange("A:N").getFormat().autofitColumns();
  // Set horizontal alignment to ExcelScript.HorizontalAlignment.left for range C:C on print
  print.getRange("C:C").getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.left);
  // Indent set to 0 for range C:C on print
  print.getRange("C:C").getFormat().setIndentLevel(0);
  // Set horizontal alignment to ExcelScript.HorizontalAlignment.center for range C:E on print
  print.getRange("C:E").getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
  print.getRange("C:E").getFormat().setIndentLevel(0);
  // Indent set to 0 for range C:E on print
  print.getRange("C:E").getFormat().setIndentLevel(0);
  // Delete range F:F on print
  print.getRange("F:F").delete(ExcelScript.DeleteShiftDirection.left);
  // Set height of row(s) at all cells on print to 35.25
  print.getRange().getFormat().setRowHeight(35.25);
  // Set height of row(s) at range 1:1 on print to 21
  print.getRange("1:1").getFormat().setRowHeight(21);
  // Set vertical alignment to ExcelScript.VerticalAlignment.center for range 1:1 on print
  print.getRange("1:1").getFormat().setVerticalAlignment(ExcelScript.VerticalAlignment.center);
  print.getRange("1:1").getFormat().setIndentLevel(0);
  // Clear print area for print.
  print.getPageLayout().setPrintArea("");
  // Set ExcelScript.PageOrientation.landscape orientation for print
  print.getPageLayout().setOrientation(ExcelScript.PageOrientation.landscape);
  // Set Legal paperSize for print
  print.getPageLayout().setPaperSize(ExcelScript.PaperType["Legal"]);
  // Set FitAllColumnsOnOnePage scaling for print
  print.getPageLayout().setZoom({scale: 0});
  print.getPageLayout().setZoom({horizontalFitToPages: 1, verticalFitToPages: 150});
}