Forum Discussion

Fennster's avatar
Fennster
Copper Contributor
May 16, 2021
Solved

Macro functions

Hi I’m new to macros and I want to be able to move a range of cells to another range of cells if a preceding cell in that range equals (say) YES. So, How do I test if a cell (A1 say in Sheet 1) equals YES and if it does, move cells A2:A8 to Sheet 2, cells A1:A7

Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    May 17, 2021

    Fennster 

    You have to loop through the cells:

     

    Sub MoveCells()
    Dim r As Long
    Application.ScreenUpdating = False
    For r = 2 To 100
        If Worksheets("PICK").Range("A" & r).Value = "YES" Then
            Worksheets("PICK").Range("C" & r).Resize(1, 3).Cut _
                Destination:=Worksheets("PRINT").Range("A" & r)
        End If
    Next r
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

6 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 16, 2021

    Fennster 

    Try this macro:

    Sub MoveCells()
    If Worksheets("Sheet1").Range("A1").Value = "YES" Then
        Worksheets("Sheet1").Range("A2:A8").Cut Destination:=Worksheets("Sheet2").Range("A1")
        Application.CutCopyMode = False
    End If
End Sub
    • Fennster's avatar
      Fennster
      Copper Contributor
      May 17, 2021

      HansVogelaar 

      Thanks for your help. I am a macro and VB beginner (I used to program in COBOL)

      I have got the script running but only for row 1. I have many rows with a YES that I want to copy over, how do I 'loop' the script to do row 2:100 say.

      I got this far:

      Sub MoveCells()
      If Worksheets("PICK").Range("A1").Value = "YES" Then
      Worksheets("PICK").Range("C1:E1").Copy Destination:=Worksheets("PRINT").Range("A1:E1")
      Application.CutCopyMode = False
      End If

      I did try  - If Worksheets("PICK").Range("A1:A100").Value = "YES" Then

      but I get an error

      Cheers, Pete

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        May 17, 2021

        Fennster 

        You have to loop through the cells:

         

        Sub MoveCells()
    Dim r As Long
    Application.ScreenUpdating = False
    For r = 2 To 100
        If Worksheets("PICK").Range("A" & r).Value = "YES" Then
            Worksheets("PICK").Range("C" & r).Resize(1, 3).Cut _
                Destination:=Worksheets("PRINT").Range("A" & r)
        End If
    Next r
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Resources