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

 

[GlennUK]

Can you explain what the shortfall is in that solution?

 

[alexenvalencia]

Hi Glenn

The problem with the second solution is that in cell A1 of the destination sheet, it has the formula "=source!A1" etc for all the cells, with no formating. I need to reproduce the effects of a copy and paste operation as executed via the right click menu, ie bring values, formulas and formatting across.

 

[GlennUK]

I need to simulate the effects of a plain sourcesheet.copy, destinationsheet.paste effect

What effects are you talking about, that aren't produced by the solution given?

 

[alexenvalencia]

Glenn - if you try the proposed solution you will see what I mean.

Copy a selection on one sheets (right click menu) and then paste it to a new sheet. Not paste special, use regular paste.

Now try roberts code. You will see that each cell is populated with a link to the equivalent cell in the original sheet. This is undesirable because if we change a value in the original sheet we will affect the destination sheet too.

 

[GlennUK]

Glenn - if you try the proposed solution you will see what I mean.

Copy a selection on one sheets (right click menu) and then paste it to a new sheet. Not paste special, use regular paste.

Now try roberts code. You will see that each cell is populated with a link to the equivalent cell in the original sheet. This is undesirable because if we change a value in the original sheet we will affect the destination sheet too.

no, the part of the code that does .Value=.Value converts the links to values ... unless I'm reading that incorrectly.

 

[alexenvalencia]

I need the equivalent of a paste.formula followed by a paste.formats

(ie a standard right and click paste and copy from one sheet to another)

 

[GlennUK]

So, it's the formats that you want copied? Do the formats vary by row? Or will the format from any visible row in the original data do?

And I'd say that you need the equivalent of paste.values and paste.formats, rather than formulas.

And you haven't said whether the code actually converts the link formulas to values, as I stated ... is that right or not?

 

[alexenvalencia]

Here is a demo sheet which you can download to see what I mean.

I have applied a filter to SOURCE sheet and would like to copy the entire data selection, without removing the filter, to DESTINATION (you can see on DESTINATION what the result should look like) preserving formating and formulas.

http://www.ingles4u.com/Copy ignoring autofilter.xls

 

[GlennUK]

Before I look at this ... can you tell me what you are aiming to do overall? Why do you want a duplicate copy of cells in another sheet? Why can't you copy the sheet object itself?