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

azerama99

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

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,460
Office Version
365, 2010
Platform
Windows, Mobile
As your stock numbers start with the letters you should be able to just do the below

Code:
If Left(c, 2) = "NT" Then
 

azerama99

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

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,460
Office Version
365, 2010
Platform
Windows, Mobile
Thanks for the feedback and welcome to the forum.
 

Forum statistics

Threads
1,081,575
Messages
5,359,711
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top