How to transfer data from one sheet to another using wild card characters

How to transfer data from one sheet to another using wild card characters in VBA. Many times we wish to create neat reports from our raw data. We might have entered different types of data and we now wish to create reports about one item separately in a new worksheet. We can do this using a looping process and extracting specific data from our raw data using wild card characters like ‘*’. Watch the training video below:

You can view this video also on YouTube.

Here’s the complete VBA code to use wild card characters to transfer data from one work to multiple worksheets automatically to create reports:

Sub extractdata()
Dim x As Long
x = 2

Do While Cells(x, 1) <> “”
If Cells(x, 1) Like “BU*” Then
Worksheets(“Sheet1”).Range(Cells(x, 1), Cells(x, 2)).Copy
erow2 = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets(“Sheet2”).Range(Cells(erow2, 1), Cells(erow2, 2))

ElseIf Cells(x, 1) Like “TM*” Then
‘copy data if it starts with TM
Worksheets(“Sheet1”).Range(Cells(x, 1), Cells(x, 2)).Copy

‘Go to sheet2. Activate it. We want the data here
‘Find the first empty row in sheet2
erow3 = Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
‘Paste the data here
ActiveSheet.Paste Destination:=Worksheets(“Sheet3”).Range(Cells(erow3, 1), Cells(erow3, 2))

ElseIf Cells(x, 1) Like “YT*” Then
‘copy data if it starts with YT
Worksheets(“Sheet1”).Range(Cells(x, 1), Cells(x, 2)).Copy

‘Go to sheet2. Activate it. We want the data here
‘Find the first empty row in sheet2
erow4 = Sheet4.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
‘Paste the data here
ActiveSheet.Paste Destination:=Worksheets(“Sheet4”).Range(Cells(erow4, 1), Cells(erow4, 2))

End If
x = x + 1
Application.CutCopyMode = False

End Sub

Published by

Dinesh Kumar Takyar

Welcome to! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: For a structured Excel VBA training course online you can visit:

7 thoughts on “How to transfer data from one sheet to another using wild card characters”

  1. I used the exact given data but it is given an error. in my case, i have employees working in a team of 2
    their pay is different, everyone takes a percentage on completed work. If job site is 50km outside of our base, there is a different pay rate for each and the same employee. So i have a master sheet that i input the address and the work completed and price in several columns and then i choose the employee name from the drop down box. When employee name chosen, i need the cells on that line to be transferred to the employee’s personal page. in which the formula defined will calculate the pay of that work and give me a total.
    i was very excited to find a very easy way to eliminate my 4-5 hr sunday accounting time but this did not work. if you could respond, i would like to send you what i am trying to create and it will give you a better understand on what i need. i know you are super busy with a lot of emails but id appreciate the help if you can spare it for me.


  2. hye.. how to do if i want to filter data based on a row value for multiple worksheet. and then transfer them into a new worksheet using looping?

      1. Hi,

        Can you please help me on extracting each letter on a name or word to transfer to another sheet as a from of template each letter has to go to a specific row & column

        Sample name:
        A B
        1 I PauL Brando I I >>>> this is from sheet 1

        extract to sheet 2 would be like this:

        A B C D E F G H
        1 I P I a I u I L I I B I r I a and so on..

  3. hi,

    I did this and I keep getting a “Next without for” error. Also I would want it to run this one and then run the exact same thing but look up another criteria of data and paste in that worksheet. So basically same thing but change the Dakota landing to another criteria in that persons sheet and paste to corresponding worksheet. I will need to do this for 5 different instances and just run all at once.

    Private Sub CommandButton1_Click()
    LastRow = ActiveSheet.Range(“A” & Rows.Count).End(xlUp).Row

    For i = 11 To LastRow
    If Cells(i, 11) = “1 / 8115″ Then

    Range(Cells(i, 1), Cells(i, 48)).Select

    Workbooks.Open Filename:=”C:\Users\lpryor\Documents\archive\WaterMaster.xlsx”

    Dim p As Integer, q As Integer

    p = Worksheets.Count
    For q = 1 To p
    If ActiveWorkbook.Worksheets(q).Name = “8115” Then
    End If

    Next q

    erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

    ActiveSheet.Cells(erow, 1).Select
    Application.CutCopyMode = False
    End If
    Next i
    Next q
    End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.