Solved

Copy from one sheet to another

Posted on 2013-06-25
17
325 Views
Last Modified: 2013-06-28
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
Comment
Question by:leezac
  • 9
  • 3
  • 2
  • +2
17 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 39277974
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
 

Author Comment

by:leezac
ID: 39278033
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39278052
What is wrong with the suggested code?

mlmcc
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39278055
That is what my code will do
0
 

Author Comment

by:leezac
ID: 39278083
Ok - thanks.   I will retry.
0
 
LVL 13

Expert Comment

by:Stacy Brown
ID: 39278959
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
 

Author Comment

by:leezac
ID: 39279620
finalword

Sheet 1 column 2 row 8 needs to also match Sheet 2 column 2 row 2.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39279664
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:leezac
ID: 39279665
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
 

Author Comment

by:leezac
ID: 39279702
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
 

Author Comment

by:leezac
ID: 39279730
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
 

Author Comment

by:leezac
ID: 39279828
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
 
LVL 14

Expert Comment

by:Faustulus
ID: 39280183
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
 

Author Comment

by:leezac
ID: 39281470
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
 
LVL 14

Accepted Solution

by:
Faustulus earned 500 total points
ID: 39283496
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
 

Author Closing Comment

by:leezac
ID: 39284353
Thank you - that is what I needed
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39284860
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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now