Help With VBA Code (my first post, please be gentle :])

azerama99

New Member
Joined
Nov 13, 2019
Messages
5
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
As your stock numbers start with the letters you should be able to just do the below

Code:
If Left(c, 2) = "NT" Then
 
Upvote 0
Thank you so much Mark!!

That has done the trick Perfectly :)

i think rather than pondering and pulling my hair out trawling google, im going to pop a post on here from now on :)

if i could buy you a beer i would!! (y)

Have a wonderful day.

Aaron
 
Upvote 0

Forum statistics

Threads
1,212,936
Messages
6,110,764
Members
448,297
Latest member
cocolasticot50

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top