Forum Discussion
Macro to copy columns if values begins specific text.
Hi everyone!
I am new to Macro. I am trying to build a macro that can copy columns which text begins with specific values (e.g. ABCD) from the active worksheet, and paste into another worksheet ('Work').
It should keep their current row orders.
I have attached a screenshot as an example.
There will be more than 2000+ rows..
I have tried myself many times but i didn't make it.
Can you guys help this?
Thanks,
Young
Thank you! Sorry I didn't put the correct number of columns.
It works now!
Appreciated for your help 🙂
4 Replies
- BennadeauIron Contributor
Hi Youngmrr,
This should be a good start
Sub Coppy() Dim lookfor As String Dim i As Integer Dim j As Integer Dim cellval As String lookfor = "ABCD" For j = 1 To 20 For i = 1 To 2000 cellval = Left(Cells(i, j).Value, 4) If cellval = lookfor Then Cells(i, j).Copy Worksheets("Work").Cells(i, j) End If Next i Next j End Sub
You can change the value to look for in row 6. Currently set to "ABCD"
This will look cell by cell in a 20 columns x 200 rows grid for anything that starts with ABCD and will copy it in your sheet named "Work".
Hope this helps.
- YoungmrrCopper Contributor
Hi, Bennadeau
Thank you for your help.
One more question for this to extend moving forward.
The macro copies only two columns.
So, can you please advise how to do if I have 10 of 'ABCD' columns (somewhere within the same row) to be looked and copied?
Many thanks for your help!
- BennadeauIron Contributor
Actually, this macro looks at the first 20 columns.
Look at line 7: "For j = 1 To 20" if you want to expand or reduce, change 20 to whatever you want.
Also, the macro looks at the first 2000 rows.
Look at line 8: "For i = 1 To 2000" if you want to expand or reduce, change 2000 to whatever you want.