Working with 2 workbooks/Finding/Inserting Data from one to another

[newbie123]

hi,
i'm very new to vba and am completely stuck on how to do something.
basically, one workbook1 contains a worksheet with a column of first names
(Column B), last names (Column C), and cities (Column D). another workbook2 contains a worksheet with a specific city info, age info, gender, first and last names matching specific people from the other workbook... what i want to do is, look through workbook1 for everybody matching the city of workbook2 and from there, compare to see if the people in workbook1 can be found in workbook2. if they can be found, copy the info from workbook2 relating to the person found and paste that info into specific columns in workbook1. it seems like such an easy concept but its not working right at all for me!

here is a sample of my code:


Worksheets("People").Activate
Worksheets("People").Range("D1").Select
Dim count As Integer
Dim lastname As String
Dim firstname As String
Dim startrow As Integer


count = 1

'loop to go through all rows in workbook1 worksheet

Do While Not IsEmpty(ActiveCell)

'check to see if city in column D equals cities in workbook2
'note: previous to this, i have an inputbox in order to find out
'what city the other workbook2 is referring to
If Worksheets("People").Columns("D").Find(city,_
LookIn:=xlValues, lookat:=xlWhole).Row Then

'if city is found in workbook1, copy lastname and first name
lastname = Range("C" & count).Value
firstname = Range("B" & count).Value
'open up workbook2
Workbooks.Open Filename:=FName(n)
Worksheets(1).Activate

'search through all of workbook2 to see if the last name can be found
Do While (Worksheets(1).Columns("A").Find(lastname, _
LookIn:=xlValues, lookat:=xlWhole).Row)

'record the row that lastname is found in, if found

startrow = Worksheets(1).Columns("A").Find(lastname, _
LookIn:=xlValues, lookat:=xlWhole).Row
If Not startrow = 0 Then
'check to see if first name matches row with last name
If (Worksheets(1).Columns("A").Find(firstname, _
LookIn:=xlValues, lookat:=xlWhole).Row) Then

'if last name and first name match, copy info from 'G:J of same row containing last name

Range("G" & startrow & ":J" & startrow).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWorkbook.Close True
Sheets("People").Activate

'Paste the info copied previously into workbook1 that 'relates to the first and last name of the person searched

Range("E" & count).Select
ActiveSheet.Paste
End If
End If
Loop
End If
count = count + 1
ActiveCell.Offset(1, 0).Select
'loop to continue comparing people in both workbooks to
'copy necessary info
Loop



here is some pseudo code i was trying to follow:

InputBox: Enter City = CityInputted

On People.xls:

For all Cities in Column D that equal CityInputted
Search Last name in City.xls
If found, check if first name matches;
if first and last name matches: copy data from columns
G,H,I,J row (whatever last name is in) into People.xls where First and Last
name matches
if not found, look at all same last names until found and if
person is not found at all, go back to People.xls to search up the next
person containing city name

this doesnt seem to work for me at all.... i dont know why.... if anybody
can offer any advice or help that would be really appreciated. thank you
very much in advance.

i have also included 2 workbooks:

if you look at the two workbooks provided, basically what happens is
Workbook1.xls contains the macros to be run. From there, there is a menu
to import data from another workbook (Workbook2.xls). When importing, an
inputbox asks you to specify the city the imported workbook (Workbook2.xls)
belongs to. Then, in Workbook1.xls it searches through it to see if the
inputted city is found. If so, it copies the last name and first name and
stores it in a variable. Then, it goes back to the Workbook2.xls and goes
through each row to see if the last name can be found. if last name can be
found, it checks to see if the first name matches. if it does, it copies
the age,gender, year of birth information and then it pastes this
information into the related last name and first name in Workbook1.xls

sorry this post is so long :S i tried to be as detailed as possible...

[XLGibbs]

Welcome to the board and to VBA. I will have a solution for you in a short time, unless someone beats me to it.

This will be easier than you think....to confirm my understanding.

Workbook1.xls will have all available names. Workbook2.xls (which will change) will contain a list of people specific to a State( or city, you have the label "City" but they are state abbreviations)

If the workbook is for New Jersey for example, search for the names in Workbook 1 from new jersey, see if they are in workbook 2, if soe paste the relevant data from workbook 1 into the next available row of workbook 2

Is this correct?

Will the workbook 2 always be a different state? Will the workbook 2 data always be on Sheet1? (same as workbook 1?)

[newbie123]

"If the workbook is for New Jersey for example, search for the names in Workbook 1 from new jersey, see if they are in workbook 2, if soe paste the relevant data from workbook 1 into the next available row of workbook 2"

your interpretation is very close :) its just the opposite for the pasting: if names in workbook1 are found in workbook2, copy the relevant information relating to that name from work book 2 (eg: age/gender/year of birth) and paste it into the relevant row in workbook 1. so, from my example of spreadsheets attached, if workbook2 is for state "NY" then it will find all people in workbook1 with state "NY" and try to find that same name in workbook2. so for "john graham" who is from NY, we see that his name is in workbook2. so, columns G,H,I of his row (1) in workbook2 will be copied and pasted into E,F,G in workbook1 that corresponds to his name row (2) ... hopefully that makes sense..

and workbook2 (or whatever the file name that is opened is called) will each contain one state per file... so another workbook, workbook3, could be a file containing NJ information and so on... workbook2 will always have data on sheet1 and workbook1 will probably have data on sheet2...

thanks very much!

[XLGibbs]

Okay, got it. I am gonna have to get back to you tomorrow (it is almost 1 AM here)...but still, not a problem.

[newbie123]

great! thanks so much :) i'd been trying to debug what i had written up and it was really getting me frustrated.. i dont think i'm looping right... or searching either :S

[XLGibbs]

Sub GetOtherInfo()
Dim wsLookFrom As Worksheet, wsPasteFrom As Worksheet, rngSource As Range, rngDest As Range
Dim wb1 As Workbook, wb2 As Workbook, c As Range, rngMatch As Range, foundmatch As Boolean

Set wb1 = Workbooks("Workbook1.xls")
Set wb2 = Workbooks("Workbook2.xls")

Set wsLookFrom = wb1.Sheets("Resource")
Set wsPasteFrom = wb2.Sheets("Sheet1")

Set rngDest = Range(wsLookFrom.Cells(2, 3), Cells(wsLookFrom.Rows.Count, 3).End(xlUp)) 'the place where the data gets pasted
Set rngSource = Range(wsPasteFrom.Cells(1, 6), Cells(wsPasteFrom.Rows.Count, 6).End(xlUp)) 'the place where the data is


For Each c In rngDest

Set rngMatch = rngSource.Find(c)
If Not rngMatch Is Nothing Then
rngFirst = rngMatch
Do
If c.Offset(, -1) = rngMatch.Offset(, -1) Then
rngMatch.Offset(, 1).Resize(1, 3).Copy
c.Offset(0, 2).PasteSpecial (xlPasteValues)
Else: Set rngMatch = rngMatch.FindNext
End If
Loop Until rngMatch = rngFirst

Else:
End If

Next c

End Sub


That seems to work provided the data is laid out exactly as you presented in the sample. Workbooks attached with module. let me in case it needs more work.

Gibbs

PS. Assumption is at least one match will be found in each states worksheet..no error handling included...

[newbie123]

wow! thank you so much! your code is like half the length of mine!! i still definately have a lot of VBA functions to learn..... i wont be able to try it out until monday or tuesday to see how it works but i will keep you posted! thank you so much for your time! how long did that take you?? including looking things up and *trying* to get mine working, it took around 2 work days or so... :S

[XLGibbs]

No worries. Your code was using the same functions, and with more time you will understand some of what happened. My code simply Set object references in memory so it was easier to refer to and manipulate them later.

Basically, instead of looping through each last and first name....I figured if the last name WAS found, the first name would be "right there" next to it...so that is where the first check came in...

You had the right idea. If you use the VBE editor and the F8 key to step through it...watch the locals window and you can kind of see how the variables get populated.

I know people who could take my code and trim into what looks like another language and be even shorter...so don't feel bad. You were VERY close to getting your method to work..

Rather than identifying each aspect of location of each match you can find a matching cell (Set statement for the object, followed by the Range to look.FIND(what)) and refer to its contents, location, or positions around it quite easily. That way you don;' have to find something, assign its row, then assign a new loop to look in that row for the other part of it...

Let me know how it works out, if I have change I will post a commented version of the code so you can understand where/what/how probably later on...

[newbie123]

Hi Gibbs!

Thanks again for the reply!

I tried to run it and got an error on line:


Set rngSource = Range(wsPasteFrom.Cells(1, 6), Cells(wsPasteFrom.Rows.Count, 6).End(xlUp)) 'the place where the data is


It says: Run-time error '1004': Method 'Range' of object '_Global' failed

I'm just a little confused from the coding as to where it identifies which state the other file possesses. After opening Workbook2.xls, I have an inputbox that asks the user to input the state of the file:

Dim state as String
state = InputBox("Please enter the state:")

Is the state stored in c in your code? Is that how it identifies which rows it knows to look in? As Workbook1.xls could have people from multiple states in it, I would have a separate workbook for each of these states and have the user open them one by one until all required people info in workbook1.xls has been filled out.. I probably need to familiarise myself with the code a lot mroe to understand what exactly is going on...