Copying from one worksheet to another

[azerama99]

Hi all,

I've done a lot of research on vba and using other peoples codes to adapt my own for my own uses. but tit seems the more niche the result you want, the less information you can find to help you.

i have created a salesman's sheet that copy's data from a raw data input, into separate sheets organised by site.
Within these sites there are a 3 sales people with their own specific ranges.

for instance below, starting on row 64 the formula will copy anything from E2:200 on the raw data input sheet containing the name Alex Philips, and will paste them in to the target sheet which is Solent new data.

each sales man will sell a selection of new and used bikes, these bikes have stock numbers start with 2 letters followed by 4 numbers. so for example NT3000 or UT3000 these stand for NT - New Triumph or UT - Used Triumph.

what id like to do is utilise the formula below to contain an IF AND statement which looks for the name of the sale person in Column E and then also in Column A Searches for the stock number (anything containing either NT). if the criteria is met then it will copy the record over as it did before, but only the new stock numbers

when i have tried to use wildcards before I'm certain i was doing it wrong.

any help would be greatly appreciated.

'DTS
Sub CopyAlexPhillips()
    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("Raw Data")
    Set Target = ActiveWorkbook.Worksheets("Solent New Data")

    j = 64     ' Start copying to row 64 in target sheet
    For Each c In Source.Range("E2:E200")   ' Do 200 rows
        If c = "Alex Philips" Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           j = j + 1
        End If
    Next c
End Sub

 

[sijpie]

Hi Azerama,

first: when posting code, put it between code brackets: at the top of the post window you see some icons, one of which is a little cloud with VBA underneath it. When you click it it writes the tags in your post. Just paste the code at the point of the cursor.

try to understand what your code is doing in the loop.

For Each c In Source.Range("E2:E200")

What this does is: it sets c to each cell in turn. Now since c is a range, you can use its Range properties. One of the properties of a Range is .Offset(R, C). This refers to a cell R rows below and C columns to the right of the current range (c).
You want to find the value of the cell in the same row as c and 4 cells to the left of c. So the Offset reference would be .Offset(0, -4)

Then you want to check if the data in that cell starts with "NT". Use the Left() function for this

Sub CopyAlexPhillips()
    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("Raw Data")
    Set Target = ActiveWorkbook.Worksheets("Solent New Data")
    
    j = 64 ' Start copying to row 64 in target sheet
    For Each c In Source.Range("E2:E200") ' Do 200 rows
        If c = "Alex Philips" And Left(c.Offset(0, -4), 2) = "NT" Then
            Source.Rows(c.Row).Copy Target.Rows(j)
            j = j + 1
        End If
    Next c
End Sub

This code will work fine. But it has a number of efficiency and user-friendlyness problems: You will need to tailor it for every name and every output sheet, and it is not very fast if the number of rows to be checked start to increase. Writing to a sheet one cell at a time is slow.

Furthermore there are some small coding things that could lead to bugs. Read the comment:

Sub CopyAlexPhillips_debugged()
' give variables a startingletter which hints at the type: _
  r for Range, l for Long, i for Integer, ws for Worksheet
' Also when using counters for rows or columns use Long type, _
  as Integer may become too small. (It is unlikely you will _
  have more than 32000 rows, but better safe than sorry)
' I always use at least one capital letter in a variable name _
  in the Dim statement, but type them with lower case. If the _
  VBeditor does not correct it to the right capitalisation, _
  then I have made a typo! Bug caught
  
    Dim rC As Range
    Dim lR As Long
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    
    ' Change worksheet designations as needed
    Set wsSource = ActiveWorkbook.Worksheets("Raw Data")
    Set wsTarget = ActiveWorkbook.Worksheets("Solent New Data")
    
    lR = 64 ' Start copying to row 64 in target sheet
    For Each rC In Source.Range("E2:E200") ' Do 200 rows
        If rC = "Alex Philips" And Left(rC.Offset(0, -4), 2) = "NT" Then
            Source.Rows(rC.Row).Copy Target.Rows(lR)
            lR = lR + 1
        End If
    Next rC
End Sub

Then about user friendliness:
It will help if you have a table with all the salesmen names and next to the name the relevant sheet names

You can then loop through this table as well to set the name to search for and the name of the sheet to write to.

As a last thing, the code can be made to run lightning fast by using arrays to read from and write to the sheet. But first I want to hear from you about automating the name lookup.

 

[TotallyConfused]

Hello Sijpie

I like the idea you talk about in the comments about using a capital letter in the DIM statement but then later typing the variable in lower case letters. I'd never thought of doing that, though (by accident) there have been times I've had the editor make a change. Great idea! Thanks for the tip. Thanks to you, I've learned something new today.

I notice the original poster hasn't been here for several days. After all the work you've done explaining things, it will be very frustrating for you if that person never shows up again, because you've brought up some good points. Hope that doesn't happen.

TotallyConfused

 

[sijpie]

You're welcome TotallyConfused. And my work hasn't been for nothing: you picked something up from it! I usually reply to somewhat older questions and so they regularly do not get responded to. No skin off my nose. But often the people are very glad somebody picked up on their problem.