Could you post an updated copy of your file and explain your new request in detail?
Sure, the main workbook has become pretty robust, so I've parsed out what I'm having trouble with to this workbook which contains the button/code and some sample data. If you notice on the tracker sheet, there are duplicate Order numbers, but the current code only updates the first match that it finds and moves to the next unique order number and updates that.
What I would like it to do, is update every instance of the order number, rather than just the first match it finds. Does this make sense?
- Sub CopyData()
- Application.ScreenUpdating = False
- Dim LastRow As Long, order As Range, srcWS As Worksheet, desWS As Worksheet, fnd As Range, sAddr As String
- Set srcWS = Sheets("Update From")
- Set desWS = Sheets("Tracker")
- LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
- For Each order In srcWS.Range("A2:A" & LastRow)
- Set fnd = desWS.Range("A:A").Find(order, LookIn:=xlValues, lookat:=xlWhole)
- If Not fnd Is Nothing Then
- sAddr = fnd.Address
- fnd.Offset(, 1).Resize(, 2).Value = order.Offset(, 1).Resize(, 2).Value
- Set fnd = desWS.Range("A:A").FindNext(fnd)
- Loop While fnd.Address <> sAddr
- sAddr = ""
- End If
- Next order
- Application.ScreenUpdating = True
- End Sub
Hm it seemed to work the first time I ran it, but I cleared out the data in the tracker (destination sheet) and ran it again and it only populates the first row. Odd that it populated everything the first run but can't get it to work again.
Please attach a copy of the file that is not working.
Welp, I tried the same file/same code on a different computer and it works fine. Not sure what happened with the one that didn't work question about this code though, is there a way to specify which columns in the destination sheet that the data will be inserted to? Noticed the way it is right now I could only get it to paste into the 2 columns adjacent to the first column.
is there a way to specify which columns in the destination sheet that the data will be inserted to
If the column will always be the same, then change the column reference (the number 1) in the code below to the number of columns to the right where the data should be inserted. For example, if you want to paste 5 columns to the right, change the 1 to 5. If the column will not always be the same then the code will have to be modified to prompt for the desired column. Please advise.
Awesome, got it working in my master workbook, thanks so much!