I am running into a problem when trying to copy multiplesheets in a row in excel. The copy works if its only 1 or 2 sheets but anything over 2 copies in a row it fails, and I dont get an error.

I am using this code...

VB Code:
  1. Set oSheet = oBook.Worksheets(oBook.Sheets(oBook.Sheets.Count).Name)
  2. lngSheetCount = oBook.Sheets.Count
  3.        
  4. 'Copy inital last sheet into new sheet
  5.  oExcel.ActiveWorkbook.Sheets(lngSheetCount).Copy _
  6. After:=oExcel.ActiveWorkbook.Sheets(lngSheetCount)
  7. Set oSheet = oExcel.ActiveSheet
  8. 'Rename the worksheet
  9. oSheet.Name = Firms.Item(clc)

I was wondering if anyone else had run into this issue, I have tried placing DoEvents in some places to try to pass control back to windows so it could finish the copy but it doesnt seem to work.