CODEWITHSUNDEEP
vbaexcel
kidEd2
kidEd2

Reputation: 11

Pause VBA code without pausing spreadsheet

I have a VBA code where it is copying and pasting data from a spreadsheet that pulls data from an external server. Consequently, that spreadsheet takes awhile (from 10-30 seconds) to load the data.

I have researched how to pause a macro - application.wait, sleep, etc - but they also pause the spreadsheet functions so the data from the external server cannot load. Therefore when the code copies the data, it pastes #DIV/0!. Is there a function that can interrupt/stop/pause the macro to give the server data time to load?

I also would like this macro to run without needing user input (some suggestions online had the user advancing the code each iteration).

Sub all()

    Dim wb1 As Workbook
    Set wb1 = Workbooks("all")

    Dim wb2 As Workbook
    Set wb2 = Workbooks("other")

    For i = 2 To 5

        Application.Wait Now() + TimeValue("00:01:00")

        wb2.Sheets("Sheet1").Cells(4, 2).Copy
        wb1.Sheets("Sheet1").Cells(i, 18).PasteSpecial xlPasteValues

    Next i
End Sub

Upvotes: 1

Views: 5480

Answers (1)

FreeMan
FreeMan

Reputation: 5687

Application.Wait suspends processing in Excel. This means that your background processing will suspend, too.

Instead, try this:

Dim WaitTime as Date
For i = 2 To 5

    WaitTime = Now() + TimeValue("00:01:00")
    While Now() < WaitTime
        DoEvents
    Wend

    wb2.Sheets("Sheet1").Cells(4, 2).Copy
    wb1.Sheets("Sheet1").Cells(i, 18).PasteSpecial xlPasteValues

Next i

Upvotes: 2

Related Questions