Hi guys,
I'm quite wondering how can I put the tab in order based on my requirements. I have tried setting it in order using the Union tool but nothing is happening. It doesn't move. Is there any other way I could arrange it. Below is the current arrangement.
I would like to arrange as follows:
1. Checklist
2. Data File
3. Latest Codes Fired
4. A. Ensure LD Remediation
Please advise.
Kamen
Solved! Go to Solution.
Hi, @KamenRider
If you only have 3 to 5 sheets, you might want to using the Block Until Done tool to rearrange the order of the sheets, but if you have 30 sheets or more, you can consider using BAT code in the same way as we did in the previous scenario. The only thing you have to do here is replace the SourceFile in the code with the absolute path to your file, e.g. 'D:/123/abc.xlsx', and let the code run for the case.
@echo off &&setlocal enabledelayedexpansion &&@echo Arrange WorkSheets In ***' SourceFile '*** ... &&powershell -Command "Add-Type -AssemblyName 'Microsoft.Office.Interop.Excel'; $ID = @('1-Checklist', '2-Data File', '3-Latest Codes Fired', '4-A. Ensure LD Remediation'); $wkBook = 'SourceFile'; $objExcel = New-Object -ComObject Excel.Application; $objExcel.Visible = $false; $objExcel.DisplayAlerts = $false; $wb = $objExcel.Workbooks.Open($wkBook); foreach ($id in $ID) { $shtNum = [int]$id.Split('-',2)[0]; $shtName = $id.Split('-',2)[1]; $wb.Sheets.Item($shtName).Move($wb.Sheets.Item($shtNum)); }; $wb.Save(); $wb.Close(); $objExcel.Quit();" &&@echo *** All done ! ***