Paste to visible cells only

[lichfields]

I'm trying to copy data from column A in a filtered list to the next column B. As column B is of course also filtered, I want to copy the visible cells (no problem) but then paste them to the visible cells (problem).

I've checked through earlier questions on this subject to find an answer but it seems there isn't one - at least not a straightforward one.

I did find a possible solution in the form of a "Code" as follows:

Worksheets("Sheet Name").Activate
Range("A2").Select
Selection.Copy
Range("A3:N1000").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Is this a macro thing? If so, how do you use it? I'm afraid I'm a macro virgin

Can anyone help?

 

[Petille]

Hi Petille

I don't understand where you're copying from and to. The macro does just copy the value of the source cell to the destination cell. If you are copying from one sheet to another the macro won't do that, it only works within the one worksheet.

The aim of the macro is to copy value into a filtered list of cells. If you're copying into a sheet that is not filtered you don't need to use the macro.

If you need to copy from one sheet to another you need to use excel copy and paste to get the source data into the destination worksheet. It may be possible to make it work across worksheets but I don't think it's necessary.

Ron

Ron you underestimate your macro lol. It worked perfectly. Here is my step by step:

1- Filter content on sheet T3x001c001.00 so that only lines with lime green show
2- Select range desired in column AC of sheet Requête300
3- Execute macro from that window: when pop up for inputing destination range
4- Click tab T3x001c001.00 and select destination range (column A, lime green rows only, as filtered), hit enter et voilà !

As I said, the macro works perfect in terms of moving column content (filtered or not) to a filtered column (on another sheet or same sheet)

What i wished was different is that it would paste unformated values. That would preserve destination format AND bring the values rather than the formulas (which posed the problem for my AR column which is a concatenated formulas column).

The sample i provided is with the implemented result.

 

[rondeondo]

Hi Petille

Now I see where the misunderstanding is coming from. You're not using my version of the macro, you're using the alternate version posted by Maxim_G.

My macro does paste values, the one from Maxim_G does paste special but doesn't specify values only so pastes with formats. That's why it works across worksheets, mine doesn't

I have modified the Maxim_G version to do paste values only. I've also put in an error handler for if the user clicks cancel on the input box.

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

 

[rondeondo]

Hi Guys

I've been trying out the Maxim_G version of the macro and unfortunately it doesn't work if pasting into a filtered list. It works fine pasting into an unfiltered sheet, but excel's built in copy and paste does that already. My macro was all about pasting values into a filtered sheet.

What it does do is do away with the need for two separate macros by putting in an input box.

 

[Petille]

 

[Petille]

I was off today and i took the time to test all above variations.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Capabilities:
[/TD]
[TD]Copy only visible from filtered column
[/TD]
[TD]Paste only to visible to filtered column
[/TD]
[TD]Paste values
[/TD]
[TD]Paste on another worksheet
[/TD]
[/TR]
[TR]
[TD]Maxim_G's
[/TD]
[TD]No*
[/TD]
[TD]Yes
[/TD]
[TD]Yes must add xlPasteValues to code
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]Rodeondo
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]No
[/TD]
[/TR]
</tbody>[/TABLE]
*A work around is to copy paste visible below the table and use that selection.

That table can help deciding which solution works best depending of each particular situation.

Thanks to both for useful tools!!

 

[gpr19]

Thanks Ron,

I also found another way to do it if anyone is interested:

Select the cells to copy, run this 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

Wow!!!! I joined MREXCEL just to thank you for this code! It worked perfectly!

 

[hossam]

Hi Guys

I've been trying out the Maxim_G version of the macro and unfortunately it doesn't work if pasting into a filtered list. It works fine pasting into an unfiltered sheet, but excel's built in copy and paste does that already. My macro was all about pasting values into a filtered sheet.

What it does do is do away with the need for two separate macros by putting in an input box.

thank you for great effort . Really I have benefited a lot from this thread but I want to know what can I do to get paste formula?

 

[rondeondo]

thank you for great effort . Really I have benefited a lot from this thread but I want to know what can I do to get paste formula?

Hi Hossam

A good question, maybe not as simple as suspected. If you do this

Range("M2").Formula = Range("L2").Formula

The exact formula is copied from L2 to M2. It is not treated as a relative formula copy. If this is what you want then good. If you want to have the formula use the same relative cells then you need to adapt this:

Selection.Copy
    Range("M2").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

This will be the same as selecting the paste special menu and choosing formula.

How this will fit in with my Macro I'm not sure as I've bypassed the whole copy and paste thing by setting a cell value to the value found in another cell.

Usually when you do copy and paste it works OK in a filtered list if copying just one formula through the selected cells. The problem is if the formulas as different beyond cell relativity.

Ron

 

[DataAnalyst]

I had the same problem and this is the solution that I use. It doesn't invovle writing macros, since I am still very very new to that and they tend to go haywire on me.

A Non-Macro Solution:
You can go to your excel options, click on customize, then from the drop down choose all commands, then scroll down until you see Select Visible Cells. Select add to customize wuick access toolbar.

To use that button: first copy the cell that you want, then select the cells that you want to copy into, click the select visible cells button, then paste as normal.

Hope this helps!

Thanks! I have written macros, but simpler is always better. I try not to spend time researching and writing macros since I have a lot of other work. Just what I needed!

 

[rondeondo]

Thanks! I have written macros, but simpler is always better. I try not to spend time researching and writing macros since I have a lot of other work. Just what I needed!

Hi DA,. I don't remember seeing the post from Mrs Ramos. but I'm afraid that solution doesn't work if you are pasting values into a filtered list. It works fine if you're pasting into a new sheet or into a section of sheet beyond the filtered section. That's why I wrote the macro.

Recap: Scenario:
You have 100 lines in an excel sheet.
You apply some filters so you can now see only 10 lines
You copy the contents of the first column and do paste values into another column within the filtered list.
The 10 values you copied will be pasted into 10 cells starting with the first one visible but in a contiguous block, aka the same 10 cells as if you removed the filter and then pasted. It will not paste into the visible cells.

I just tested again in Excel 2013 as I had heard it had been fixed.
I auto filled 60 cells in a column with the numbers 1-60. Starting at A2
Next column added a formula =isodd(a2) and copied it down. So now every second cell has true.
Auto Filter. Filter by column B to show only true
Now copy by any means column A into Column C starting at C2
Result. The data from Column A2:A61 that was visible is copied into C2:C31 but you don't notice until you remove the filter.

If you're coping into a completely blank column this is annoying. But if your C column contains data in the filtered out rows, it's just been overwritten. Again if you don't turn off the filters you don't find out until you have done more work. Al of which must be undone and re-done

Forgive my rant but I did a lot of google searching prior to writing the macro and found lots of reference to copying visible cells but it only works when pasting into an unfiltered sheet.

Ron