How to paste data into a filtered table?

Macros "take entirely too long"?  Never heard that before.... I do with a click what used to take a week - but that's just me.

If you want to paste onto a filtered table, you need to first sort the table so that all the visible cells will end up together. Of course, the logic of sorting depends on your filtering and how many levels of filtering you are using, but that is the basic premise of the solution.

A macro, on the other hand, would work amazingly well in this situation, and would be faster than anything you could to manually. For those of you who woould like a macro solution, select the cells to be copied, run the macro below, and select the filtered cells where you want to paste values into visible cells only.

Sub PasteSelectionIntoVisibleCells()

    Dim rngC As Range

    Dim rngP As Range

    Dim rngA As Range

    Dim rngI As Range

    Dim i As Integer

    Dim xlCalcMode As XlCalculation

    Set rngC = Selection

    Set rngP = Application.InputBox("Select the filtered cell to paste into", , , , , , , 8)

    Set rngP = rngP.SpecialCells(xlCellTypeVisible)

    If rngC.Cells.Count <> rngP.Cells.Count Then

        MsgBox "You are copying " & rngC.Cells.Count & " cells into " & rngP.Cells.Count & " cells."

        Exit Sub

    End If

    i = 1

    xlCalcMode = Application.Calculation

    Application.EnableEvents = False

    Application.Calculation = xlCalculationManual

    For Each rngA In rngP.Areas

        For Each rngI In rngA.Cells

            rngI.Value = rngC.Cells(i).Value

            i = i + 1

        Next rngI

    Next rngA

    Application.Calculation = xlCalcMode

    Application.EnableEvents = True

End Sub

Hello,

I have not gone through all the above conversation. But, i guess the below code should work for you.

You need to select the cells you want to copy before running below code.

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

Bernie,

I had a simple idea that completely solves my problem but I don't know if it is possible using macros (but I think so). 

With your code we can copy data from an unfiltered table onto visible cells of a filtered table. However, if  the visible cells of the filtered table are already partially filled, this information will be lost because will be replaced by the elements of the unfiltered table, right? 

And this is my problem. I cannot lose data from my filtered table. And even using your code to copy / paste, it's a huge task to fill the filtered table with data from the unfiltered table in order to avoid replacements.

Is it possible to copy data from the unfiltered table and paste them into the visible cells of the filtered table by adding the elements of the two tables? Or alternatively, preventing blank cells of the unfiltered table to overlap the cells containing data of the filtered table? Or even, copying nonblank cells only from the unfiltered table (maintaining their original positions) and pasting them into the filterered table?

Any of these simple ideias solve my problem (because all blank cells in the unfiltered table are nonblank cells in the filtered table and vice versa) but I really don't know how to do it.

Is it simple (or possible) to slightly modify your code to do one of these procedures?

If yes, can you help me?

 I can't fully understand Excel VBA.

 :-/

Thx,

Rodolfo

So, you want to copy from multiple blocks.  How about multiple columns - are you selecting more than one column in each block? Are the blocks discontinuous by row and by column?

I was able to paste into 32 discontinuous rows in the filtered table - i think the limit is in the thousands of cells, so I am not sure why you saw a limit at 16 blocks for the destination.

> Is there a way (for example, using a macro) to make multiple selections of dispersed cells to copy them to another location of the spreadsheet?

Of course - macros can do almost anything. Would you want them "Concentrated" into the paste block?

Hi Bernie,

Thanks for your reply.

Your code works fine if I copy a block of contiguos rows from the unfiltered table to the visible cells of the filtered table. But I have 3 questions for you (or for someone who wants to help me):

1. Copy/Paste block of contiguos rows is not my case. I must be able to select rows that are not contiguous to copy them from the unfiltered table to the visible cells of the filtered table. And in this case, the code is not working properly even selecting the position for the rows in the filtered table in accordance with the position of the rows in the unfiltered table.
2. On the other hand, your code doesn't let me select more than 16 (not contiguous) rows to paste data onto the filtered table. I need to be able to select at least 20 (not contiguous) rows.

The last question is not related to the code and is the following:

3. When I make a multiple selection of dispersed cells in Excel, the copy command says something like that: "The copy command cannot be used on multiple selections". Is there a way (for example, using a macro) to make multiple selections of dispersed cells to copy them to another location of the spreadsheet?

Thx,

Rodolfo