Solved

Pulling Data from One Spreadsheet to Another

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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,…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now