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?

 

[DataAnalyst]

Wow, this is awesome! Very cool!

Excel is so inconsistent and glitchy I never assume it will work the way I think it will, or the way it's supposed to. The other day I used the visible cells only button and then checked to make sure it pasted correctly even though I've done it before. You can never know with this Excel. This Excel works differently from the Excel at my last job and differently from my boss's version at this job. It may be the way the employer customizes it, or it may be something else - It's good to always double-check that functions worked the way you expected.

Good job!

 

[DanS123]

Hi guys,

I don't know if someone found a better solution to this but I also struggled with the problem of pasting data into filtered cells and here is my workaround. I...

9. Go back to your destination table and use vlookup, looking up the key in column A in the unfiltered source data. The formula would look like this: =VLOOKUP(A1 (or whatever your first cell is);'[unfiltered source data file.xlsx]New sheet'!$A$1:$B$5000;2;FALSE). Change the formula as you need and apply it to all visible cells so that you don’t mess up the hidden cells (In excel 2010 and 2013 you can just pull down the formula with your mouse and it will automatically be applied to visible cells only). This will paste the data you want to only the 5000 cells of the 10,000 cell table without changing the hidden 5000 cells. The you can delete column A with the keys you assigned, if you want.

Any chance you could possibly explain step 9 in layman's terms; sorry, not brilliant with excel and sounds like this could solve a major issue for me.

Thanks in advance.

 

[DataAnalyst]

Any chance you could possibly explain step 9 in layman's terms; sorry, not brilliant with excel and sounds like this could solve a major issue for me.

Thanks in advance.

Hi Dan,
I'm in a bit of a crunch so I'm making a quick answer. The VLookup formula is very convenient for bringing data from another table. Here is an example of one I use:
=VLookup(G2, Values!$A$2:$C$16000, 3, False)
To bring values in from a table on the Values sheet in the workbook. The Excel help file will give you details of the syntax.
As I understand it Mish6 is saying you can filter your data and use the Visible Cells Only button or command to paste the VLookup formula into only the filtered rows and bring in data. You can find the Visible Cells Only button in the Customize Toolbar section of Excel.
You might want to do the VLookup formula in just a few rows to make sure it works as you expect before you paste it into all your visible rows. Afterwards use the filter to check the results for the #NA error. This means VLookup didn't understand the source data for that row.
Hope this helps
</SPAN>

 

[binkrust]

Some workarounds available do work for normal pasting to filtered selections, but I couldn’t find one that could “PASTE VALUES”.
I needed to Copy from a filtered selection & “Paste Values” to a filtered selection.

(Apparently an “Add-In” called Paste Buddy from Excel Campus can do it, which is free to try, but then around $14.95 to buy) or you can try this method that worked for me in Excel 2010 on Windows 7.

Here’s how to do it:

Firstly, you need to have the “Select Visible Cells” function available. You can use the shortcut [Alt + ;] or better still, put it in your Toolbar Ribbon: (Right click in toolbar ribbon, Customize the ribbon, Choose Commands from, Commands Not in the Ribbon, Select Visible Cells, then add it where you want it to be placed).

Secondly, you need to copy & add this macro to a button on your spreadsheet:

Sub CopyPasteToVisibleCellsOnly()
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 Paste:=xlPasteValues​

Set too = thing.Offset(1).Resize(too.Rows.Count)​

Exit For​

End If​

Next​

Next​

End Sub

PROCEDURE:
-Highlight the filtered cells to be copied
-Click on “Select Visible Cells”
-Run the macro (a pop up box appears)
-Highlight the filtered cells you want to paste values to
-Click on “Select Visible Cells” again
-Then click “OK” in the pop up box

Done!
Sorry it’s long winded & includes a macro, but I couldn’t get any other method to work.
No doubt someone could improve this macro further, by adding a prompt for which type of “Paste Special” you want to use perhaps.

 

[therealexcelgeek]

I had a similar issue myself. I wanted to copy some data from sheetA (a cut down version of sheet B) to sheetB (the master spreadsheet I had filtered).
My issue was when I copied back to the master spreadsheet (sheetB) I filtered it and it wouldn't paste in correctly.

Here is my simple solution, instead of complicated code or settings, I simply sorted the data (on sheetB) instead of filtering the data, then simply
copy and pasted the data to the new location (sheetB).

So, lets assume on sheetB you originally filtered on a column called "status". Instead of filtering for that, sort on that "status" value and then
copy sheetA to sheetB.

Works well for me.

 

[Onur Tufan]

Hello,

Actually I was here to find a solution, however I have found no solution therefore I had to find a solution myself; and here it is:


Firstly, my goal was aligning the numbers in a specific column as sequential (consecutive). (after 61002, 61003 must be seen instead of 877, then go on like that). However all the numbers had different spaces between them (in a column), and I cannot align them on different sheet and past as aligned. They used to look like below:


[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]61000 = {[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]}[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]61001 = {[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]}[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]61002 = {[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]}[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]877= {[/TD]
[/TR]
</tbody>[/TABLE]


I aimed to align them as consecutive ( 61000, 61001, 61002, 61003, 61004,) without deleting any pharanthesis, and deleting any space (I couldn't delete even any blanks because I have some valuable data very beside them).

Step 1:
Insert new column to their left side, and new row to top of them (for filtering them later)
Then, write 1 beside our former A1 (which is 61000 = {, and align 2,3 under new written 1. And drag them until to the end of your data (1,2,3,4....4012,4013,4014 etc.)
And write a,b,c,d, ..etc-(as much as you need) to top of them. Like:

[TABLE="width: 192"]
<colgroup><col width="64" style="width:48pt" span="3"><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]a[/TD]
[TD="width: 64, bgcolor: transparent"]b[/TD]
[TD="width: 64, bgcolor: transparent"]c[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]61000 = {[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]name = "A"[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]country = X[/TD]
[/TR]
</tbody>[/TABLE]

P.S. : If your data is too long, and it takes too long to drag, put any letter to new A1, then copy it (ctrl+c). Then quickly scroll to the end of your data from the right hold (hold and take it to the very down).
Paste your A1 to very end of your data in the same column. Then this time, copy that letter which you just have pasted (on Axx). Then; "CTRL+SHIFT+Up Arrow" and, "CTRL+V" . All column is same letter now, this time fill A2 and A3 with 1 and consequently 2. Select them altogether and click their down right corner and let excel to align it to the very end of your data.



Step2:

Copy first two column to the "Sheet 2" (new page, let's name it as replica sheet); then copy second column and paste it to very beside it (to column C) "We name it as replica column". Filter first row, and delete needless symbols from the third column (replica column); like;
~[TABLE="width: 192"]
<colgroup><col width="64" style="width:48pt" span="3"><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]a[/TD]
[TD="width: 64, bgcolor: transparent"]b[/TD]
[TD="width: 64, bgcolor: transparent"]bb[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]61000 = {[/TD]
[TD="bgcolor: transparent"]61000 = {[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"]}[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"]61001 = {[/TD]
[TD="bgcolor: transparent"]61001 = {[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent"]}[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent"]61002 = {[/TD]
[TD="bgcolor: transparent"]61002 = {[/TD]
[/TR]
</tbody>[/TABLE]

As you see, second column has parenthesis, third one has not.
Then copy your first and third column (replica column) to the third sheet (very new sheet) side by side.

Step 3:

In the third sheet, columns will be "a & bb"; filter them and select just bb's blanks from the filter. Then delete them all (all the rows which includes blanks between the values). Then it should be seen like this:

[TABLE="width: 128"]
<colgroup><col width="64" style="width:48pt" span="2"><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]a[/TD]
[TD="width: 64, bgcolor: transparent"]b[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]61000 = {[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"]61001 = {[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent"]61002 = {[/TD]
[/TR]
</tbody>[/TABLE]

Beside B columnd; amend your values by aligning them like:
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"><tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]61000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]61001[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]61002[/TD]
[/TR]
</tbody>[/TABLE]
Then after selecting them altogether, align them until the end of your data:

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"><tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]61000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]61001[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]61002[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]61003[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]61004[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]61005[/TD]
[/TR]
</tbody>[/TABLE]

For the same appearance; with B column; use the formula:
=C2&" = {"

Because of you'd used formula, you can't use this cells later. So copy all the column and paste onto itself as values (after clicking D Column, all values in the column will seen as selected, don't move your Mouse and right click to Column D again {right click to D}, and "paste special--> values"

Step 4:

Hold sheet 3 like that.
On sheet 2; delete your bb column. Then use VLOOKUP: Lookup value: Sheet2's A column; Table array: Sheet3's A to D ;col_index_sum is 2, and Range_lookup is 0.
in fact its like below:
=VLOOKUP(A:A;Sheet3!A:D;4;0)


Step 5:

Copy C column in Sheet2 and do the same "paste values" process. Filter again all #N/A's and delete them all (Not rows, only cells - just select them all and press "delete").

Now, select Sheet2's B column and select old values by selecting all and unselecting only blanks, paranthesises and others (because numbers are too much, you can select them one by one). *our aim is to select numbers and delete them (not rows, only cells again) *

Now,

Select column b and press CTRL+G --> special --> blanks , then right click one of the selected cells and delete them all by choosing "Shift cells left".

Step 6:

We're almost finished...
Copy sheet2's first two column (we just have them anyway), then paste them on to Sheet1's first two column.

Now, your data is aligned consecutively.


This example may not be exactly the same with your data of course, however it can give some idea, and help you out to find new solutions.
I hope it Works for you too.

 

[Mariahballmer]

Will ctrl D paste/fill values in visible cells only?

 

[katzpauz]

FINALLY! I've been looking all day for a solution to this dilemma. Your macro worked like a charm. It is a bit slow, but luckily I'm dealing with less than 200 rows so 10-15 seconds is not too long to wait. Thanks again!

 

[katzpauz]

FINALLY! I've been looking all day for a solution to this dilemma. Your macro worked like a charm. It is a bit slow, but luckily I'm dealing with less than 200 rows so 10-15 seconds is not too long to wait. Thanks again!

That was meant for "binkrust" and the macro that he/she posted on 28 April 2015.

 

[jordhall07]

First, I want to say thanks to everyone who has contributed to this thread, as it helped me a lot when putting together a solution that addresses more than one column of data being copied to another range. The code I implemented today is included below; hope it helps others as well. Note that this is an adaptation of code originally posted by Maxim_G. If anyone else finds a way to include capability to also deal with hidden columns as well, let me know.

Sub Paste_Values_To_Filtered_Cells()


'Does not work with hidden columns, only with hidden rows


Dim SrcRng As Range
Dim SrcRngVisible As Range
Dim DestRng As Range
Dim SrcCell As Variant
Dim DestCell As Variant
Dim DestRngColumnCount As Long
Dim DestRngRowCount As Long
Dim msgAnswer As Variant


On Error Resume Next
'Select source range
Set SrcRng = Application.InputBox("Select source range", Default:=Selection.Address, Type:=8)
On Error GoTo 0
If SrcRng Is Nothing Then Exit Sub


On Error Resume Next
'Select destination range
Set DestRng = Application.InputBox("Select destination range", Type:=8)
On Error GoTo 0
If DestRng Is Nothing Then Exit Sub


Application.ScreenUpdating = False


'Calculate # of columns, # of rows, first column for later reference
SrcRngColumnCount = SrcRng.Columns.Count
DestRngColumnCount = DestRng.Columns.Count
DestRngRowCount = DestRng.Rows.Count
DestRngFirstColumn = DestRng.Cells(1, 1).Column


'Determines if the source and destination ranges have the same number of columns
If SrcRngColumnCount <> DestRngColumnCount Then
    msgAnswer = MsgBox("The source and destination ranges do not have the same number of columns." & vbCrLf & vbCrLf & _
        "Please adjust your selected ranges and try again.", vbExclamation + vbOKOnly, "Selected Ranges Invalid")
    Application.ScreenUpdating = True
    Exit Sub
End If


'Set source to only be visible cells
Set SrcRngVisible = SrcRng.SpecialCells(xlCellTypeVisible)


'Loops through each visible cell in the source range
For Each SrcCell In SrcRngVisible
    'Loops through each cell in the destination range
    For Each DestCell In DestRng
        'Only takes action if cell is visible (RowHeight is not 0)
        If DestCell.EntireRow.RowHeight > 0 Then
            'Source cell value is entered into destination cell if not empty text string
            If SrcCell.Value <> "" Then
                DestCell.Value = SrcCell.Value
            End If
            
            'Determines whether there are multiple columns of values that are being copied
            If DestRngColumnCount > 1 Then
                If DestCell.Column = DestRngFirstColumn + DestRngColumnCount - 1 Then
                    'Move to next row, reset column
                    Set DestRng = DestCell.Offset(1, (DestRngColumnCount - 1) * -1).Resize(DestRngRowCount, DestRngColumnCount)
                Else
                    'Move to next column
                    Set DestRng = DestCell.Offset(0, 1).Resize(DestRngRowCount, DestRngColumnCount)
                End If
                Exit For
            Else
                'Move to next row
                Set DestRng = DestCell.Offset(1, 0).Resize(DestRngRowCount, DestRngColumnCount)
                Exit For
            End If
        End If
    Next
Next


Application.ScreenUpdating = True


End Sub