Forum Discussion

Copper Contributor

Nov 20, 2020

Solved

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 (...

excel

Macros and VBA

Youngmrr
Nov 21, 2020
Bennadeau

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

Appreciated for your help 🙂

Bennadeau

Iron Contributor

Nov 20, 2020

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.

Youngmrr
Copper Contributor
Nov 21, 2020
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!
- Bennadeau
  Iron Contributor
  Nov 21, 2020
  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.
  - Youngmrr
    Copper Contributor
    Nov 21, 2020
    Bennadeau
    
    Thank you! Sorry I didn't put the correct number of columns.
    It works now!
    
    Appreciated for your help 🙂