VB question- please help!

[Marie]

This probably sounds confusing on what I need. I will try to explain a little
bit better. The first quarter my company buys accounts receivable and scores
them by risk. There are numerous fields that identify the criteria. To test
to see if we are scoring them correctly we would like to look back and see
why we made the decision to buy it and if it was a good decision. So in my
first sheet, as I said, if I
started with 5000 accounts after 8 quarters I still want to have 5000
accounts, regardless of whether they were paid off. So I can look at them and
see how we scored them. All information from column A-AK will never change,
columns AL-BA would have information, times past due- etc that would change
during the life of the account. (It's a pretty big file - which is one reason
I need the macro)The common field in every quarter (or sheet) would be the
note number, which I have in column B, starting in row 2 - which will be the
same column and row on every sheet . I want it to find this number and if
it is still on the newest sheet (or newest quarter) copy the data (from
AL-BA, or if there is a better way to see if something has changed??)and
carry the new information to the first sheet if not do nothing. So every
quarter I can download the newest quarter, run the macro and it would update
the account. Like I said something I am having trouble with is I have to make
sure if the original month started with 5000 accounts at the end of the 8th
quarter I still have to have 5000
accounts. Make sense??
Is this possible?

 

[Tom Ogilvy]

Sub copydata()
Dim rng1 as Range, rng2 as Range
Dim cell as Range
with Worbooks("Book1.xls").Worksheets(1)
set rng1 = .Range(.Cells(2,"B"),.Cells(rows.count,2).End(xlup))
End With
With Workbooks("Book2.xls").Worksheets(1)
set rng2 = .Range(.Cells(2,"B"),.Cells(rows.count,2).End(xlup))
End With
for each cell in rng1
res = application.Match(cell.Value,rng2,0)
if not iserror(res) then
rng2.offset(0,36).Resize(1,16).copy _
Destination:=cell.offset(0,36)
end if
Next
End Sub

would be a start. Change Book1 and Book2 to match your
requirements/situation. The data is copied from Book2 to Book1 in the code.

 

[Marie]

Tom,
I guess I didn't explain that there are multiple note numbers in column B.
The first number starts in column B row 2. There are 5000 different numbers
that I need the code to look for on every sheet and then match. The code
worked but it copied everything it found in column B row 2 to every account.
I guess I didn't explain it well enough. Do I need to name a range for the
column the note numbers are in? I have tried changing your code to pick up
the entire column of numbers with no luck. Could you please help, again?
Thanks
Marie

 

[Tom Ogilvy]

I think you need to spend some time understanding what the code does. It
only works on two worksheets as written. It loops through all the entries
in column B of Book1, worksheet1 and then looks in column B of Book2,
worksheet 2. If it finds a matching entry, it copies columns AL-BA of that
row back to columns AL-BA of the row in Book1, worksheet 1.

You can certainly alter it to search through multiple sheets of another
workbooks or even multiple books with single or multiple sheets.

 

[Tom Ogilvy]

Disregard. See discussion in later thread.