Paste Values on copied sheets

[jlf81]

Hi, I wondered if anyone could help me I am trying to copy some sheets into a new workbook, and then paste them as values (basically leaving me with the originals as they where, and the new copies as just values in a new book) I keep getting runtime error '-2147217848 (80010108)' Method 'Value' of object 'Range; failed.

Anyone have any idea what is going wrong

Thanks in advance

So far I have been using: -

Sheets(Array("Volume Update - Weekly Alloc", "Fixed vs Variable", _
"Alloc Magins - Group", "Annualised Income", "Volume Analytic 52 Week", _
"Stacked Product Fee Graphs", "Volume Analytics Fee & LTV", _
"Volume Analytics LTV £", "Volume Analytics LTV #", "Volume Analytics LTV ATV", _
"Volume Analytics Fee £", "Volume Analytics Fee #", "Volume Analytics Fee ATV", _
"BTL Volume Analytics")).Copy
Sheets("Volume Update - Weekly Alloc").Range("A1:FL600").Value = Sheets("Volume Update - Weekly Alloc").Range("A1:FL600").Value
Sheets("Fixed vs Variable").Range("A1:FL600").Value = Sheets("Fixed vs Variable").Range("A1:FL600").Value
Sheets("Alloc Magins - Group").Range("A1:FL600").Value = Sheets("Alloc Magins - Group").Range("A1:FL600").Value
Sheets("Annualised Income").Range("A1:FL600").Value = Sheets("Annualised Income").Range("A1:FL600").Value
Sheets("Volume Analytic 52 Week").Range("A1:FL600").Value = Sheets("Volume Analytic 52 Week").Range("A1:FL600").Value
Sheets("Stacked Product Fee Graphs").Range("A1:FL600").Value = Sheets("Stacked Product Fee Graphs").Range("A1:FL600").Value
Sheets("Volume Analytics Fee & LTV").Range("A1:FL600").Value = Sheets("Volume Analytics Fee & LTV").Range("A1:FL600").Value
Sheets("Volume Analytics LTV £").Range("A1:FL600").Value = Sheets("Volume Analytics LTV £").Range("A1:FL600").Value
Sheets("Volume Analytics LTV #").Range("A1:FL600").Value = Sheets("Volume Analytics LTV #").Range("A1:FL600").Value
Sheets("Volume Analytics LTV ATV").Range("A1:FL600").Value = Sheets("Volume Analytics LTV ATV").Range("A1:FL600").Value
Sheets("Volume Analytics Fee £").Range("A1:FL600").Value = Sheets("Volume Analytics Fee £").Range("A1:FL600").Value
Sheets("Volume Analytics Fee #").Range("A1:FL600").Value = Sheets("Volume Analytics Fee #").Range("A1:FL600").Value
Sheets("Volume Analytics Fee ATV").Range("A1:FL600").Value = Sheets("Volume Analytics Fee ATV").Range("A1:FL600").Value
Sheets("BTL Volume Analytics").Range("A1:FL600").Value = Sheets("BTL Volume Analytics").Range("A1:FL600").Value

 

[xlbo]

This may be easier - set an object as a placeholder for the new workbook that is generated, then copy the sheets into that:

Sub CopyandPaste()
Dim NewBk as workbook, sht as worksheet

[b]Set NewBk = workbooks.add[/b]

Thisworkbook.Sheets(Array("Volume Update - Weekly Alloc", "Fixed vs Variable", _
        "Alloc Magins - Group", "Annualised Income", "Volume Analytic 52 Week", _
        "Stacked Product Fee Graphs", "Volume Analytics Fee & LTV", _
        "Volume Analytics LTV £", "Volume Analytics LTV #", "Volume Analytics LTV ATV", _
        "Volume Analytics Fee £", "Volume Analytics Fee #", "Volume Analytics Fee ATV", _
        "BTL Volume Analytics")).Copy [b]Before:=NewBk.sheets(1)[/b]

For each sht in NewBk.worksheets

 sht.cells.copy
 sht.cells.pastespecial xlpastevalues
 application.cutcopymode = false
next

set NewBk = nothing

End sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[jlf81]

Thanks, I will give that a go Monday when I am back in work. I knew I jmust be missing something

Thanks

Josh

 

[jlf81]

I have tried it this morning and it looks to be working perfectly.

Thanks again

Josh