Page 1 of 5 123 ... LastLast
Results 1 to 10 of 44

Paste to visible cells only

This is a discussion on Paste to visible cells only within the Excel Questions forums, part of the Question Forums category; I'm trying to copy data from column A in a filtered list to the next column B. As column B ...

  1. #1
    New Member
    Join Date
    May 2004
    Location
    Sunny Geneva
    Posts
    16

    Default Paste to visible cells only

    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?

  2. #2
    Board Regular
    Join Date
    Jan 2004
    Location
    Fife, Scotland
    Posts
    1,368

    Default Re: Paste to visible cells only

    How about selecting visible cells only before pasting - would that work.

    Select your range in Col B, then shortcut to visible cells only is ALT;

    (long way is edit->goto>special>visible cells only), then paste.

    Does that do it

  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,644

    Default

    I don't think this is possible without code.
    If posting code please use code tags.

  4. #4
    New Member
    Join Date
    May 2004
    Location
    Sunny Geneva
    Posts
    16

    Default Re: Paste to visible cells only

    Thanks, folks

    I did try as you suggested but, as I pretty much expected from earlier responses, I got an error message:

    "The comand you chose cannot be performed with multiple sections. Select a single range and click the command again" (which defeats the object)

    Can anyone tell me how to use the code? i.e. like, where to put it?

    Sorry to be such a dim-wit but I'm fairly new to this game....

  5. #5
    Board Regular
    Join Date
    Jan 2004
    Location
    Fife, Scotland
    Posts
    1,368

    Default Re: Paste to visible cells only

    Okay, I don't think the first way will work. What about this. With the filtered list, select the top cell in col Bsay b2, enter the formula =A2. Copy cell B2, select the rest of colb down to the bottom of your data, do the Alt; bit paste.

    Then if you dont want formulas do edit, paste special, values.

    A bit long winded maybe

  6. #6
    New Member
    Join Date
    May 2004
    Location
    Sunny Geneva
    Posts
    16

    Default Re: Paste to visible cells only

    GorD

    Long-winded? It's nothing short of genius! Worked like a charm - and so simple! (Why didn't I think of it?)

    Thanks in abundance!!

  7. #7
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    4,965

    Default Re: Paste to visible cells only

    Hi,

    Here is another way:

    Assign this code to Button on your worksheet :

    Assumptions: List in Cloumn A is Named CopyRange and that in column B PasteRange


    Sub Copy_Filtered_Cells()
    ****Dim SourceRange As Range, TargetRange As Range
    ****Dim Cell As Range
    ****If ActiveSheet.FilterMode = True Then
    ********Set SourceRange = Range("CopyRange")
    ********Set TargetRange = Range("PasteRange")
    ********With Application
    ************.ScreenUpdating = False
    ************For Each Cell In SourceRange.SpecialCells(xlCellTypeVisible)
    ****************Cell.Copy TargetRange.Cells(Cell.Row)
    ************Next
    ************.CutCopyMode = False
    ********End With
    ****End If
    End Sub



    Hope this helps.
    Office/Excel 2007 Win XP

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  8. #8
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,644

    Default

    Will this not copy any blank cells as well.
    If posting code please use code tags.

  9. #9
    New Member
    Join Date
    May 2002
    Location
    Kalamazoo MI US
    Posts
    6

    Default Re: Paste to visible cells only

    I know this is an old thread, but I have found these steps to work in Excel 2007:

    I have a format and formula in a cell that I want to copy only into visible cells, in this case outlined subtotals.

    Collapse the outline (or filter your data) to the level you want visible.
    Copy the desired cell.
    Activate Select Visible Cells tool (I have this on my Quick Access Toolbar).
    Select the target range.
    Paste.

    Please test, but this works for me.

  10. #10
    New Member
    Join Date
    Jul 2010
    Posts
    4

    Default Re: Paste to visible cells only

    I was trying to apply GorD solution posted on May 14th 2004, yet it doesn't work for me
    I expected it to work since it did for lichfields, so I thought I was missing on smth. I suspect it is the "bit paste" part from: "Copy cell B2, select the rest of colb down to the bottom of your data, do the Alt; bit paste."

    I get the Alt+;, which is a shortcut for selecting only visible cells, still what's the deal with bit paste?

    could anyone elaborate on this, please ? I've got a 2007 MsExcel btw, in case this is relevant.

Page 1 of 5 123 ... LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com