0

I am trying to move certain worksheets to a new workbook and copy paste all cells as values to remove formula's and links to other worksheets. The problem is my code is taking far too long (I have ID'd part of the code which is responsible for this). Any help in making this more efficient would be much appreciated.

Currently I am using the follow code to copy worksheets to a new workbook. The code is taking 48 seconds to run.

  dim i as variant
  i=array (I have a total of 10-12 worksheets named here)
  thisworkbook.sheets(i).copy

The below code copies and paste the cells as values in the new workbook created. The code is taking approx 140 seconds to run (having checked with debug.print timer code)

 activeworkbook.sheets.select
 cells.select
 selection.copy
 selection.pastespecial paste:-xlpastevalues, operations:-xlnone, skipblanks
 :=false, transpose:=false

I have also tried using the changing the code to use the below to paste values

  dim ws as worksheet
  for each ws in activeworkbook.worksheets
  with ws.usedrange
 .value = .value
  end with
  cells (1,1).select
  net ws

The problem with this is it doesn't work with the worksheets that have a pivot table on them and throws up an error.

Thank you

Jav171
  • 13
  • 1
  • 5
  • 4
    You should really avoid using `.Select` and declare your variables well, described [here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Furthermore, for code optimization you'd rather post your question on [SE Code Review](https://codereview.stackexchange.com/) – JvdV Aug 27 '18 at 12:09
  • If you are needing to "paste values only" on a large amount of data in a row, you definitely should use this method over the PasteSpecial method. make sure the two ranges you are "transferring" between are exactly the same size. use code below Sub TransferValuesOnly() 'PURPOSE: How To Paste Values Only Without Copy/Pasting Dim rng As Range 'Grab Some Data & Store it in a "Range" variable 'Transfer to same spot in another worksheet ( PasteSpecial Values Only) Worksheets("Sheet2").Range("A1").Resize(rng.Rows.Count, rng.Columns.Count).Cells.Value = rng.Cells.Value End Sub – Ahmed amin shahin Aug 27 '18 at 12:28
  • Possible duplicate of [Copy an entire worksheet to a new worksheet in Excel 2010](https://stackoverflow.com/questions/8439657/copy-an-entire-worksheet-to-a-new-worksheet-in-excel-2010) – Samuel Hulla Aug 27 '18 at 12:34
  • I think that you should specify a certain range or loop through all used cells which would be way faster. – William Tong Aug 27 '18 at 12:42
  • @AhmedShahin this type of contribution should be included here as an answer, not a comment – Marcucciboy2 Aug 27 '18 at 13:07

0 Answers0