Getting data from one workbook to another

[easybpw]

Hi. My last question for the day is a bit tricky. My boss thinks since I had success from all of you with some other issues that I should run this one by all of you.

I have 2 seperate workbooks that have most of the same data except one is for 2003 and the other is 2004. In column D in both workbooks we have names and in column G we have numbers that go with the names. The problem is that in the new workbook (2004) those numbers are not there so I have to find a way to add them. We have lots and lots of names. Is there some piece of code that can look at the 2003 workbook in column D and if that name is also in the 2004 workbook in column D to then take the number in 2003 column G and put it in column G 2004? An example is this:

2003

J.Smith 15

2004

J.Smith

In short if the name is in both workbooks then the code would take the appropriate number in column G 2003 and put it in 2004 column G.

I hope this is clear enough. Any suggestions are greatly appreciated.

Bill

 

[HalfAce]

Hi Bill,
A few things to take into account....

(1) If the 2003 file is already open, close it before running this routine. (You'll be running it from the 2004 file.)

(2) Change the file path, sheet names (and actual workbook names and ranges if necessary) to suit.

Give this a try and see if it's close to what you want.

Sub TestMacroForBillsBoss()
Dim range1 As Range, range2 As Range
Worksheets("Sheet1").Select
Set range1 = Worksheets("Sheet1").Range("D1", Range("D1").End(xlDown))
Application.ScreenUpdating = False
Workbooks.Open Filename:="C:\Documents and Settings\My Documents\2003.xls", UpdateLinks:=True
Worksheets("Sheet1").Select
Set range2 = Worksheets("Sheet1").Range("D1", Range("D1").End(xlDown))
For Each c In range1
  For Each d In range2
   If c.Value = d.Value Then
      d.Offset(0, 3).Copy
      Windows("2004").Activate
      c.Offset(0, 3).Select
      ActiveSheet.Paste
      Windows("2003").Activate
      d.Select
      CutCopyMode = False
   End If
  Next d
Next c
Windows("2003").Close SaveChanges = False
[A1].Select
  CutCopyMode = False
  Application.ScreenUpdating = True
End Sub

This does not take into account anything you want to do with names in one list that are not found in the other. Post back if you want something for those too.
This any help?
Dan

 

[easybpw]

Dan,

Hey thanks for the help. I am trying hard to understand everything. I am having a problem with this part of the code. Can you explain it in as simple English as possible so I can follow along. Here it is:

If c.Value = d.Value Then

This is where the debugger stopped. What is it I need to check out?

Thanks again!

Bill

 

[easybpw]

Wow please disregard my last post. I believe I have this piece of code working correctly. Dan, thanks for the help. It looks like it is doing what I want. Thanks again!

Bill

 

[HalfAce]

You're more than welcome. Glad you got it working.
By way of explaining "If c.Value = d.Value Then" in your reply...
In the For Each statements you're looping through column D of sheet 1 in both workbooks. "c" is a variable assigned to the cell in workbook 2004 that you're currently testing and "d" is the variable assigned to the cell in the workbook 2003 that "c" is being compared to. So to put it plainly, if c is (at this moment) cell D8 of workbook 2004, then d will be cell D8 of workbook 2003. Probably not explaining it very well, but then I only took 12 years of English in school as a kid. ( )
Anyway, good job getting it figured out.
Dan