CODEWITHSUNDEEP
excelvba
A.Oreo
A.Oreo

Reputation: 321

Wait the time of calculation in vba

I want to use vba run the loop for the data (2010, 2011) (2011, 2012) (2012, 2013)...to create a new workbook one by one.

But here is a problem that, when we enter the parameter Current: and Previous: we need wait for a while for the calculation of the formulas(It is on the Bloomberg terminal), if we create or copy the data in the sheet immediately, it may be empty or (Data requesting). So how do I solve this problem?

enter image description here

Sub Copy()
  For i = 2015 To 2017
     ThisWorkbook.Activate
     Range("C8") = i
     Range("C9") = i + 1

     Dim wb As Workbook
     Set wb = Workbooks.Add
     ThisWorkbook.Sheets("Sheet1").Copy Before:=wb.Sheets(1)
     wb.SaveAs "C:\Users\user\Desktop\VBA test\" & i & ".xlsx"

  Next i
End Sub

My questions are

1.How to save as value?

2.In this case, the new book always has a empty sheet1 and a copied sheet1(1) could I only have a copied sheet1?

3.How to close the new workbooks when created or don't let them be open(In this case, I have a lot of opened workbooks)

4.How to stay after the syntax Range("C9") = i + 1 to give the enough time to the calculation by the original workbook?

Upvotes: 1

Views: 2071

Answers (2)

Egan Wolf
Egan Wolf

Reputation: 3573

1.

ActiveSheet.Cells.Copy
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats

2.

ActiveSheet.Copy

3.

ActiveWorkbook.Close

4.

Application.Wait (Now + TimeValue("00:00:10"))'wait 10 seconds

To sum it up:

Sub Copy()
  Application.ScreenUpdating = False
  For i = 2015 To 2017
     ThisWorkbook.Activate
     Range("C8") = i
     Range("C9") = i + 1
     Application.Wait (Now + TimeValue("00:00:10"))'wait 10 seconds
     Dim wb As Workbook
     Dim ws As Worksheet
     ThisWorkbook.Sheets("Sheet1").Copy
     Set wb = ActiveWorkbook
     Set ws = ActiveSheet
     ws.Cells.Copy
     ws.Cells.PasteSpecial Paste:=xlPasteValues
     ws.Cells.PasteSpecial Paste:=xlPasteFormats
     wb.SaveAs "C:\Users\user\Desktop\VBA test\" & i & ".xlsx"
     wb.Close
  Next i
  Application.ScreenUpdating = True
End Sub

I took this parts from my old macro, but should work fine.

I added Application.ScreenUpdating = False/True. Always use it in macros that are doing a lot of changes on your screen (deleting/inserting rows/columns, opening/closing workbooks etc.).

Upvotes: 1

Vityata
Vityata

Reputation: 43585

Just guessing... Try to write twice Calculate, it would slow down the loop and thus it probably would be ok:

Option Explicit

Public Sub TestMe()

    Dim i As Long

    For i = 1 To 5

        Calculate
        Worksheets(i).Copy After:=Worksheets(i + 1)
        Calculate

    Next i

End Sub

Upvotes: 0

Related Questions