Copy/Paste Dilemma

[cymraeg]

Have viewed similar threads, but cannot find one that adequately answers problem.

Currently have a macro which at the end produces a report with subtotals on specific columns - M to Q inc, plus V,W, and Z. The number of rows in the 'raw' file can vary, as well as the number of rows that are subtotaled. What I need is some vba which allows for this variance and will copy the subtotaled value from each of the columns into another sheet in the workbook with the same column headings. The sheet name of the destination will always be 'Summary', but the source sheet name is currently variable.

Can this be done, perhaps using the 'at each change in' reference from the subtotal command, as a basis ?

[stnkynts]

A couple of questions:

1. You said the source sheet name will change but is there always only 2 sheets (the source sheet and the summary sheet)?
2. Is the subtotal row on the source sheet the last used row?
3. Can you upload a copy of your workbook to make things easier?

[cymraeg]

In answer to your first question - yes
In answer to your second question - no. The source data can contain up to 20 subtotal rows, each with a different amount of rows of data
In answer to your third question - not sure. I do not see a button in the quick reply section that will allow me to do this. Please bear with me as I am new to this forum. Any guidance would be appreciated.

[stnkynts]

The source data can contain up to 20 subtotal rows, each with a different amount of rows of data

Here is going to be the hard part. We have no way of knowing where these subtotal rows are and how to reference them. Its akin to shooting blind.

With regards to attaching a workbook click the Go Advanced button next to the Post Quick Reply button. You should be able to see it if you scroll down.

[cymraeg]

File attached. Thanks for the procedure instruction.

Presuming that the subtotal copy/paste is out of the question, is there a way to copy/paste just the grand total, or is that also out of the question ?

[stnkynts]

Give this a try and lets see how close I got:

Please Login or Register  to view this content.

[cymraeg]

Your solution worked beautifully. Frankly I am astonished, I would never have achieved this. Thank you