Results 1 to 3 of 3

Thread: Paste selected cells
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Paste selected cells

    Hi,

    I Have a piece of code below which loop searches an array for a specific string, once found it selects the cells which contain the text and loops until finished.

    What I want to do now is copy the selected cells and paste them to another workbook but I'm having difficulty.


    Any help will be appreciated
    Code:
    'if the searchreturns a cell
        If Not c IsNothing Then
            'note theaddress of first cell found
            firstaddress =c.Address
            Do
               'FoundCells is the variable that will refer to all of the
                'cellsthat are returned in the search
                IfFoundCells Is Nothing Then
                    SetFoundCells = c
                Else
                    SetFoundCells = Union(c, FoundCells)
    
                End If
                'find thenext instance of "er99"
                Set c =.Cells.FindNext(c)
            Loop While Notc Is Nothing And firstaddress <> c.Address
    
            'after entiresheet searched, select all found cells
           FoundCells.Select
            FoundCells.CopyWorksheets("Sheet2").Range("A1")
    Last edited by Fluff; Jun 6th, 2019 at 08:37 AM.

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,875
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Paste selected cells

    Please post the complete sub
    thanks

    - click on # icon above post window first and then ...
    [ CODE ] paste your code inside the tags [ /CODE ]
    Last edited by Yongle; Jun 6th, 2019 at 08:54 AM.

  3. #3
    New Member
    Join Date
    Jun 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Paste selected cells

    Apologies, I wasn't able to edit the post due to the timeout.

    Please see the full sub below:

    Code:
    Sub FindError()
    
    
    Dim c As Range, FoundCells As Range
    Dim firstaddress As String
    
    
    Application.ScreenUpdating = False
    With Sheets("Sheet1")
        'find first cell that contains "er99"
        Set c = .Cells.Find(What:="er99", After:=.Cells(Rows.Count, 1), LookIn:=xlValues, LookAt:= _
        xlPart, MatchCase:=False)
        
        'if the search returns a cell
        If Not c Is Nothing Then
            'note the address of first cell found
            firstaddress = c.Address
            Do
                'FoundCells is the variable that will refer to all of the
                'cells that are returned in the search
                If FoundCells Is Nothing Then
                    Set FoundCells = c
                Else
                    Set FoundCells = Union(c, FoundCells)
                    
                End If
                'find the next instance of "er99"
                Set c = .Cells.FindNext(c)
            Loop While Not c Is Nothing And firstaddress <> c.Address
                    
            'after entire sheet searched, select all found cells
            FoundCells.Select
            FoundCells.Copy Worksheets("Sheet2").Range("A1")
            
            
        Else
            'if no cells were found in search, display msg
            MsgBox "No cells found."
        End If
    End With
    Application.ScreenUpdating = True
    
    
    End Sub
    My main issue is getting 'FoundCells.Copy Worksheets("Sheet2").Range("A1") to work correctly.

Some videos you may like

User Tag List

Tags for this Thread

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
  •