Peter Groves
asked on
I need to update cell values from one sheet into cells on another sheet based on a reference cell
Hi ,
I need to update cell values from one sheet into cells on another sheet based on a reference cell. Total Excel noob!
I have a sheet that contain an ID number with a bunch of dates that I need to update cells on another sheet based on the ID number.
The ID number is the reference for both sheets!
Private Sub Worksheet_BeforeDoubleClic
Dim sh As Worksheet
Dim rw As Integer
Dim RowCount As Integer
Dim lrow As Long
Dim srow As Integer
lrow = Cells(Rows.Count, 1).End(xlUp).Row
If Intersect(Target, Range("A1")) Is Nothing Then
Else
RowCount = 0
Set sh = ActiveSheet
For rw = 2 To lrow
srow = match(Sheets("origin_sheet
If srow <> 0 Then
Sheets("destination_sheet"
End If
Next rw
End If
End Sub
Thanks
Pete
I need to update cell values from one sheet into cells on another sheet based on a reference cell. Total Excel noob!
I have a sheet that contain an ID number with a bunch of dates that I need to update cells on another sheet based on the ID number.
The ID number is the reference for both sheets!
Private Sub Worksheet_BeforeDoubleClic
Dim sh As Worksheet
Dim rw As Integer
Dim RowCount As Integer
Dim lrow As Long
Dim srow As Integer
lrow = Cells(Rows.Count, 1).End(xlUp).Row
If Intersect(Target, Range("A1")) Is Nothing Then
Else
RowCount = 0
Set sh = ActiveSheet
For rw = 2 To lrow
srow = match(Sheets("origin_sheet
If srow <> 0 Then
Sheets("destination_sheet"
End If
Next rw
End If
End Sub
Thanks
Pete
Roy Cox
Can you attach an example file
ASKER
Origin sheet contains newly updated dates!
Network | User Status | created | Status | approved Status | ready Status | completed Status
4232432 | design | 2017-08-11 | 2016-02-11 | 207-06-13 |
10,000 other similar records as line above with different unique network numbers
Destination sheet is of similar makup also with 10,000 records with the network numbers 95% same as origin numbers which is why
I can't just paste over the columns!
Network | User Status | billed on | Status | approved Status | ready Status | completed Status
4232432 | design | 2018-02-04 | 2016-02-11 | 207-06-13 | 2018-03-27 | 2018-04-08
I used the | to delimit the fields.
Hope this helps
Pete
Network | User Status | created | Status | approved Status | ready Status | completed Status
4232432 | design | 2017-08-11 | 2016-02-11 | 207-06-13 |
10,000 other similar records as line above with different unique network numbers
Destination sheet is of similar makup also with 10,000 records with the network numbers 95% same as origin numbers which is why
I can't just paste over the columns!
Network | User Status | billed on | Status | approved Status | ready Status | completed Status
4232432 | design | 2018-02-04 | 2016-02-11 | 207-06-13 | 2018-03-27 | 2018-04-08
I used the | to delimit the fields.
Hope this helps
Pete
can you attach a small example workbook. I think AutoFilter with VBA might be what you need, but I need to see the sheet layout
I haven't helped you with any code
ASKER
I've uploaded the file. Trimmed down to eliminate confidential stuff and reduce the file size as both sheets have of 10,000 entries. The file however still has 140.000 or so blank row which I've spent the last 30min trying to delete! So sorry about that!
But in a nutshell , I take the date entries from the origin file and upload to the destination master with the network number as reference!
thanks
Pete
EE3.xlsx
But in a nutshell , I take the date entries from the origin file and upload to the destination master with the network number as reference!
thanks
Pete
EE3.xlsx
ASKER
Yeah sorry, I must have hooked the solution button by accident!
Pete
Pete
ASKER
I found this that works nicely! From Barb Henderson on Youtube.
Sub findsomething()
Dim rng As Range
Dim account As String
Dim rownumber As Long
Dim x As Integer
For x = 2 To 20
account = Sheets("originaux").Cells(
MsgBox ("text = ") & Sheets("originaux").Cells(
Set rng = Sheets("Rap").Columns("F:F
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
rownumber = rng.Row
MsgBox ("text = ") & Sheets("Rap").Cells(rownum
Next x
End Sub
Sub findsomething()
Dim rng As Range
Dim account As String
Dim rownumber As Long
Dim x As Integer
For x = 2 To 20
account = Sheets("originaux").Cells(
MsgBox ("text = ") & Sheets("originaux").Cells(
Set rng = Sheets("Rap").Columns("F:F
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
rownumber = rng.Row
MsgBox ("text = ") & Sheets("Rap").Cells(rownum
Next x
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Well if your'e happy with that code then good, but on larger data sets AutoFilter would be much faster!