Hey,
I’ve have absolutely no scripting experience but I said I’d do a job for my girlfriend’s father so I’m looking for some help.
It’s complicated so I will try to explain as best I can! Any help at all will be greatly appreciated.
There is an Excel worksheet that tracks deliveries made to 7 different outlets on 7 separate runs.
It has 7 different sheets where each sheet corresponds to one of the runs. Each sheet has 11 columns but I am only concerned with the first 4 columns which look like this:
Type Name Supply Run
1 Outlet 1 160 1
4 Outlet 2 25 1
7 Outlet 3 80 1
3 Outlet 4 110 1
The Type corresponds to 1 of the 7 outlets and the Run corresponds to one of the 7 deliveries. So on Sheet 1 all the Runs will have the same value of 1, on Sheet 2 they’ll all be 2 etc.
The task then is to:
Create a new worksheet with 7 sheets. This time each sheet corresponds to 1 of the 7 outlets. So on Sheet 1, Type will always be 1, on Sheet 2, Type will always be 2 etc.
Compare the Type cell and copy the results from the 4 columns of the same row into a corresponding sheet in the new worksheet. So if Type is 1 it is copied into sheet 1 along with the Name, Supply and Run cells.
3)The number of Rows per sheet is not constant so I need to determine when to stop looping through the rows.
Each sheet of the final new workbook will look something like:
Type Name Supply Run
1 Outlet 1 10 1
1 Outlet 2 35 7
1 Outlet 3 75 6
1 Outlet 4 130 3
If anyone can give me help with any of the 3 steps it would be great.
tell application "Microsoft Excel"
set newBook to make new workbook
repeat until (count of sheets in active workbook) ≥ 7
make new worksheet at end of active workbook
end repeat
end tell
The path I’d follow would be to use Advanced Filter’s copy to other location feature 49 times; 7 times on each of the 7 source sheets.
tell application "Microsoft Excel"
-- this assumes that the source data sheets are the 7 left-most tabs in the source workbook.
-- adjust the name of the source workbook
set sourceBook to workbook "SourceBook.xls"
set compositeSheet to sheet 1 of sourceBook
set startRowCount to count of rows in (current region of cell "a1" of compositeSheet)
-- copy all seven sheet to sheet 1
repeat with sheetIndex from 2 to 7
set oneSheetsDataRange to current region of cell "a1" of sheet sheetIndex of sourceBook
set oneSheetsData to get value of oneSheetsDataRange
set writeToRange to (current region of cell "a1" of compositeSheet)
set writeToRange to get offset writeToRange row offset (count of rows of writeToRange)
set writeToRange to get resize writeToRange row size (count of rows of oneSheetsDataRange) column size (count of columns of oneSheetsDataRange)
set value of writeToRange to oneSheetsData
end repeat
-- make new workbook
set destinationBook to make new workbook
-- with 7 sheets
repeat until (count of sheets in destinationBook) ≥ 7
make new worksheet at end of destinationBook
end repeat
-- filter sheet1 to new sheet
set dataRange to (current region of cell "a1" of compositeSheet)
set critRange to get resize (get offset (cell 1 of dataRange) column offset (1 + (count of columns of dataRange))) row size 2 column size 1
set value of cell 1 of critRange to "Type"
repeat with sheetIndex from 1 to 7
set value of cell 2 of critRange to sheetIndex
clear contents cells of sheet sheetIndex of destinationBook
advanced filter dataRange ¬
action filter copy ¬
criteria range critRange ¬
copy to range range "a1" of sheet sheetIndex of destinationBook
end repeat
-- reset sheet 1
clear contents (get offset dataRange row offset startRowCount)
clear contents critRange
end tell
didn’t find the file for me. I had to open the file first using
open workbook workbook file name "Users:hopper:Documents:Excel:Runs.xls"
Is there a way to avoid this? I have the script in the same folder as the xls file.
Second I got an error message “Microsoft Excel got an error: current region of cell “a1” of worksheet 1 of workbook “Runs.xls” doesn’t understand the advanced filter message.” in regards to the lines :
advanced filter dataRange ¬
action filter copy ¬
criteria range critRange ¬
copy to range range "a1" of sheet sheetIndex of destinationBook
Thanks again for the help.
I’m going to keep at it and see what I can do.
The code assumes that the workbook being acted on is already open.
Is the extention in Excel2008 “xls” or “xlsm” or ???, That needs to match.
Or it might be a difference between my 2004 and 2008.
Have you downloaded the AppleScript manual for Excel 2008 from the MicroSoft website?
Before the erroring code, have you put
Thanks Mike. I’ve got it working now. I owe you a beer
Dunno if you can help with one more thing?
I want to Sum up a column. I have the number of rows in the column stored in a variable called maxRows. So say I want to sum from C2 to C20, how do I do that?
The closet I have got is below
set formula of cell ("C" & (maxRows) + 1) of sheet i of destinationBook to "=Sum(R2C3 : R " & maxRows & " & C3)""
I know the first part works but inside the brackets of the Sum formula is wrong and I get a circular error.
