Hello,

I have a excel file (workflowtracker.xls) with 2 sheets. Sheet 1 - user input and sheet 2 (dashboard) using values from sheet1. my dashboard in sheet2 in the same excel file works as per my requirement. Sheet 1 has all the user input data and sheet 2(dashboard) has a summary of data or you can say a list of delayed deliveries, WIP etc.

Now I want to make the dashboard working from a separate excel file (xyz.xls) as keeping both the sheets in the same file is a security risk.

Can anyone help me with the code. my sheet 2 should be in a separate excel file. How to call the the first excel file.

Below is my code which works when both the sheet are in the same excel file.

[VBA]
Option Explicit

Dim TempVal As String

Private Sub Worksheet_Activate()

Application.DisplayAlerts = False
TempVal = Range("G5").Value
Range("G5").Value = "Wait Refreshing Data"
Application.ScreenUpdating = False
If ActiveSheet.AutoFilterMode = False Then
Range("A5:G65536").Select
Selection.AutoFilter
Else
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
End If

Range("A6").FormulaR1C1 = _
"=IF(OR('Daily Workflow Tracker'!RC[20]>=3,'Daily Workflow Tracker'!RC[22]>0),'Daily Workflow Tracker'!RC,"""")"
Range("B6").FormulaR1C1 = "=IF(RC[-1]<>"""",'Daily Workflow Tracker'!RC[5],"""")"
Range("C6").FormulaR1C1 = "=IF(RC[-2]<>"""",'Daily Workflow Tracker'!RC[8],"""")"
Range("D6").FormulaR1C1 = "=IF(RC[-3]<>"""",'Daily Workflow Tracker'!RC[10],"""")"
Range("E6").FormulaR1C1 = "=IF(RC[-4]<>"""",'Daily Workflow Tracker'!RC[16],"""")"
Range("F6").FormulaR1C1 = "=IF(RC[-5]<>"""",'Daily Workflow Tracker'!RC[17],"""")"
[/VBA]
Please help...