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

6 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
  3. Lenzie

    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
    Selection.Copy

    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
    Worksheets(“8115”).Select
    End If

    Next q

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

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

    Reply

Leave a Reply

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