Announcement

Collapse
No announcement yet.

Move row to another sheet by command button

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Move row to another sheet by command button



    Hey guys happy holidays!!

    I have 7 columns. A:G
    Column G contains the sheetname
    A:F contains the data to be moved.
    I would like to be able to select any cell in column G and then press the command button and it remove the data from the sheet and paste it in the first blank row of the sheetname selected in G

    Any ideas?
    The Only Dumb Question Is A Question Not Asked.

  • #2
    Re: Move row to another sheet by command button

    Give this a try on a back up copy.
    You did not specify whether or not the delete the cells and shift them up or to clear the contents of the cell in column-G

    Code:
    Option Explicit
    
    Sub Move_Row()
        
        Dim nextrow As Long, i As Long, ws As Worksheet, sName As String
        
        Application.ScreenUpdating = False
        
        On Error Resume Next
        With ActiveSheet
            If Selection.Cells.Count > 1 Then Exit Sub
            If Not Intersect(ActiveCell, Columns("G:G")) Is Nothing Then
                i = ActiveCell.Row
                sName = .Range("G" & i).Text
                Set ws = Worksheets(sName)
                If Not ws Is Nothing Then
                    nextrow = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
                    .Range("A" & i & ":F" & i).Cut Destination:=ws.Range("A" & nextrow)
                    On Error GoTo 0
                    Set ws = Nothing
                Else
                    MsgBox "Worksheet " & sName & " does not exist in this workbook"
                End If
            End If
            
        End With
        
        Application.ScreenUpdating = True
    
    End Sub
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

    Comment


    • #3
      Re: Move row to another sheet by command button

      AAE, Thanks for your assistance. And to your question above,yes i would like it to clear the cells including G. Also shift the rows up. Thanks
      Last edited by yegarboy; December 27th, 2010, 23:37. Reason: Incorrect Info
      The Only Dumb Question Is A Question Not Asked.

      Comment


      • #4
        Re: Move row to another sheet by command button

        Instead of cutting the row can we copy and paste then clear contents?
        The Only Dumb Question Is A Question Not Asked.

        Comment


        • #5
          Re: Move row to another sheet by command button

          Bump
          The Only Dumb Question Is A Question Not Asked.

          Comment


          • #6
            Re: Move row to another sheet by command button

            Bump
            The Only Dumb Question Is A Question Not Asked.

            Comment


            • #7
              Re: Move row to another sheet by command button

              Code:
              Option Explicit
              
              Sub Move_Row()
                  
                  Dim nextrow As Long, i As Long, ws As Worksheet, sName As String
                  
                  Application.ScreenUpdating = False
                  
                  On Error Resume Next
                  With ActiveSheet
                      If Selection.Cells.Count > 1 Then Exit Sub
                      If Not Intersect(ActiveCell, Columns("G:G")) Is Nothing Then
                          i = ActiveCell.Row
                          sName = .Range("G" & i).Text
                          Set ws = Worksheets(sName)
                          If Not ws Is Nothing Then
                              nextrow = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
                              .Range("A" & i & ":F" & i).Cut Destination:=ws.Range("A" & nextrow)
                              .Range("A" & i).EntireRow.Delete
                              On Error GoTo 0
                              Set ws = Nothing
                          Else
                              MsgBox "Worksheet " & sName & " does not exist in this workbook"
                          End If
                      End If
                      
                  End With
                  
                  Application.ScreenUpdating = True
              
              End Sub
              AAE
              ----------------------------------------------------

              Forum Rules | Message to Cross Posters | How to use Tags

              Comment


              • #8
                Re: Move row to another sheet by command button

                Thats perfect AAE! Thanks for your patients and your help.
                The Only Dumb Question Is A Question Not Asked.

                Comment


                • #9


                  Re: Move row to another sheet by command button

                  Glad to have been of help.
                  AAE
                  ----------------------------------------------------

                  Forum Rules | Message to Cross Posters | How to use Tags

                  Comment

                  Working...
                  X