Copy subtotal rows to new sheet. Stumped

joashm

New Member
Joined
Oct 28, 2010
Messages
2
I have been looking long and hard for a vba code to copy the subtotal rows in my worksheet to a new sheet and I am stumped.

My worksheet ("Analysis1") has 15 columns and rows which keep accumulating as data is enterd. I have autofiltered the sheet already to consider the rows I need only. I then subtotal 7 columns at each change in column F. Now i need to copy these 7 subtotal cells (F,Z,AA,AB,AC,AE,AH) from all the subtotal rows to a new sheet (Analysis2) so that I can run a report from it. I need to do this at a press of a button.

I am very new to coding and have tried different codes from different applciations to attemptted several things

Conditionally formating column F (where my subtotals are based from) to include the word "total" within the text, selects the subtotal cells. However it will only change the font etc. Can I conditionally format it and then select the entire row and copy and paste values to new sheet? I have tried several different codes but have no luck.

Another thing I have tried is, the subtotal rows have blank cells. I have tried to go down a column which has blank cells in the subtotal rows and identified the blank cell and tried to copy the entire row to a new sheet. But I have not been able to write a proper code for this either.

I do not want to go the special cells/visible cells route because it takes along time to copy my data. (When I write the code for this it copies the entire sheet, all 10,48576 rows in excel, to the new sheet and cannot do this with available memory).


I would sincerely appreciate any and all help.

Thank you
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
I do not want to go the special cells/visible cells route because it takes along time to copy my data. (When I write the code for this it copies the entire sheet, all 10,48576 rows in excel, to the new sheet and cannot do this with available memory).

Have you tried like this?

Code:
Sheets("Analysis1").UsedRange.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Analysis2").Range("A" & Rows.Count).End(xlUp).Offset(1)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,162
Messages
5,594,609
Members
413,917
Latest member
devansh02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top