CODEWITHSUNDEEP
excelvbaperformance
Diet_Rich
Diet_Rich

Reputation: 55

VBA Copying Sheets Lags in one WB but not another

My VBA Code works on one Workbook but not on the other.

In Summary the code:

Produces the needed end result in both But it lags about a minute in one long But is otherwise identical Sheet names are correct

.Sheets(Array("TotalReimbursement", "ReadyForExport")).Copy is where it lags

Where should I look for the error? Given the same code works fine in another spreadsheet, it seems like the code itself is the wrong place to look.

Sub EmailReimbursementRequest()

Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim TheActiveWindow As Window
Dim TempWindow As Window

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

Set Sourcewb = ActiveWorkbook

With Sourcewb
    Set TheActiveWindow = ActiveWindow
    Set TempWindow = .NewWindow
    .Sheets(Array("TotalReimbursement", "ReadyForExport")).Copy
End With

TempWindow.Close

Set Destwb = ActiveWorkbook

More code from there....It should generate the email in about a second with the attachments.

Upvotes: 0

Views: 31

Answers (1)

Michael
Michael

Reputation: 4848

Could be due to a number of issues:

  • A large number of formulas that need to recalculate
  • Formulas that link to external sources that take a while to recalculate
  • A large amount of formatting (for example, if borders/shading have been applied to the entire worksheet instead of just the relevant areas)
  • A large "Used Area"
  • A large number of named ranges
  • A large number of objects and embedded images

Start by removing unnecessary formatting, clear everything from all unused cells (select all rows below your data then clear and delete, then select all columns to the right of your data then clear and delete), save and reopen, then try running again.

Also, what's the purpose of creating a temp window? That seems unnecessary from the visible code...

Upvotes: 1

Related Questions