Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Pulling Data from One Spreadsheet to Another

Posted on 2013-01-18
5
Medium Priority
?
395 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 2000 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

670 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