Kaniguan1969
03-29-2014, 05:45 AM
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.
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.