Clever Geek Handbook

Copy sheets from udf to new book

I have a workbook with 5 sheets filled with UDFs (Custom Functions).
I need to copy these 5 sheets into a new workbook, but I only want the values.
The problem is that when I copy these sheets, all cells with UDF are split to #value on it because I don't have my macros in 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 vba project

.

I tried to copy and paste only values ​​between the workbook, but it doesn't work. I think this is because my sheet has a lot of concatenated rows and columns. Excel shows an error message. I will translate from Portuguese to English here, maybe Excel doesn't use it exactly like this:

This option requires the merged cells to be the same size.

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

I do not know what to do. What I am doing now is copying and pasting just the values ​​into my original workbook, and then copying the entire sheet into the new workbook. The problem is that this action destroys my original workbook, so I have to close it and reopen it, which I didn't want to do.

Does anyone know how to proceed?

+3


source to share


3 answers


Try the following steps.



  • Copy sheets inside an existing file.
  • Copy then paste the values ​​of all information on the newly copied sheet (in the existing file)
  • Move the sheet of newly copied (and newly pasted values) to the new file.
+2


source


Given that you requested the code, you can automate guitarthrower's solution like below (where you have to change the sheet names in the workbook 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
+2


source


Try the following:

  • Copy the complete file using SaveAs
  • In the copy of the file, navigate to each target sheet.
  • Right click the top left cell and select copy:

enter image description here

  • Then right click and select only special values ​​to select.
  • Now remove any sheets that you don't want to include.
+1


source






More articles:


All Articles