Copying en entire selection when it is AUTOFILTERED

[alexenvalencia]

My original question:

I have a block of data which I want to select in order to copy it.

It begins in column A and runs to column AU. It begins in row 2 and runs down a number of rows (typically 2000 rows but it varies) until there is no data in the rows.

Column C is the KEY and has data all the way till the last row so we should use this to determine how far down we need to make the selection.

Roberts excellent solution:

[/FONT]
[FONT=Tahoma]Sub Macro1()[/FONT]
 
[FONT=Tahoma]Dim rngMyDataRange As Range[/FONT]
 
[FONT=Tahoma]Set rngMyDataRange = Range("A2:AU" & Range("C" & Rows.Count).End(xlUp).Row)[/FONT]
 
[FONT=Tahoma]End Sub[/FONT]
[FONT=Tahoma]

</PRE>
A problem remains: The block of data is often autofiltered by the user with the filter being applied to COLUMN C (the key column). I need to copy the ENTIRE block of data to a new sheet, while not removing the user defined autofilter as he doesnt want to lose his filter settings (a valid option is to remove the filter, remember the settings, execute the copy and reapply the filter, but there must surely be a simpler solution).

Robert suggested:

[FONT=Tahoma]Sub Macro1()[/FONT]
 
[FONT=Tahoma]   Dim strSourceTab As String, _[/FONT]
[FONT=Tahoma]       strDestinTab As String[/FONT]
[FONT=Tahoma]   Dim lngLastRow As Long[/FONT]
 
[FONT=Tahoma]   'Source tab name (change to suit)[/FONT]
[FONT=Tahoma]   strSourceTab = "Sheet1"[/FONT]
[FONT=Tahoma]   'Destination (output) tab name (change to suit)[/FONT]
[FONT=Tahoma]   strDestinTab = "Sheet2"[/FONT]
 
[FONT=Tahoma]   'http://excelvbamacro.com/how-to-find-the-last-row-that-contain-data-in-excel.html[/FONT]
[FONT=Tahoma]   lngLastRow = Sheets(strSourceTab).UsedRange.Row - 1 + Sheets(strSourceTab).UsedRange.Rows.Count[/FONT]
 
[FONT=Tahoma]   With Sheets(strDestinTab).Range("A2:AU" & lngLastRow)[/FONT]
[FONT=Tahoma]       .Formula = "=" & strSourceTab & "!A2"[/FONT]
[FONT=Tahoma]       .Value = .Value 'Converts the above formula (link) range to values.  Comment out / delete if not needed.[/FONT]
[FONT=Tahoma]   End With[/FONT]
 
[FONT=Tahoma]End Sub[/FONT]

</PRE>
Which is a nice attempt but unfortunately I need to simulate the effects of a plain sourcesheet.copy, destinationsheet.paste effect.

Does anyone have any ideas? Is it possible to modify roberts code to have the desired effect or is there an even simpler solution, such as a variation on the .copy method that actually includes underlying filtered rows?

Many thanks,

Alex

 

[alexenvalencia]

Hi Glen,

Because in the destination sheet, I intend to consolidate the data using column C as a key, that is to say if a value in column C repeats, I apply an aggregating algorithm. I want to retain a copy of the sheet before aggregation.

 

[GlennUK]

Why don't you copy the sheet itself?

 

[alexenvalencia]

Is there a way to copy a sheet and specify the destination sheet name? (instead of sheet1 etc)

Although the above may be useful, it owuld still be useful to discover a more efficient way to copy an enitre data selection that is filtered without having to copy the sheet!

 

[GlennUK]

Copy the sheet, then rename it. Simple as that .... although in your case you'd then remove the filter on the copied sheet.

 

[alexenvalencia]

There must be a less expensive method of copying a filtered selection than this!

 

[GlennUK]

But you are asking to copy a filtered selecion as if it isn't filtered, without removing the filter. I think it is a good method considering the constraints you're demanding.

 

[alexenvalencia]

Cheers Glenn!