How to copy subtotalled cells to a new worksheet (in a macro), wi.

[Guest]

Need to copy subtotals to a new worksheet as one of many steps included in a
macro. The row number for the subtotals will change each month. Any ideas
how to include this copy & paste in a macro without grabbing the wrong row on
the 2nd and subsequent months? Thanks!

 

[Guest]

You could use named ranges (name the cells using the name box in upper left
corner). Then, instead of

Range("A1").Copy

Use Range("NamedRange").Copy


Or, if your subotals are the last row in a particular column, you could run
a search on this forum for "last row" and you'll find the syntax for finding
the last cell in a column that has data in it.

 

[Dave Peterson]

Record a macro when you apply the subtotals.

Use the outlining symbols at the left to hide what you want hidden.

Select your range
edit|goto|special|visible cells only

Copy and paste.

That recorded macro should be pretty close to what you need.