Solved

Populating a worksheet in one workbook from values in another workbook.

Posted on 2014-01-19
20
848 Views
Last Modified: 2014-02-09
I have a worksheet that displays sale values for whatever region is selected. I need to match the selected region with the same region in another workbook that has multiple worksheets to populate the first worksheet. How do I do this? I've tried the following:

Sub Retrieve_Sales()

Dim OldRegion As String
Dim NewRegion As String

OldRegion = Range("E2").Value
NewRegion = Range("C4").Value

For Each cell In Range("E2:F19")
cell.Value = Replace(cell.Value, OldRegion, NewRegion)
Next cell

End Sub
0
Comment
Question by:Barbara69
  • 10
  • 9
20 Comments
 
LVL 3

Expert Comment

by:Conner Turner
ID: 39793031
Hello,

I'm not sure if this is quite what you are as,in but to obtain values across open workbooks use an "!" as an operant before the name so I I was to get C4 in Profit to be the same as D12 in the Loss workbook I would type into C4 "=!ProfitC4" this would obtain data from across the workbooks.

You can always use an =vlookup if your data is arranged in a table format.

=vlookup(Range,String,TableNamesColumn,FALSE)

HTH,
Conner
0
 
LVL 80

Expert Comment

by:byundt
ID: 39793034
Are you using formulas to pull the data back? If so, you could write your macro like this:
Sub Ranger()
Dim oldWorksheetName As String, NewWorksheetName As String
oldWorksheetName = "Sheet4"
NewWorksheetName = "Sheet1"
Range("J1:J10").Replace oldWorksheetName, NewWorksheetName, LookAt:=xlPart, MatchCase:=False
End Sub

Open in new window

When reviewing your code, I noticed that you used cell E2 for the OldRegion and C4 for the NewRegion. But your loop immediately overwrote the value in cell E2 because it was working on cells in range E2:F19. Was this a typo when you were composing the question?
0
 
LVL 80

Expert Comment

by:byundt
ID: 39793043
If you try Conner's suggestion of using a formula to return the values, you will need to use INDIRECT to return the range because the sheet name is a variable. If so, be advised that the source workbook must be open for INDIRECT to work. You will get an error if the source workbook is closed.

For example, to get the value from cell E2 in worksheet specified by cell E1 in workbook Otherworkbook.xlsx, you might use:
=INDIRECT("'[Otherworkbook.xlsx]" & $E$1 & "'!" & CELL("address",E2))
As you copy this formula down and across, the CELL function will reference other cells so the formula pulls other values from E2:F19 in that worksheet.
0
 

Author Comment

by:Barbara69
ID: 39793228
The values in the first workbook, worksheet 2 are linked to the second workbook, worksheets (i.e. F8 in 1st workbook, worksheet 2 ='[Regions.xlsx]Region 1'!B7). Cell C4 on worksheet 2 in the first workbook is where the new region (i.e. region 4) is selected from a drop down box. A search needs to be done to find region 4 in the second closed workbook, which has 20 worksheets in it, then the values from the second workbook that match the worksheet for region 4 need to go in the first workbook, worksheet 2 in cells E2:F19.
0
 
LVL 80

Expert Comment

by:byundt
ID: 39793232
If the second workbook is closed, then you will need VBA (such as the Ranger macro previously posted) to facilitate getting the data.

Are you using a formula to pull the values? If so, please post it.

If you aren't using a formula to pull the values, please consider doing so. VBA has difficulty getting data from closed workbooks (can be done, but is somewhat involved).
0
 

Author Comment

by:Barbara69
ID: 39793251
I forgot to tell you the range for the values in the second workbook B1:B18.
0
 
LVL 80

Expert Comment

by:byundt
ID: 39793281
Barbara69,
Make sure that you have formulas in cells E2:E19 that look like:
='C:\Some path\[Regions.xlsx]Region 1'!B7

The following code (which must be installed in the code pane of the worksheet being modified) will update those formulas when you change the selection in cell C4 dropdown:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim targ As Range, newSheet As Range
Dim frmla As String, oldSheet As String
Set newSheet = Range("C4")
Set targ = Range("E2:E19")
If newSheet.Value <> "" And Not Intersect(newSheet, Target) Is Nothing Then
    Application.EnableEvents = False
    frmla = targ.Cells(1, 1).Formula
    oldSheet = Split(frmla, "]")(1)
    oldSheet = Split(oldSheet, "'")(0)
    targ.Replace oldSheet, newSheet.Value, LookAt:=xlPart, MatchCase:=False
    Application.EnableEvents = True
End If
End Sub

Open in new window

0
 

Author Comment

by:Barbara69
ID: 39794421
byundt when I run your code, I get argument not optional error.
0
 
LVL 80

Expert Comment

by:byundt
ID: 39794617
I developed the code using Excel 2013, and am not seeing that error message. Which version are you using?

I did make a couple of small tweaks to the code so it would run a little faster (turning off screen updating), could tolerate a worksheet name containing single quotes, and wouldn't error if there was no formula in cell E2.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim targ As Range, newSheet As Range
Dim frmla As String, oldSheet As String
Set newSheet = Range("C4")
Set targ = Range("E2:E19")
If newSheet.Value <> "" And Not Intersect(newSheet, Target) Is Nothing Then
    frmla = targ.Cells(1, 1).Formula
    If frmla <> "" Then
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        oldSheet = Split(frmla, "]")(1)
        oldSheet = Split(oldSheet, "!")(0)
        If Right(oldSheet, 1) = "'" Then oldSheet = Left(oldSheet, Len(oldSheet) - 1)
        targ.Replace oldSheet, newSheet.Value, LookAt:=xlPart, MatchCase:=False
        Application.EnableEvents = True
    End If
End If
End Sub

Open in new window

I have attached my test workbooks. If you want to try testing them at your end, please proceed as follows:
1.  Open workbooks Regions.xlsx and UpdateReferencesToClosedWorkbookQ28342697.xlsm
2.  Make sure that you enable macros
3.  Save Regions.xlsx to any folder on your computer
4.  Select cell E2 In UpdateReferencesToClosedWorkbookQ28342697.xlsm
5.  Click in the formula bar and paste the following formula:
='[Regions.xlsx]Region 3'!B1
6.  Copy the formula from cell E2 down to cell E10. These formulas should return different values.
7.  Close workbook Regions.xlsx. The formulas in E2:E10 should update with the path to the closed workbook
8.  Change the value in the dropdown in cell C4. Different values should be returned.
UpdateReferencesToClosedWorkbook.xlsm
Regions.xlsx
0
 

Author Comment

by:Barbara69
ID: 39794904
Yours worked, but when I put the code in my workbook, I got the same error as before. I'm using 2010. I came up with the following, but I'm not sure of the syntax.

Sub Retrieve_Sales()

Dim NewRegion As String
Dim x as integer

NewRegion = Range("C4").Value

    For x = 1 To Workbooks("Regions.xlsx").Sheets.Count
        If NewRegion = Workbooks("Regions.xlsx").Sheets(x).name Then
             
      Worksheets("Sales").Range("E2:F19").Replace _
       What:= Worksheets("Sales").Range("E2:F19"),  
        Replacement:=Workbooks(“Regions.xlsx”).Range(B7:B18), _
       SearchOrder:=xlByRows, MatchCase:=False
             
        End If
    Next x

End Sub
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 80

Expert Comment

by:byundt
ID: 39795413
The code you posted in your latest Comment has a number of issues:
1.  There is no need to loop through the worksheets in Regions.xlsx. You already know the name of the desired worksheet and can refer to it by name (instead of by index number) with:
Workbooks("Regions.xlsx").Worksheets(NewRegion)
2.  The statement beginning Worksheets("Sales").Range does not do what you think it does. That method is designed to replace one piece of text with another--not one range of cells with another.
3.  The range being copied needs to match the range it is being pasted into. The code is trying to copy B7:B18 into E2:E19, with a mismatch on both number of rows and number of columns.

I don't know how you intend to resolve issue #3, but try modifying the following code:
Sub Retrieve_Sales()
Dim NewRegion As String
NewRegion = Range("C4").Value
If NewRegion <> "" Then
    Worksheets("Sales").Range("E2:E19").Value = Workbooks(“Regions.xlsx”).Worksheets(NewRegion).Range("B1:B18").Value
End If
End Sub

Open in new window

0
 
LVL 80

Expert Comment

by:byundt
ID: 39795445
If my most recent Comment is not resolving the issue for you, please post a sample workbook that reproduces the problem. This sample workbook must have the code exactly as you tested, but doesn't need to contain any data at all.

Please also post a workbook containing a single sheet of data just like you need to have copied into the other workbook. The data can be made up, but it needs to be in the exact same cells as the real data.

If you have merged cells in either the source or destination ranges, that will cause problems unless the source and destination ranges match exactly. Make sure that the sample workbooks you post have merged cells exactly as they occur in your real workbooks.
0
 

Author Comment

by:Barbara69
ID: 39800619
I was able to modify your comment and it now works. E2 is a merged cell on the 1st worksheet. Does the Regions workbook have to be open?

Sub Retrieve_Sales()

Dim NewRegion As String

NewRegion = Range("C4").Value

If NewRegion <> "" Then
Worksheets("Sales").Range("E2").Value = Workbooks("Regions.xlsx").Worksheets(NewRegion).Range("B1").Value

Worksheets("Sales").Range("F3:F19").Value = Workbooks("Regions.xlsx").Worksheets(NewRegion).Range("B2:B18").Value


End If

End Sub
0
 
LVL 80

Expert Comment

by:byundt
ID: 39800674
With the type of code in your last Comment, the Regions.xlsx workbook must be open. VBA has difficulty directly getting data from closed workbooks.

If you want the code to work with a closed Regions.xlsx workbook, then the best approach is to put formulas in the cells that receive the data. The VBA code can modify those formulas to point to a different worksheet. This indirect approach is what I suggested in http:/Q_28342697.html#a39793281

If that macro isn't working for you, please post your workbooks so I can make the necessary modifications for merged cells.

Brad
0
 

Author Comment

by:Barbara69
ID: 39800983
Please see the attached.
0
 
LVL 80

Expert Comment

by:byundt
ID: 39801271
Barbara,
There was no attachment.

One common "gotcha" with attaching a file is failing to enter a description after you click the Upload button. If you don't, the file won't be attached when you click the Submit button.

Brad
0
 

Author Comment

by:Barbara69
ID: 39801429
0
 

Author Comment

by:Barbara69
ID: 39801431
0
 

Author Comment

by:Barbara69
ID: 39801437
I did enter a description, but I've sent the attachments.
0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 39801597
I added formulas pointing to an open Reports.xlsx workbook in cells E2, F3:F5 and E8:F19. These formulas look like this:
='[Regions.xlsx]Region 19'!$B2

I then put a Worksheet_Change event sub in the code pane for the Sales worksheet. I could then eliminate the command button you had to the right of the dropdown in cell C4, as the Worksheet_Change sub runs automatically when the user changes a cell value.

After making these changes, the data updated as desired when different choices were made in cell C4 dropdown when workbook Regions.xlsx was open. The data also updated as desired when Regions.xlsx was closed.

Important

You need to link the formulas to the location of Regions.xlsx on your server (or wherever it is stored). To do so, launch Excel then follow these instructions exactly:
1.  Open Regions.xlsx from your server
2.  Open the attached copy of Sales-Report.xlsm. Make sure you enable macros when this workbook opens.
3.  Test the code by changing the value in cell C4. If you are satisfied, proceed to step 4.
4.  Close Regions.xlsx
5.  Once again, test the code by changing the value in cell C4. If you are satisfied, proceed to step 6.
6.  Save the file Sales-Report.xlsm, renaming it as you like.

After having completed step 6, the formulas in cells E2, F3:F5 and E8:F19 will be linked to the appropriate worksheet in the closed workbook Regions.xlsx as stored on your server. The macro will update those formulas when the user changes the dropdown selection in cell C4, thereby bringing the desired data back to the workbook.

The following code must be installed in the code pane for the Sales worksheet in workbook Sales-Report.xlsm. It won't work at all if installed anywhere else!
Private Sub Worksheet_Change(ByVal Target As Range)
Dim targ As Range, targ1 As Range, targ2 As Range, targ3 As Range, newSheet As Range
Dim frmla As String, oldSheet As String
Set newSheet = Range("C4")
Set targ1 = Range("E8:F19")
Set targ2 = Range("F3:F5")
Set targ3 = Range("E2")
If newSheet.Value <> "" And Not Intersect(newSheet, Target) Is Nothing Then
    frmla = targ1.Cells(1, 1).Formula
    If frmla <> "" Then
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        oldSheet = Split(frmla, "]")(1)
        oldSheet = Split(oldSheet, "!")(0)
        If Right(oldSheet, 1) = "'" Then oldSheet = Left(oldSheet, Len(oldSheet) - 1)
        For Each targ In Union(targ1, targ2, targ3).Areas
            targ.Replace oldSheet, newSheet.Value, LookAt:=xlPart, MatchCase:=False
        Next
        Application.EnableEvents = True
    End If
End If
End Sub

Open in new window


I've attached the updated Sales-Report.xlsm file. It includes the suggested code as well as the required formulas for an open copy of Regions.xlsx. I did not change anything in Regions.xlsx, and so did not attach it.
Sales-ReportQ28342697.xlsm
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

746 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

13 Experts available now in Live!

Get 1:1 Help Now