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
Worksheets(“Sheet2”).Activate
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
Worksheets(“Sheet3”).Activate
‘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
Worksheets(“Sheet4”).Activate
‘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
Worksheets(“Sheet1”).Activate
x = x + 1
Loop
Application.CutCopyMode = False

End Sub

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

  1. Selim

    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.

    thanks

    Reply
  2. Diana

    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?

    Reply

Leave a Reply

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