Paste to visible cells only

Likes Likes:  0
Page 1 of 8 123 ... LastLast
Results 1 to 10 of 71

Thread: Paste to visible cells only

  1. #1
    New Member
    Join Date
    May 2004
    Location
    Sunny Geneva
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,426
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    72,961
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,426
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    6,246
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    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 2010 64Bits -- Win10 64Bits

    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
    72,961
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

User Tag List

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