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]

Hi Ron,
Thanks for the heads up. When I used the select visible cells button it worked as intended. I filtered my data by department number. For each department number I wanted to paste department name in a new column. So I filtered for Dept. 1 and pasted the name into the visible cells of the blank Department Name column. Then I unfiltered and checked it and it had not pasted contiguously. I did this with four other departments and checked after each paste and it worked.

Each version of Excel I've used has worked differently. The one at my current job is different from my last job. My boss' works differently than mine. The latest version I've used is 2007. I'm not surprised that it's not working on later or different versions. I've become very cautious and always checking to make sure Excel works as intended. In my current version it doesn't always update formulas if rows are added or moved. I've learned to always check that!

 

[rondeondo]

Hi DA

So you put in one value and copied it through the filtered cells? yes I agree that will always work in any version of Excel. What will not work is if you copy all of the cells in a filtered column into an adjacent column. Prior to writing this macro I used to put in a formula say in C2 the formula of =A2 and copy the formula through the filtered cells.
Then unfilter the sheet
Then copy - paste values for the whole column.

This works effectively, the problem is when the filter being applied is based on a number of columns and you want to copy sections form one column to another then unfiltering and re-filtering becomes time consuming and you have to remember what you set the filter to. Sadly I had to spend quite a bit of time doing this hence the macro.

If you don't need to do this very often then there's no need for the macro.

 

[DataAnalyst]

Yes, it could get complex if you're using a lot of filters and a formula or two. That's good to know! Luckily I haven't had to do that yet. If I ever do I know where to look to see how!

 

[Peregrin]

HI,

I know this is a really old thread but lemme share you what I did.
This applies to all excel!!

I just numbered my range (i.e 1, 2 then dragged it, mine was up to 80)
Then on the left side of the filtered data, created the same numbers.
Then simply use the VLOOKUP formula, then paste value!

Its easy to do if you are trying to avoid all those MACROs!

 

[faddies]

Sub Copy_Filtered_Cells()
    Set from = Application.InputBox("Select range of Cell to copy", Type:=8).SpecialCells(xlCellTypeVisible)
    'Selection.SpecialCells(xlCellTypeVisible).Select
    'Set from = Selection
    Set too = Application.InputBox("Select range to Paste Copied Cells", 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

above code will work for you. and to learn ho to run macro refer my step by step guide. How to Run Macro in Excel | Fahad Bin Zafar

 

[RCONDADO]

The following macro was successfully run. But, I need of the same macro, that copie only values. Do you know what I have to modify to paste only values?

Sub Copy_Filtered_Cells()

Dim from As Variant
Dim too As Variant
Dim thing As Variant
Dim cell As Range

'Selection.SpecialCells(xlCellTypeVisible).Select

    Set from = Selection.SpecialCells(xlCellTypeVisible)
    Set too = Application.InputBox("Select range to copy selected cells to ( Visible cells only)", 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

Thank you to all!

 

[ukmikeb]

Hi
Try :-

thing.PasteSpecial(xlPasteValues)

hth

 

[RCONDADO]

Thank you Mike!
This is it!

 

[matinablr]

Thank you Mike!
This is it!

could it be possible for us who don't know how to uses macros tell from the begginning how to make it so as be able to paste in filtered cells through macro..

 

[ukmikeb]

Hi

Welcome to the MrExcel Forum.

Here is a thread :-
http://www.mrexcel.com/forum/excel-questions/817826-visual-basic-applications-help-me-please.html

that in Post #6 gives instructions on how to enter a macro.

For more specifics on Paste filtered cells search this site with those three words.

If you then have further questions please start a new thread describing your problem.

hth