Update workbook data from another workbook

[Kaniguan1969]

Hi Expert,

I have two workbook that contains userform. workbook(user1) use for data entry transaction by user. in my sample
the user enter ticketnumber, requestor and department, in the original dataentry it has 10 columns. After the user enter those items
the workbook(admin) will consolidate/capture those items entered by the user and save/copy to admin workbook. SO, the admin
will update those items and filled up the columns datefinish, status, note, and update.

After the admin filled up those items i need again to update/overwrite the user1 workbook with the items entered by admin using ticket number
as the reference. my COncern is how I will captured those items/records updated by admins and copied/overwrite to user1 workbook? any idea guys. THanks.

Sample data and results

Workbook(user1)
sheet(transactionDB)

Ticket------Requestor---Department--DATEFINISH--STATUS----NOTE---UPDATE
L11403-002--xxxx--------Line1
L11403-002--xxxx--------Line1
L11403-002--xxxx--------Line1


workbook(admin)
sheet(transactionDB)

Ticket------Requestor---Department-----DATEFINISH--STATUS----NOTE---UPDATE
L11403-002--xxxx--------Line1----------3/28/2014---On-going--test--Pending
L11403-002--xxxx--------Line1----------3/28/2014---On-going--test--Pending
L11403-002--xxxx--------Line1----------3/28/2014---On-going--test--Pending


Actual result for user1 records
--------------------------------------------------------------------------
Ticket------Requestor---Department-----DATEFINISH--STATUS----NOTE---UPDATE
L11403-002--xxxx--------Line1----------3/28/2014---On-going--test--Pending
L11403-002--xxxx--------Line1----------3/28/2014---On-going--test--Pending
L11403-002--xxxx--------Line1----------3/28/2014---On-going--test--Pending

So far this is my codes.


Dim wb As Workbook, wbTarfile As Workbook
Dim ws As Worksheet, wsTarfile As Worksheet
Dim SearchRange As Range
Dim FindWhat As Variant
Dim FoundCells As Range
Dim FoundCell As Range

With Application
.EnableEvents = False
.DisplayAlerts = False
.AskToUpdateLinks = False
End With

Tarfile = "C:\admin.xlsm"
Set wbTarfile = Workbooks.Open(Tarfile)
Application.EnableEvents = True

If wbTarfile.ReadOnly Then
ActiveWorkbook.Close
MsgBox "Cannot process data consolidation, someone currently using the file. Please try again later."
Exit Sub
End If

DoEvents

Set wb = ThisWorkbook
Set ws = ThisWorkbook.Sheets("TransactionDB")
Set wsTarfile = wbTarfile.Sheets("TransactionDB")

If Me.TextBox1.Value <> "" Then
FindWhat = TextBox1.Value ' This is ticket number with textbox value in userform
'Search range
Set SearchRange = wsTarfile.Range(wsTarfile.Cells(1, 1), wsTarfile.Cells(Rows.Count, 1).End(xlUp))
'Search
Set FoundCells = FindAll(SearchRange:=SearchRange, _
FindWhat:=FindWhat, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
MatchCase:=False, _
BeginsWith:=vbNullString, _
EndsWith:=vbNullString, _
BeginEndCompare:=vbTextCompare)
If FoundCells Is Nothing Then
'Debug.Print "Value Not Found"

Else
For Each FoundCell In FoundCells

'copy/overwrite the records of user1(workbook) that came from admin(workbook)

Next FoundCell
End If
MsgBox "Records Updated!", vbInformation
End If


btw, i have an existing thread in excel forum, so far i didnt received any response. I tried to post a new tread in vba express, i'm hoping someone will give me the solution. thanks.

[SamT]

Thank you for mentioning that you have a thread in another forum. Can you please copy the address of that thread and paste it in your next post here.
SamT: Moderator

I have some questions and suggestions about the code you have shown.

FindAll is not a VBA for Excel function. There are several FindAll addins and UserDefinedFunctions available. Is that what you are using? Whose FindAll are you using?

You have used some VBA KeyWords as variables. (FindWhat, SearchRange) This is very not recommended.

Are you using a VBA Form (MsForms.UserForm) or a Worksheet Form (Controls on the Worksheet and Cells to record inputs)?

What is the name of the sub this code is in? Is this all the code in the Sub?

What workbook is this form/code in?

Take a look at the "tables" your original post. We can't tell what values go in which column. If you use the "go Advanced" button at the bottom of the post editor, an advanced Editor will show, and it can insert tables for you to fill in with values. The Advanced Editor will also let you paste small ranges from Excel into the post. you can use the Table editing buttons to show the gridlines (table borders.)

[Kaniguan1969]

Hi SamT.

Thank you very much. Here is the link from the other thread. http://www.excelforum.com/excel-programming-vba-macros/1000258-update-copy-from-workbook-to-another-workbook.html

The FindAll,,this is came from cpearson.

I'm new in excel VBA. hoping for your understanding.

I'm using a userform from a worksheet. This is use by the user to enter records.

The name of the sub is private sub updaterecords. Yes this is code in the sub.

under workbook(user1). have to capture the updated records in workbook(admin) then overwrite to workbook(user1)

Workbook(user1)
sheet(transactionDB)
--This is the format the user save the records entered in a userform. all in all it contains 15 columns
-- the ten columns is filled up by user thru userform. my sample is only 7 columns.



TicketNumber
Requestor
Department
DateFinished
Status
Note
Update


L11403-002
xxxx
Line1






L11403-002
xxxx
Line1






L11403-002
xxxx
Line1








workbook(admin)
sheet(transactionDB)
--this is the actual data updated by admin. need to capture this updated records and replicate/overwrite the records in user1(workbook)
--the reference is the ticket and department(Line1).


Ticket
requestor
department
sdatefinished
status
note
update


L11403-002
xxxx
Line1
3/28/2014
on-going
test
pending


L11403-002
xxxx
Line1
3/28/2014
on-going
test
pending


L11403-002
xxxx
Line1
3/28/2014
on-going
test
pending




L11403-003
xxxx
Line1
3/30/2014
cancelled

test
cancelled



L11403-001
mmm
Line2
3/31/2014
finished
approved
OK