Large file taking much time to save or open

Hi, prabhuduraraj09,

if you turn on Calculation at the end of the macro Excel will do exactly that - calculate. You would need to turn it off prior to opening the file, when being turned on or the file being saved calculation is taking place.

Maybe you could post the code to look at - the common things are ScreenUpdating, EnableEvents and Calculation to speed the code up but maybe some of your code may be shortened/streamlinied for faster execution.

Ciao,
Holger

Hi, prabhuduraraj09,

if you open a woirkbookand want to refresh pivots in that workbook itīs not ThisWorkbook (which is the workbook with the code) but ActiveWorkbook (as that workbook would be the last one being opened). Waiting for the full code to have a look at.

Ciao,
Holger

Hi Hahobe,

Please find the attached file with codes.
This is what i was talking about to get streamlined.

Hope you would help me in sorting this

Hi, prabhuduraraj09,

as I do not have the files to test this is untested and needs your test (this is only the start of the macro but shows how to go on):

Sub UpdateRVCbackupTransmission()

Dim wb2 As Workbook
Dim sPath As String
Dim ws As Worksheet
Dim sFilename As String
Dim rngArea As Range

With Application
  .ScreenUpdating = False
  .EnableEvents = False
  .Calculation = xlCalculationManual
End With

sFilename = "New UKC PPI Respond v Calc Not PAID NEW "
a = "RVC Backup for Transmission Template.xlsm"
b = sFilename & Format(Date, "dd.mm.yyyy") & ".xlsm"

Set wb2 = Workbooks.Open(Filename:="Q:\Dan work for Prabhu\KC13\March\RVC Backup for Transmission Template.xlsm")

'NPM sheet in template file

With Worksheets("NOT PAID - Match")
  Set rngArea = .Range("A9", .Cells(Rows.Count, "A").End(xlUp)).Offset(-1)
  Set rngArea = rngArea.Resize(rngArea.Rows.Count, .Cells(9, Columns.Count).End(xlToLeft).Column)
End With
With wb2.Sheets("NPM")
  .Range("A8").Resize(rngArea.Rows.Count, rngArea.Columns.Count).Value = rngArea.Value
  .Range("B:C,E:G,O:O").Delete
End With

wb2.Worksheets("NPM").Range("A1:A5").Value = Worksheets("NOT PAID - Match").Range("A1:A5").Value

'NPFF sheet in template

With Worksheets("NOT PAID - Flat Fee")
  Set rngArea = .Range("A9", .Cells(Rows.Count, "A").End(xlUp)).Offset(-1)
  Set rngArea = rngArea.Resize(rngArea.Rows.Count, .Cells(9, Columns.Count).End(xlToLeft).Column)
End With
With wb2.Sheets("NPFF")
  .Range("A8").Resize(rngArea.Rows.Count, rngArea.Columns.Count).Value = rngArea.Value
End With

With Worksheets("NOT PAID - Flat Fee WITH CALC")
  Set rngArea = .Range("A9", .Cells(Rows.Count, "A").End(xlUp)).Offset(-1)
  Set rngArea = rngArea.Resize(rngArea.Rows.Count, .Cells(9, Columns.Count).End(xlToLeft).Column)
End With
With wb2.Sheets("NPFF")
  With .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Resize(rngArea.Rows.Count, rngArea.Columns.Count).Value = rngArea.Value
  .Range("B:C,E:G,O:O").Delete
End With

'...
End Sub

Ciao,
Holger