Hi,
Hoping someone can help.
I have the below VBA code within a workbook (book 1) on Sharepoint.
It opens a separate workbook also on Sharepoint, filters data and copies it to a sheet in book 1.
Then I have Index match formulas in another sheet in book 1 that finds the data
It does everything I need to it do, but I'm finding the workbook is recalculating every couple of minutes.
If I remove the below VBA code from book 1 it stops recalculating.
Any Help would be greatly appreciated
--------------------------------------------------------------
Dim wb1 As Workbook, wb2 As Workbook, numrows As Double
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Set wb1 = ThisWorkbook
wb1.Sheets("Summary").Activate
Sheets("Summary").UsedRange.ClearContents
IDMlocation = "https://sharepoint.com/Shared Documents/General/Project tracker/TASK_SIGNOFF_REPORT.csv"
Set wb2 = Workbooks.Open(Filename:=IDMlocation, ReadOnly:=False)
With wb2.Sheets("TASK_SIGNOFF_REPORT")
'' Filters for programs ''
Range("G:G").AutoFilter _
Field:=1, _
Criteria1:=Array("XXXX" & "*"), Operator:=xlFilterValues
Columns("C:F").EntireColumn.Hidden = True
Columns("H:K").EntireColumn.Hidden = True
Columns("U:V").EntireColumn.Hidden = True
numrows = ActiveSheet.Range("B:B").End(xlDown).Row
rangestring = "A1:V" & numrows
Range(rangestring).Copy _
Destination:=wb1.Worksheets("Summary").Range("A1")
End With
wb2.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Hoping someone can help.
I have the below VBA code within a workbook (book 1) on Sharepoint.
It opens a separate workbook also on Sharepoint, filters data and copies it to a sheet in book 1.
Then I have Index match formulas in another sheet in book 1 that finds the data
It does everything I need to it do, but I'm finding the workbook is recalculating every couple of minutes.
If I remove the below VBA code from book 1 it stops recalculating.
Any Help would be greatly appreciated
--------------------------------------------------------------
Dim wb1 As Workbook, wb2 As Workbook, numrows As Double
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Set wb1 = ThisWorkbook
wb1.Sheets("Summary").Activate
Sheets("Summary").UsedRange.ClearContents
IDMlocation = "https://sharepoint.com/Shared Documents/General/Project tracker/TASK_SIGNOFF_REPORT.csv"
Set wb2 = Workbooks.Open(Filename:=IDMlocation, ReadOnly:=False)
With wb2.Sheets("TASK_SIGNOFF_REPORT")
'' Filters for programs ''
Range("G:G").AutoFilter _
Field:=1, _
Criteria1:=Array("XXXX" & "*"), Operator:=xlFilterValues
Columns("C:F").EntireColumn.Hidden = True
Columns("H:K").EntireColumn.Hidden = True
Columns("U:V").EntireColumn.Hidden = True
numrows = ActiveSheet.Range("B:B").End(xlDown).Row
rangestring = "A1:V" & numrows
Range(rangestring).Copy _
Destination:=wb1.Worksheets("Summary").Range("A1")
End With
wb2.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic