Copying from one worksheet to another

azerama99

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

VBA Code:
'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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
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.
VBA Code:
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
VBA Code:
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:
VBA Code:
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

Board Regular
Joined
May 4, 2017
Messages
246
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,319
Messages
5,595,465
Members
413,992
Latest member
CSEGUIN1973

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
Top