Call a named range from a different workbook

[Nikijune]

Hello,

I'm sure if this is even possible, but I would like to call a range of data from a workbook into another.

Basically I have a workbook that has data for each day. I want to call the data for today only into a workbook of todays data only. So each day I go into that workbook it will update with the named range for that day.

Any suggestions?

Thanks Nik

 

[JLGWhiz]

No spaces in names

=[Workbook1.xlsx]Sheet1!A1

With spaces in names

'='[my Workbook.xlsx]my Sheet'!A1

If the workbook is closed, precede the bracketed portion with the path delimited with backslash (/) and enclose path, workbook, sheet group with apostrophe.

 

[Nikijune]

VBA code to Copy Data from a different workbook

I have renamed this because I don't think what I was asking before was possible.

Let me give a bit more context and hopefully someone can help.

In my Forecast spread sheet I have tables for each week that shows the forecasted manpower, with times all the way down column A. From B4 - I4 are headers. i.e calls, required staff, Provided staff and so on....

Above these headings in cell B3 - I4 is the date (the cell is merged)

This is then replicated in columns K-R for the next day and again in T-AA... so on and so on.

I then have another spread sheet, for real time. What I want to happen is, when I open the real time spread sheet, I want it to be populated with the data for that day from the forecasting sheet.

I thought it would be quite simple to name the range of each day in the forecast sheet and then call that range. I am thinking now that perhaps I could have code that on opening the realtime sheet it calls the forecast sheet, looks for todays date and then populates the relevant data.

Am I over thinking this? Really struggling right now

 

[JLGWhiz]

Re: VBA code to Copy Data from a different workbook

Based on post #3. Merged cells can be a pain when using VBA to address them. If you had your date in a single cell, like above the first cell of the weekly manpower forecast, then code like that below could be used to find and copy the data.

Private Sub Workbooks_Open()
Dim sh1 As Worksheet, sh2 As Worksheet, fn As Range
Set sh1 = Workbooks(1).Sheets("Forecast") 'Edit workbook and sheet name
Set sh2 = Workbooks(2).Sheets("Realtime") 'Edit workbook and sheet name
 Set fn = sh1.Rows(3).Find(Date, , xlValues, xlWhole)
  If Not fn Is Nothing Then
   fn.Offset(1).Resize(1, 8).Copy sh2.Range("B4")
  End If
End Sub

The code would be installed in the Realtime workbook 'ThisWorkbook' code module. That workbook would need to be saved as a macro enabled workbook.
But the merged cell could cause a hiccup if you try the code while still merged.

 

[JLGWhiz]

Re: VBA code to Copy Data from a different workbook

There are some other pertinent factors here. The forecast workbook would need to be already open when you open the Realtime workbook for the code, as written, to work. You really do not need two workbooks to do what you want. You could use two sheets in the same workbook, and use a sheet activate event to trigger the code to run and populate your real time sheet. But you could have other reasons for separate workbooks.

 

[Nikijune]

Re: VBA code to Copy Data from a different workbook

Thanks JLGWhiz

Other users will populate the Forecasting workbook, which is why it would have to be separate.

I have just tried the code, but not sure if I have formatted correctly. Could you check for me please?

Private Sub Workbooks_Open()
Dim sh1 As Worksheet, sh2 As Worksheet, fn As Range
Set sh1 = Workbooks("O:\Customer Operations\Shared\Operational Excellence\Planning\Real Time\Dev\Week Ahead Template.xlsm").Sheets("Sheet1")
Set sh2 = Workbooks("O:\Customer Operations\Shared\Operational Excellence\Planning\Real Time\Dev\RealTime RCS v3.xlsm").Sheets("Sheet1")
Set fn = sh1.Rows(3).Find(Date, , xlValues, xlWhole)
  
  If Not fn Is Nothing Then
   fn.Offset(1).Resize(1, 8).Copy sh2.Range("B4")
  End If
End Sub

 

[JLGWhiz]

Re: VBA code to Copy Data from a different workbook

Made some minor tweaks to title line and path statement. Using a variable for the path saves space.

Private Sub Workbook_Open()
Dim sh1 As Worksheet, sh2 As Worksheet, fPath As String, fn As Range
fPath = "O:\Customer Operations\Shared\Operational Excellence\Planning\Real Time\Dev\"
Set sh1 = Workbooks(fPath & "Week Ahead Template.xlsm").Sheets("Sheet1")
Set sh2 = Workbooks(fPath & "RealTime RCS v3.xlsm").Sheets("Sheet1")
Set fn = sh1.Rows(3).Find(Date, , xlValues, xlWhole)
     If Not fn Is Nothing Then
         fn.Offset(1).Resize(1, 8).Copy sh2.Range("B4")
     End If
End Sub

Also, in the 'Find' statement it was assumed that your Real Time sheet would always be for the current date, ergo, the constant 'Date' as the search criteria. If you try to use it for any other date, that would need to be changed. If you have formulas in the copied ranges, they will be invalid in the destination sheet and you would need to use the 'PasteSpecial xlPasteValues' method instead, which would entail moving the destination 'sh.Range("B4").PasteSpecial xlPasteValues' to the next line after .Copy.

 

[Nikijune]

Re: VBA code to Copy Data from a different workbook

It is falling over at this bit

  Set sh1 = Workbooks(fPath & "Week Ahead Template.xlsm").Sheets("Sheet1")

 

[JLGWhiz]

Re: VBA code to Copy Data from a different workbook

It is falling over at this bit

  Set sh1 = Workbooks(fPath & "Week Ahead Template.xlsm").Sheets("Sheet1")

What does 'falling over' mean? Do you get an error message? If so, what is it? Are both workbooks open? Is either workbook open, if so which one? Is it the host for the code?