3

I have a workbook with 5 sheets full of UDF (user defined functions).
I need to copy these 5 sheets to a new workbook but I only need the values.
The problem is that when I copy these sheets all cells with UDF became broken with #value on it, because I don't have my macros on this new workbook.

I cannot copy the module, because many users will use this workbook and maybe their Excel will not allow me to manipulate the vba project.

I tried copying and pasting only values between workbook but it doesn't work. I think that is because my sheet have a lot of merged rows and columns. Excel shows an error message. I'll translate from portuguese to english here, maybe Excel doesn't use this exactly workd:

This option requires that merged cells are all the same size.

I don't understand this error. I even tried to do this manually: copy the entire sheet (right click, move and copy), then selected all the used range on my original workbook, ctrl+c, then selected the first cell in the new workbook then ctrl+v and then paste only values. And Excel throws an error. I also tried selecting the entire range on the new workbook before pressing ctrl+v. Same problem.

I don't know what to do. What I am doing now is copying and pasting only values in my original workbook and then copying the entire sheet to a new workbook. The problem is that this action destroy my original workbook, so I have to close it and reopen, what I didn't want to do.

Anyone have any idea on how to proceed?

pnuts
  • 58,317
  • 11
  • 87
  • 139
hend
  • 565
  • 1
  • 6
  • 13

3 Answers3

2

Try these steps

  1. Copy the sheets inside the existing file.
  2. Copy then paste values all info in the newly copied sheet (in the existing file)
  3. Move the newly copied (and newly pasted values) sheet to a new file.
guitarthrower
  • 5,624
  • 3
  • 29
  • 37
  • 1
    God, that is so simple and obvious I can't believe I didn't think about this before! ¬¬' thank you! – hend Aug 22 '14 at 19:15
  • We've all been there! And welcome to StackOverflow. If this did the trick, make sure to accept the answer so that others will know. – guitarthrower Aug 22 '14 at 19:16
  • @hend you can hit the tick though so it turns green! You will gain 2 points for doing this. – whytheq Aug 23 '14 at 20:45
2

Given you asked for code, you can automate guitarthrower's solution as below (where you would change your sheet names in the book to be copied below)

Sub Redone()
Dim WB As Workbook
Dim ws As Worksheet

'runs on ActiveWorkbook
Set WB = ActiveWorkbook
WB.Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")).Copy

'converts formulae to values on new workbook with the 5 specific sheets
For Each ws In ActiveWorkbook.Sheets
    ws.UsedRange.Value = ws.UsedRange.Value
Next

End Sub
Community
  • 1
  • 1
brettdj
  • 54,857
  • 16
  • 114
  • 177
  • no good. when copy happens, it creates the new file, and that becomes ActiveWorkbook, that has no idea of the UDF in the original workbook. – robotik Sep 29 '16 at 11:18
  • @robotik the question specifically asked for the copies to be values only. – brettdj Sep 29 '16 at 14:16
  • after copy, before you turn them to values, they already can't calculate the UDF and give `#NAME?` error – robotik Oct 04 '16 at 11:11
1

Try this:

  • Copy the complete file using SaveAs
  • In the copy of the file go to each of the target sheets.
  • Right-click the top left cell and select copy:

enter image description here

  • Then right-click again and select paste special choosing values only.
  • Now delete all the sheets that you do not want included.
whytheq
  • 34,466
  • 65
  • 172
  • 267
  • You answered almost exactly how I did! But you beat me by a minute and included a picture, so I deleted my answer. :P I suggest, as a best practice, that @hend "save as" first to avoid accidentally saving over his/her file and losing all his/her UDFs. – n8. Aug 22 '14 at 19:20
  • I have a lot of VBA code in my file, a lot of other sheets and many conditional formats. I don't these in my new file. I used guitarthrower suggestion as it makes simple to have just what I want in my new file. But thank you very much! – hend Aug 23 '14 at 19:33