• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

Copy from one sheet to another

I am needing help to copy items from column Column S row 8 Sheet1 to
Column S row 2 of Sheet 2 where
Sheet 1 column 2 row 8 matches Sheet 2 column 2 row 2.


Thanks in advance.
_________________________________________________________________
0
leezac
Asked:
leezac
  • 9
  • 3
  • 2
  • +2
1 Solution
 
aikimarkCommented:
This is confusing.
1. you want to copy "items" (plural), but the description is for a single cell's data
2. you sometimes use the letter column indicator and sometimes use the column number.

============
Maybe this is what you need
If  Sheet1.Cells(8,2).value = Sheet2.Cells(2,2).value Then
    Sheet2.Range("S2").value = Sheet1.Range("S8").value
End If

Open in new window

0
 
leezacAuthor Commented:
No, I need it to only match the values
Sheet 1 column 2 row 8 matches Sheet 2 column 2 row 2.

then copy items from column Column S row 8 Sheet1 to
Column S row 2 of Sheet 2
0
 
mlmccCommented:
What is wrong with the suggested code?

mlmcc
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
aikimarkCommented:
That is what my code will do
0
 
leezacAuthor Commented:
Ok - thanks.   I will retry.
0
 
Stacy BrownSenior Applications AdministratorCommented:
Do you need to use code?  I would use a cell reference in the cells you wish to contain the same information.  So if you want the information on Sheet 2, S2 to contain the information from Sheet 1, S8 try this:

Go to Sheet 2
Click in cell S2
Type an equals sign (=)
Using your mouse, click on Sheet 1
Click in Cell S8
Hit ENTER on your Keyboard

Or you could just type the whole thing like this:
=Sheet1!S8
0
 
leezacAuthor Commented:
finalword

Sheet 1 column 2 row 8 needs to also match Sheet 2 column 2 row 2.
0
 
mlmccCommented:
What do you want it to be if they are not equal?
Use this in Sheet 2 Row 2 Column S

=IF(Sheet1!B8=Sheet2!B2,Sheet1!S2,"")

mlmcc
0
 
leezacAuthor Commented:
Aikimark - I guess I was not clear.  Your code does work if the one value matches in the rows specified, but I need for it to do a search for the matching number
Sheet 1 column 2 row 8 needs to also match Sheet 2 column 2 row 2.
because it can be in any row to 1st empty cell.

I may have to repost.
0
 
leezacAuthor Commented:
Something like this

Application.ScreenUpdating = False
Dim lngRow As Long, sh1 As String, sh2 As String,
Sheets("Validation").Activate


lngRow = Sheets(sh1).UsedRange.Rows.Count

    For i = lngRow To 2 Step -1
        If sh2.Cells(8, 2).Value = sh1.Cells(2, 2).Value Then
sh1.Range("L2").Value = sh2.Range("S8").Value

End If
Next
0
 
leezacAuthor Commented:
mlmcc - I think yours will work but I think we need to use Match in the formula.

=IF(Sheet1!B8=Sheet2!B2,Sheet1!S2,"")

The numbers matching may be in different rows on different sheets and the value for S2 needs to be added to the corresponding row.


Something like this
=if(iserror(IF(INDEX(Sheet4!AP:AP,MATCH(A1,Sheet4!C:C,0)
0
 
leezacAuthor Commented:
This post may be getting confusing so let me explain again

I need vba or formula to

find the matching number from
Sheet 1 column 2 row 8 to end of used row = Sheet 2 column 2 row 2 to end of used row

Then once the match is found copy

value from column Column S for the matching items from Sheet1 to
Column S row that matches of Sheet 2 where
0
 
FaustulusCommented:
leezac,
This thread suffers from severe language problem. Could you post a sample workbook?
1. After 8 posts I finally understood that the number Sheet1!B8 must be looked for in all of Sheet2!Row 2
2.
Then once the match is found copy value from column Column S for the matching items from Sheet1 to Column S row that matches of Sheet 2 where
3. Apparently, the result is a column number [Sheet2!.Cells(2, x)], but then you say "value", so this is the point where understanding ends.
4. The long and the short of it is that I have no idea of what you want done after that match is found. On a worksheet you could highlight ranges and, even more opportune, show how the matching should be done on a real example.
Any one of the participating experts can offer you a solution to this problem very quickly. The reason you don't have one yet is that we don't understand your language. Posting a workbook would overcome that.
0
 
leezacAuthor Commented:
The attached file has two tabs - Validation and Employee
I need to search for matching employee id and copy the corresponding badge from the Validation tab to the Employee tab to end or used row on Validation tab.

Formula or VBA ok.

Thanks
sample.zip
0
 
FaustulusCommented:
Please enter this formula in Employee!L2 and copy down for as far as required:-
=VLOOKUP(A2,Validation!$B$2:$S$64000,18,FALSE)

Open in new window

64000 is the maximum number of rows to be searched in sheet Validation. You might change this into a smaller number.
Column S is the 18th column counting from column B. B and S are defined in the lookup range.
The formula will return #N/A (not available) if the employee number wasn't found. That can be changed according to your wish if required. Let's first find out if this is what you had in mind.
0
 
leezacAuthor Commented:
Thank you - that is what I needed
0
 
FaustulusCommented:
Thank you for the points! I'm sure glad we got it sorted finally.
Let me know if you need any fine tuning of this formula. I'll be glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now