Forum Discussion

cak980's avatar
cak980
Copper Contributor
May 05, 2022

Using VBA to move a range of data to a different sheet when complete (need the for dummies version)

I'm trying to setup a macro so that when a task is completed (indicated with X), it automatically gets moved to a separate tab and deleted from original location.  I was able to accomplish this but i...
Macros and VBA
cak980's avatar
cak980
Copper Contributor
May 05, 2022
Sub move_rows_to_another_sheet()
For Each myCell In Selection.Columns(4).Cells
If myCell.Value = "Closed" Then
myCell.EntireRow.Copy Worksheets("Sheet2").Range("A" & Rows.Count).End(3)(2)
myCell.EntireRow.Delete
End If
Next
End Sub
  • Rajesh950's avatar
    Rajesh950
    Copper Contributor
    Apr 07, 2023

    hi team

    i need a code in which i can move my cells data to another sheet ,

    like i have data in cells A to I

    and if i write c in cell I1 then it should move data from cell A to I into another worksheet

    thanks

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 05, 2022

    cak980 

    Thanks!

    Because you use EntireRow, the entire row gets moved.

    Which column or columns in the row would you like to move?

    • austin2134's avatar
      austin2134
      Copper Contributor
      Aug 30, 2022
      I found the same code online and i'm changing it to fit my file.
      I also only want to extract from certain columns "A","E", and "F". What would you change in this row:
      myCell.EntireRow.Copy Worksheets("Sheet2").Range("A" & Rows.Count).End(3)(2)
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Aug 30, 2022

        austin2134 

        Do you want to copy them to columns A, E and F, or for example to columns A, B and C?

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        May 06, 2022

        cak980 

        Try this:

         

        Sub move_rows_to_another_sheet()
    Dim myCell As Range
    Dim w As Worksheet
    Dim s As Long
    Dim m As Long
    Dim t As Long
    Dim rng As Range
    Set w = Worksheets("Sheet2")
    t = w.Range("A" & w.Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    m = Selection.Rows.Count
    For s = m To 1 Step -1
        If Selection.Range("D" & s).Value = "Closed" Then
            t = t + 1
            Set rng = Range("A" & Selection.Range("D" & s).Row).Resize(ColumnSize:=4)
            rng.Copy Destination:=w.Range("A" & t)
            rng.Delete Shift:=xlShiftUp
        End If
    Next s
    Application.ScreenUpdating = True
End Sub

         

         

Resources