[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

Pulling Data from One Spreadsheet to Another

How can I pull values from one spreadsheet into another, where I can choose which cells get overridden based on matching other cells?

For example, source spreadsheet has 3 columns: WHO, TOTAL, and SUB, and 3 rows - CompanyA, CompanyD, and CompanyF. Numbers are populated in the TOTAL and SUB columns.
The destination spread is formatted the same way. It has 7 rows, for CompanyA through CompanyG, also with numbers populated in the TOTAL and SUB columns.

I want to create an automated mechanism to take the data in the TOTAL and SUB columns of the source spreadsheet (Company's A,D, and F) and bring it over to the destination spreadsheet, overwriting the data in the TOTAL and SUB columns for Company's A,D, and F.

Is this possible to do? Attached are both files. Any advise will be greatly appreciated. Thanks.
Source.xlsx
destination.xlsx
0
ronpiecyk
Asked:
ronpiecyk
  • 3
  • 2
1 Solution
 
redmondbCommented:
Please see the attached. A few points...
(1) Does clicking on the blue button count as an "automated mechanism"? If not, please let me know and I can add a suitable Event macro.
(2) I assumed that you only wanted to update a single Destination match. If not, I can change it to do multiples.
(3) If you will have tens of thousands of source entries and hundreds of thousands of destination entries please let me know and I'll give you faster (but more opaque) code.

The code is...
Option Explicit

Sub Get_Updates()
Dim xCell As Range
Dim xFind As Range
Dim xLast_Row_Dest As Long
Dim xLast_Row_Srce As Long
Dim xSrce As Worksheet
Dim xDest As Worksheet

Set xSrce = Workbooks("source.xlsx").Sheets("Sheet1")
Set xDest = ThisWorkbook.Sheets("Sheet2")

xLast_Row_Dest = xDest.Range("A1").SpecialCells(xlLastCell).Row
If xLast_Row_Dest < 2 Then
    MsgBox ("No data found in Destination (" & xDest.Parent.Name & ":" & xDest.Name & ") - run cancelled.")
    Exit Sub
End If

xLast_Row_Srce = xSrce.Range("A1").SpecialCells(xlLastCell).Row
If xLast_Row_Srce < 2 Then
    MsgBox ("No data found in Source (" & xSrce.Parent.Name & ":" & xSrce.Name & ") - run cancelled.")
    Exit Sub
End If

For Each xCell In xSrce.Range("A2:A" & xLast_Row_Srce)
    Set xFind = xDest.Range("A2:A" & xLast_Row_Dest).Find(What:=xCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows _
                            , SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not xFind Is Nothing Then
        xFind.Offset(0, 1) = xCell.Offset(0, 1)
        xFind.Offset(0, 2) = xCell.Offset(0, 2)
    Else
        MsgBox ("No match found for " & xCell & ".")
    End If
Next

End Sub

Open in new window

Regards,
Brian.destination.xlsm
0
 
ronpiecykAuthor Commented:
Thank your Brian, this is EXACTLY what I was looking for. One last request - could you take out the blue button, and instead make it that it can be called via keystroke, like CTRL-SHFT-T or something similar? Additionally, can we make it that it can be run on ANY sheet in destination.xlsm, not just Sheet2? Thanks.
0
 
redmondbCommented:
ronpiecyk,

Aw, I liked that button!

Ctrl-Shift-T it is.

Regards,
Brian.destination-V2.xlsm
0
 
ronpiecykAuthor Commented:
Very quick response. Very efficient and knowledgeable responder. Thank you!
0
 
redmondbCommented:
Thanks, ronpiecyk!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now