Copy subtotal rows to new sheet. Stumped

[joashm]

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

 

[VoG]

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?

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

 

[joashm]

Works like a charm.

Thank you very much.