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) equal...
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
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
      • Fennster's avatar
        Fennster
        Copper Contributor
        May 20, 2021
        Hi Hans,
        After a lot of fiddling, I finally got it to work, fantastic and many thanks.
        I Didn't realise that the positioning of the syntax is as important as the function.
        What I would like to do now is be able to delete or mark, from my original 'list of data' those items (rows) that have been selected with a YES.
        Basically within the workbook I have 3 sheets.
        MAIN - Contains the full list of data.
        PICK - That I copy all data from MAIN into but then set up my Y selection.
        The user puts a YES against the required items for that day.
        Then I run your super SUB that copies the YES items into a third PRINT Sheet
        Thereby, creating my Print List for the day.
        Your next challenge is:
        What I would like to do is 'mark' on the original list MAIN that the row has previously been
        selected within the PICK sheet. I think I would need to give each row in the MAIN sheet a Seq No so that the lines in the PICK Sheet can refer back to the rows in the MAIN Sheet and perhaps 'shade' the row or mark the row somehow, to show that it has previously been selected??
        Would that be possible,
        For now, I will get the user to shade the rows in the MAIN sheet, using the printed list as a guide.
        Thanks for all your help so far, Cheers, Pete
        PS: My brother lives in Holland, a place called Drunen
        PPS: Am I allowed to give you my email address?

Resources