Hello Lovely peoples!
I have found my self in the wonderful world of VBA and learning everyday that i use it.
However, i have found i have hit a brick wall.
I'm trying to create a Commissions spreadsheet for the sales staff and i want them to be able to export their data from the system that we use via .CSV, then copy and paste it to this spreadsheet.
Once in the spreadsheet, i want them to be able to press a button and it sorts the raw data in to 2 separate sheets that are hidden which the main sheet will take a Vlookup feed from.
I can do all of the above with the exception ofsorting the raw data and copying the data from one sheet to another based on a partial text match.
So in summary anything on the raw data sheet that contains "NT" i want to be copied to one worksheet, then anything containing "UT" to another.
These stock numbers are comprised of two letters NT or UT and then four numbers, for example. NT1111.
Here is a code that had Written worked for me before with regards to searching for a specific word and copying anything over that had that word. but it wont work with wildcards for a partial match.
Sub CopyNT()
Dim c As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet
' Change worksheet designations as needed
Set Source = ActiveWorkbook.Worksheets("Data")
Set Target = ActiveWorkbook.Worksheets("NT Data")
j = 2 ' Start copying to row 2 in target sheet
For Each c In Source.Range("A1:A200") ' Do 200 rows
If c = "*NT*" Then
Source.Rows(c.Row).Copy Target.Rows(j)
j = j + 1
End If
Next c
End Sub
Many thanks in advance!
Aaron
I have found my self in the wonderful world of VBA and learning everyday that i use it.
However, i have found i have hit a brick wall.
I'm trying to create a Commissions spreadsheet for the sales staff and i want them to be able to export their data from the system that we use via .CSV, then copy and paste it to this spreadsheet.
Once in the spreadsheet, i want them to be able to press a button and it sorts the raw data in to 2 separate sheets that are hidden which the main sheet will take a Vlookup feed from.
I can do all of the above with the exception ofsorting the raw data and copying the data from one sheet to another based on a partial text match.
So in summary anything on the raw data sheet that contains "NT" i want to be copied to one worksheet, then anything containing "UT" to another.
These stock numbers are comprised of two letters NT or UT and then four numbers, for example. NT1111.
Here is a code that had Written worked for me before with regards to searching for a specific word and copying anything over that had that word. but it wont work with wildcards for a partial match.
Sub CopyNT()
Dim c As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet
' Change worksheet designations as needed
Set Source = ActiveWorkbook.Worksheets("Data")
Set Target = ActiveWorkbook.Worksheets("NT Data")
j = 2 ' Start copying to row 2 in target sheet
For Each c In Source.Range("A1:A200") ' Do 200 rows
If c = "*NT*" Then
Source.Rows(c.Row).Copy Target.Rows(j)
j = j + 1
End If
Next c
End Sub
Many thanks in advance!
Aaron
