May 16 2021 01:20 AM
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
May 16 2021 01:52 AM
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
May 17 2021 12:11 AM
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
May 17 2021 01:31 AM
SolutionYou 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
May 20 2021 01:37 AM
May 20 2021 02:08 AM
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?
May 21 2021 11:33 AM
May 17 2021 01:31 AM
SolutionYou 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