-
VBA for multiple excel file
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...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules