Macro to copy columns if values begins specific text.

[Youngmrr]

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

[#2]

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.

[#3]

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!

[#4]

@Youngmrr 

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.

[#5 · Accepted solution]

@Bennadeau 

Thank you! Sorry I didn't put the correct number of columns. 

It works now!

Appreciated for your help :)