macro automation error and paste value (excel)

[bluerayz]

I have a macro here which works by going through a folder and extracting individual and a selection of cells. I am getting an automation error when the macro steps into picking a selection of cells to copy. The first senerio works, but any copying senerios after that does not. The first attempt to copy and paste a selection that is working is printing #REF! though; is there someting im missing in my code to call the value instead of the formula? and is there a noticble error in my code that is causing an automation error?

Thanks,

[bluerayz]

i have attached an example which the macro can be ran on, just change the worksheet to CBM Summary1.

[LegareColeman]

Your code has the following statement numerous times:

wbk.Close SaveChanges:=False

Those statements are followed by statements that use wbk. You can't use a workbook after you close it.

[Jazzymoves]

I think you need a paste special to paste values instead of #REF! I looked this up in help. maybe it will help:

newSheet.Range("A1").PasteSpecial Paste:=xlValues

[bluerayz]

Yep I see the problem,

If the values I am copying are based on a formula, how is it possible to copy and paste them as just values instead of references to the formula?

thanks for the help

[LegareColeman]

You must use the PasteSpecial method instead of Paste. Your first Paste would look something like this to paste the value:

wsh.Range("M" & i).PasteSpecial Paste:=xlPasteValues