Reference another workbook and return values from the same cell across multiple sheets/tabs

whatsup · #4 11-07-2014, 07:38 PM

Here an example how you can insert either a formula or a value from another workbook into your workbook of destination.

With your provided uploads, you will run into an error after the third loop, since your source doesn't provide sufficent Sheets. But it will show you, how you can assemble parts of a path to a working formula, or use them to get values of an open workbook.

Copy the code in a new module and step through the code using F8 while observing the changes in your sheet.

Code:

Sub Get_Data()
Dim lngCol As Long
Dim lngMonth As Long
Dim lngStart As Long
Dim strformula As String
 
Const strCellRef As String = "$F$6"
 
With ActiveSheet
    lngStart = .Cells(2, 1).Value
    lngMonth = Month(.Cells(2, 1).Value)
 
    Do While Month(lngStart + lngCol) = lngMonth
        'Insert formula
        .Cells(2, lngCol + 2).Formula = "='[Source WKB.xlsx]" & Format(lngStart + lngCol, "DD.MM.YY") & "'!" & strCellRef
        'Insert value
        .Cells(2, lngCol + 2).Value = Workbooks("Source WKB.xlsx").Sheets(Format(lngStart + lngCol, "DD.MM.YY")).Range(strCellRef).Value
        lngCol = lngCol + 1
    Loop
End With
 
 
End Sub

Any questions?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
I need to add multiple values based on multiple criteria in a cell not sure what to do	AUHAMM	Excel	3	10-27-2014 09:11 PM
Reporting Values Between Sheets/Tabs	jjbNana	Excel	3	10-06-2011 10:28 PM
How to count multiple values in a single cell, except zero?	iuliandonici	Excel	1	04-13-2011 09:45 PM
Sum & difference between multiple values in a single cell	iuliandonici	Excel	4	04-13-2011 09:27 PM
How do I merge data from one sheet in a workbook out into multiple sheets	nolesca	Excel	4	06-07-2010 08:13 AM