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