i running macro opens file referencing 1 working in, pastes relevant items values separate sheet , makes workbook out of sheet.
the reason why doing because there several thousand countifs, averageifs, , processor-intensive ilk.
the program runs start finish, fine. issue few of items calculated before copy/paste operation , lot of #value errors on copy of sheet formulas--even though formulas calculating correctly on further inspection.
i suspect correct course of action delay run until sheet finishes calculating. , appreciated.
edit: i've tried manner of application.calculations , nothing seems working. links , items calculate if open manually , let processor thing. items calculate ones contain "counta" somewhere in it. possible application calculation methods don't work countifs , like?
shouldn't hard - worksheet object has calculate property fires after calculates. can add custom property worksheet exposes flag set after done calculating. in worksheet code has time consuming calculation...
option explicit private can_copy boolean public property copyok() copyok = can_copy end property private sub worksheet_calculate() can_copy = true end sub private sub worksheet_activate() can_copy = false end sub private sub worksheet_change(byval target range) can_copy = false end sub 'for volitile functions. private sub worksheet_selectionchange(byval target range) can_copy = false end sub ...and in calling code:
dim book workbook set book = application.workbooks.open("c:\foobar.xlsm") while not book.worksheets("sheet1").copyok doevents loop 'do thing... note missed events trigger recalculation, should cover scenario of opening it.
