SOLVED

Macro functions

%3CLINGO-SUB%20id%3D%22lingo-sub-2358591%22%20slang%3D%22en-US%22%3EMacro%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2358591%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20I%E2%80%99m%20new%20to%20macros%20and%20I%20want%20to%20be%20able%20to%20move%20a%20range%20of%20cells%20to%20another%20range%20of%20cells%20if%20a%20preceding%20cell%20in%20that%20range%20equals%20(say)%20YES.%20So%2C%20How%20do%20I%20test%20if%20a%20cell%20(A1%20say%20in%20Sheet%201)%20equals%20YES%20and%20if%20it%20does%2C%20move%20cells%20A2%3AA8%20to%20Sheet%202%2C%20cells%20A1%3AA7%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2358591%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2358633%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2358633%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1054938%22%20target%3D%22_blank%22%3E%40Fennster%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20this%20macro%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20MoveCells()%0A%20%20%20%20If%20Worksheets(%22Sheet1%22).Range(%22A1%22).Value%20%3D%20%22YES%22%20Then%0A%20%20%20%20%20%20%20%20Worksheets(%22Sheet1%22).Range(%22A2%3AA8%22).Cut%20Destination%3A%3DWorksheets(%22Sheet2%22).Range(%22A1%22)%0A%20%20%20%20%20%20%20%20Application.CutCopyMode%20%3D%20False%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2359839%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2359839%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help.%20I%20am%20a%20macro%20and%20VB%20beginner%20(I%20used%20to%20program%20in%20COBOL)%3C%2FP%3E%3CP%3EI%20have%20got%20the%20script%20running%20but%20only%20for%20row%201.%20I%20have%20many%20rows%20with%20a%20YES%20that%20I%20want%20to%20copy%20over%2C%20how%20do%20I%20'loop'%20the%20script%20to%20do%20row%202%3A100%20say.%3C%2FP%3E%3CP%3EI%20got%20this%20far%3A%3C%2FP%3E%3CP%3ESub%20MoveCells()%3CBR%20%2F%3EIf%20Worksheets(%22PICK%22).Range(%22A1%22).Value%20%3D%20%22YES%22%20Then%3CBR%20%2F%3EWorksheets(%22PICK%22).Range(%22C1%3AE1%22).Copy%20Destination%3A%3DWorksheets(%22PRINT%22).Range(%22A1%3AE1%22)%3CBR%20%2F%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3EI%20did%20try%26nbsp%3B%20-%20If%20Worksheets(%22PICK%22).Range(%22A1%3AA100%22).Value%20%3D%20%22YES%22%20Then%3C%2FP%3E%3CP%3Ebut%20I%20get%20an%20error%3C%2FP%3E%3CP%3ECheers%2C%20Pete%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2360054%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2360054%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1054938%22%20target%3D%22_blank%22%3E%40Fennster%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20have%20to%20loop%20through%20the%20cells%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20MoveCells()%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20For%20r%20%3D%202%20To%20100%0A%20%20%20%20%20%20%20%20If%20Worksheets(%22PICK%22).Range(%22A%22%20%26amp%3B%20r).Value%20%3D%20%22YES%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Worksheets(%22PICK%22).Range(%22C%22%20%26amp%3B%20r).Resize(1%2C%203).Cut%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Destination%3A%3DWorksheets(%22PRINT%22).Range(%22A%22%20%26amp%3B%20r)%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20r%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2371185%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2371185%22%20slang%3D%22en-US%22%3EHi%20Hans%2C%3CBR%20%2F%3EAfter%20a%20lot%20of%20fiddling%2C%20I%20finally%20got%20it%20to%20work%2C%20fantastic%20and%20many%20thanks.%3CBR%20%2F%3EI%20Didn't%20realise%20that%20the%20positioning%20of%20the%20syntax%20is%20as%20important%20as%20the%20function.%3CBR%20%2F%3EWhat%20I%20would%20like%20to%20do%20now%20is%20be%20able%20to%20delete%20or%20mark%2C%20from%20my%20original%20'list%20of%20data'%20those%20items%20(rows)%20that%20have%20been%20selected%20with%20a%20YES.%3CBR%20%2F%3EBasically%20within%20the%20workbook%20I%20have%203%20sheets.%3CBR%20%2F%3EMAIN%20-%20Contains%20the%20full%20list%20of%20data.%3CBR%20%2F%3EPICK%20-%20That%20I%20copy%20all%20data%20from%20MAIN%20into%20but%20then%20set%20up%20my%20Y%20selection.%3CBR%20%2F%3EThe%20user%20puts%20a%20YES%20against%20the%20required%20items%20for%20that%20day.%3CBR%20%2F%3EThen%20I%20run%20your%20super%20SUB%20that%20copies%20the%20YES%20items%20into%20a%20third%20PRINT%20Sheet%3CBR%20%2F%3EThereby%2C%20creating%20my%20Print%20List%20for%20the%20day.%3CBR%20%2F%3EYour%20next%20challenge%20is%3A%3CBR%20%2F%3EWhat%20I%20would%20like%20to%20do%20is%20'mark'%20on%20the%20original%20list%20MAIN%20that%20the%20row%20has%20previously%20been%3CBR%20%2F%3Eselected%20within%20the%20PICK%20sheet.%20I%20think%20I%20would%20need%20to%20give%20each%20row%20in%20the%20MAIN%20sheet%20a%20Seq%20No%20so%20that%20the%20lines%20in%20the%20PICK%20Sheet%20can%20refer%20back%20to%20the%20rows%20in%20the%20MAIN%20Sheet%20and%20perhaps%20'shade'%20the%20row%20or%20mark%20the%20row%20somehow%2C%20to%20show%20that%20it%20has%20previously%20been%20selected%3F%3F%3CBR%20%2F%3EWould%20that%20be%20possible%2C%3CBR%20%2F%3EFor%20now%2C%20I%20will%20get%20the%20user%20to%20shade%20the%20rows%20in%20the%20MAIN%20sheet%2C%20using%20the%20printed%20list%20as%20a%20guide.%3CBR%20%2F%3EThanks%20for%20all%20your%20help%20so%20far%2C%20Cheers%2C%20Pete%3CBR%20%2F%3EPS%3A%20My%20brother%20lives%20in%20Holland%2C%20a%20place%20called%20Drunen%3CBR%20%2F%3EPPS%3A%20Am%20I%20allowed%20to%20give%20you%20my%20email%20address%3F%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor

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

6 Replies

@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

@Hans Vogelaar 

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

best response confirmed by allyreckerman (Microsoft)
Solution

@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
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?

@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?

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