Solved

Pulling Data from One Spreadsheet to Another

Posted on 2013-01-18
5
387 Views
Last Modified: 2013-01-21
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
Comment
Question by:ronpiecyk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 38795029
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
 

Author Comment

by:ronpiecyk
ID: 38795262
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
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 38795339
ronpiecyk,

Aw, I liked that button!

Ctrl-Shift-T it is.

Regards,
Brian.destination-V2.xlsm
0
 

Author Closing Comment

by:ronpiecyk
ID: 38801661
Very quick response. Very efficient and knowledgeable responder. Thank you!
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38801704
Thanks, ronpiecyk!
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question