Forum Discussion
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...
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
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
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?
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?
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?
- 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