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

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
  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?
  - austin2134
    Copper Contributor
    Aug 30, 2022
    That was going to be my next question.
    From the original sheet, I only want 3/8 columns(A, E, and F). Once I figure that out, I want to place them into certain cells such as A, B, and C onto the next sheet.

cak980

Copper Contributor

May 06, 2022

HansVogelaar A-E

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

cak980
Copper Contributor
May 06, 2022
I must be doing something wrong, it's not working. I made some edits - sheet 2 is actually "Completed" and "Closed" is actually "X" and is column E

Forum Discussion

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

Resources