JimS
07-14-2009, 01:30 PM
I need a macro that will perform the steps below but I’m not sure how to start.
I import several Sheets from another source (there can be 1 – 30 Sheets).
Each sheet’s actual name begins with “Sheet” (ie: Sheet1, Sheet1(2), Sheet1(3), etc).
The data that gets imported on each individual sheet can vary in “width” and “length”.
The columns are grouped in sets of 6, meaning Column B-G is one set of points, H-M (if present) is similar data points to B-G but a different set, N-S (if present) is also similar data points to B-G & H-M but a different set that I need to compare.
Column A is a Timestamp that will exist on each sheet but does not need to be evaluated.
There may only be 1-set of Columns with data (A-G) or there maybe several sets of columns with data.
Here’s what I’m after:
1) Insert 10 rows on every sheet which name begins with “Sheet”. There are other sheets in the workbook but their names do not begin with “Sheet”.
2) Perform the following 3 calculations for every “column used” starting with column B
a. =MAX(B12:Bxxxx) This should be in Cell B1 and get copied out to the last column used.
b. =Average(B12:Bxxxx) This should be in Cell B2 and get copied out to the last column used.
c. =PERCENTILE(B12:Bxxxx, 0.95) This should be in Cell B3 and get copied out to the last column used.
These calculations begin at B12 only if 10 rows get inserted (there is a Header Row [in row 11]).
xxxx can be any number of rows.
When this is all done I will need to create a Table with all the formulas results but I’ll worry about that later.
Thanks for any ideas on how to start.
Jim
I import several Sheets from another source (there can be 1 – 30 Sheets).
Each sheet’s actual name begins with “Sheet” (ie: Sheet1, Sheet1(2), Sheet1(3), etc).
The data that gets imported on each individual sheet can vary in “width” and “length”.
The columns are grouped in sets of 6, meaning Column B-G is one set of points, H-M (if present) is similar data points to B-G but a different set, N-S (if present) is also similar data points to B-G & H-M but a different set that I need to compare.
Column A is a Timestamp that will exist on each sheet but does not need to be evaluated.
There may only be 1-set of Columns with data (A-G) or there maybe several sets of columns with data.
Here’s what I’m after:
1) Insert 10 rows on every sheet which name begins with “Sheet”. There are other sheets in the workbook but their names do not begin with “Sheet”.
2) Perform the following 3 calculations for every “column used” starting with column B
a. =MAX(B12:Bxxxx) This should be in Cell B1 and get copied out to the last column used.
b. =Average(B12:Bxxxx) This should be in Cell B2 and get copied out to the last column used.
c. =PERCENTILE(B12:Bxxxx, 0.95) This should be in Cell B3 and get copied out to the last column used.
These calculations begin at B12 only if 10 rows get inserted (there is a Header Row [in row 11]).
xxxx can be any number of rows.
When this is all done I will need to create a Table with all the formulas results but I’ll worry about that later.
Thanks for any ideas on how to start.
Jim