Solved

Pulling Data from One Spreadsheet to Another

Posted on 2013-01-18
5
383 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

756 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