Results 1 to 3 of 3

Thread: Copying a range of cells from one sheet to another not working
Thanks Thanks: 0 Likes Likes: 0

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

    Default Copying a range of cells from one sheet to another not working

    Hi! I'm trying to copy a range of cells to a second sheet and then delete the row from the first sheet. It seems like it should be pretty simple but the code is throwing an unknown error and highlighting the line "Sheets("Sheet1").Range(cell1, cell2).Copy"
    Any ideas why this would be happening?

    As a side note, ideally this code would paste into the first empty row in sheet 2, but I'm doing things one step at a time and want to get this first basic step debugged first.

    Code:
    Public Sub FindTag()
    
    
    'Creating an input box
    Dim myValue As Variant
    myValue = InputBox("Enter Tag being found")
    
    
    'Searching for the input to delete
    
    
          Dim x As String, firstCell As Variant, secCell As Variant, NextRow As Variant
          
          Dim Found As Boolean
          ' Select first line of data.
          Range("A2").Select
          ' Set search variable value.
          x = myValue
          ' Set Boolean variable "found" to false.
          Found = False
          ' Set Do loop to stop at empty cell.
          Do Until IsEmpty(ActiveCell)
             ' Check active cell for search value.
             If ActiveCell.Value = x Then
                Found = True
                Exit Do
             End If
             ' Step down 1 row from present location.
             ActiveCell.Offset(1, 0).Select
          Loop
       ' Check for found.
          If Found = True Then
            Column = Mid(ActiveCell.Address, 4, 1)
    cell1 = "A" & Column
            cell2 = "M" & Column
            
            'Copy the data
            Sheets("Sheet1").Range(cell1, cell2).Copy
            'Activate the destination worksheet
            Sheets("Sheet2").Activate
            'Select the target range
            Range("A2").Select
            'Paste in the target destination
            ActiveSheet.Paste
    
    
            Application.CutCopyMode = False
            
            ActiveCell.EntireRow.Delete
            MsgBox "Tag copied!"
          Else
             MsgBox "Value not found"
          End If
    End Sub

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,214
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Copying a range of cells from one sheet to another not working

    Try:
    Code:
    cell1 = Cells(Activecell.Row,"A")
    cell2 = Cells(Activecell.Row,"M")
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    4,447
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Copying a range of cells from one sheet to another not working

    If you allow me I show you some improvements in your code

    Code:
    Public Sub FindTag()
        Dim myValue As Variant, f As Range, lr As Long
        
        'Creating an input box
        myValue = InputBox("Enter Tag being found")
        If myValue = "" Then Exit Sub
        
        'Searching for the input
        Set f = Range("A:A").Find(myValue, LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            'first empty row in sheet 2
            lr = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1
            'Copy the data and paste
            f.EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & lr)
            f.EntireRow.Delete
            MsgBox "Tag copied!"
        Else
            MsgBox "Value not found"
        End If
    End Sub
    Let me know if you have any doubt.
    Regards Dante Amor

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
  •