Copying and Pasting From/Into Filtered Data and Having Trouble

[Kramklop]

My overall goal is to transfer Data from Spreadsheet A that has been filtered into Spreadsheet B that has also been filtered. Copying and pasting refuses to cooperate given the filtering, I've also tried using an equation to make the cells equal to one another but I can't drag and drop that either without it applying to the filtered/hidden cells. I found the following formula:

Sub Copy_Filtered_Cells()
Set from = Selection
Set too = Application.InputBox("Select range to copy selected cells to", Type:=8)
For Each cell In from
cell.Copy
For Each thing In too
If thing.EntireRow.RowHeight > 0 Then
thing.PasteSpecial
Set too = thing.Offset(1).Resize(too.Rows.Count)
Exit For
End If

Next
Next
End Sub



This formula worked for some of the data but then suddenly stopped giving me an error. The Error I'm getting is "Paste Special Method of Range Class Failed". All attempts to use this have failed after the initial success and I'm unsure what happening to change it. The cells being copied and then pasted range from small words to numbers. I'm not sure exactly what is wrong with thing.pastespecial but it seems to be the issue.

Information on whatever is the simplest and most accurate way of copying the filtered information from Sheet A and Pasting it into SheetB would be greatly appreciated.

 

[JackDanIce]

Without being in front of your PC monitor, can you describe what data is in SheetA and SheetB and what filter is being applied?

State:
- What row is the header on (both sheets)
- Which column is being filtered and with what value (both sheets)

In addition:
- Are the filters applied before the macro is run or earlier in the macro than the code you are showing?
- Can you also provide an example of what your file looks like before you run the macro and what it should look like after you run the macro?

Again, bear in mind you are describing to someone reading this thread who cannot see your monitor or file, so include any other useful information.

 

[Kramklop]

Hi,I feel like the concept itself (pasting information in a sheet that has rows hidden from filtering) should have a somewhat accessible solution but I'll provide more information to help out.

The header is in row 1. Multiple columns are being filtered, almost exclusively by number requirements, such as Column X is filtered by values greater than 2 or Column Y is sorted by any values that aren't 0.

In a normal scenario this is just a cut and paste operation. In this scenario the only way to copy the sets of data I want to transfer together are to apply filters to grab that data and then paste it into a sheet that has been filtered as well.

 

[JackDanIce]

It seems like we're at different points of view here.

You can use .SpecialCells(xlCellTypeVisible).Copy to copy all the filtered data that is visible in a defined area or range. Why would you test for a row height of zero and what would you do if this had been manually changed to zero, independent of a filter applied or not?

If the data is filtered on both sheets with the same criteria and the maximum data sizes (mas row and max column) are the same in both sheets, then you probably can copy and paste the data as you need but not in the manner your code suggests.

What I mean by different points here is you're describing the problem without specifics or considering the fact, there may be VBA features you're unaware of that can achieve what you need.

In other words, I suspect what you need doing can be done but if you do not give sufficient and precise information, we're at different points of view of understanding...

Again, bear in mind a reader can't see your PC monitor so how you're explaining the problem may not be understood in the same way you do, by someone who's only reading your description without any reference points.