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
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?
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      May 20, 2021

      Fennster 

      Hi Pete,

       

      I live about 30 miles north-east of Drunen. And yes, you can give me your email address if you like.

       

      You will indeed have to add some kind of unique identifier to the items, for example a sequence number.

      Which column would you like to use for that?

      • Fennster's avatar
        Fennster
        Copper Contributor
        May 21, 2021
        Hi Hans,

        It's going to be easier to communicate vie email.

        My address is: pete.fenn@live.com

        I look forward to hearing from you,

        Cheers, Pete

Resources