Mix paste values and paste formulas in a new Workbook.

[Rickinnocal]

Good day, I have a Command button on a workbook that creates a new workbook and copies data from the old workbook to it. The new workbook, which is based on a workbook supplied to me by the client, is then sent to my client.

Currently, all the cells in the new workbook are populated by :

    wsPaste.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    wsPaste.Range("A1").PasteSpecial xlPasteFormats
    wsPaste.Range("A1").PasteSpecial xlPasteColumnWidths

However, my client has complained. Some of the cells in his workbook contain simple formulae along the lines of "=A3*A4" but in the version I'm sending him that cell contains the result of A3*A4, not the formula, so if he makes any changes to my data, they don't recalculate.

How can I change what I'm doing so that the cells (hundreds of them) that I want to paste as just values do paste that way, but certain cells - just a couple of dozen or so - remain as formulas?

Thanks in advance.

 

[Joe4]

"PasteSpecial xlPasteValuesAndNumberFormats" turns formulas into hard-coded values.

Why not just do a simple copy/paste of the whole sheet at once, which should copy/paste over everything "as-is" (including formatting and spacing)?

 

[Rickinnocal]

"PasteSpecial xlPasteValuesAndNumberFormats" turns formulas into hard-coded values.

Why not just do a simple copy/paste of the whole sheet at once, which should copy/paste over everything "as-is" (including formatting and spacing)?

Hi, the reason I can't do that is that then the cells whose values I need will contain formulae that refer to sheets in a different workbook.

 

[Joe4]

Hi, the reason I can't do that is that then the cells whose values I need will contain formulae that refer to sheets in a different workbook.

They shouldn't. That is not the experience that I have.

Open up to new workbooks, Book1 and Book2.
On Book1, add some data simple formulas, and formatting.

Then run this VBA code to copy that to Sheet2, and you will see that none of the formulas copied into Book2 reference Book1.

Sub TestMacro()

    Windows("Book1").Activate
    Cells.Copy
    Windows("Book2").Activate
    ActiveSheet.Paste
    
End Sub

​

​

​

​

 

[Rickinnocal]

When I tried that, I got an error from the Macro "Subscript out of Range".

If I manually copy and paste, I get this...

As you see, the contents of cell D5 in TestPasteBook are "=[TestCopyBook.xlsm]Sheet1!D5+[TestCopyBook.xlsm]Sheet2!D5" when I want it to just be "40".

Cell F5 (which I've added a leading apostrophe to to show the contents) just shows a simple formula, which is what I want, but cells that have formulae across multiple sheets copy over with their references. These are the cells that I want to copy as values only, so that they don't try to reference sheets that the client won't get.

 

[Joe4]

When I tried that, I got an error from the Macro "Subscript out of Range".

That is because you have different file names!
You need to change "Book1" and "Book2" to the name of your actual files.
You may also need to include the file extension in with the file name, as well as indicate sheet names, if you have multiple, i.e.

Sub TestMacro()

    Windows("TestCopyBook.xlsm").Activate
    Sheets("Sheet3:").Select
    Cells.Copy
    Windows("TestCopyBook.xlsm").Activate
    Sheets("Sheet1").Select
    ActiveSheet.Paste
    
End Sub

However, I see a flaw in your process. In your original file, there are 3 sheets, and your formula is referencing the other 2 sheets.
In your destination file, there is only one sheet. So how is that formula you are copying over supposed to work if there are no other sheets to reference?

 

[Joe4]

In re-reading your posts, I think I am a bit confused as to what you are trying to do.
I thought your issue was that it was copying the values and not the formulas, but from your last posts, it looks like you want the values are not the formula.
If you copy over the values, the formulas won't recalculate (as there are no formulas to recalculate).

So I am unclear as to exactly what you are trying to do here.
Are you saying that you want the formula that reference other sheets to be hard-coded, but the formulas that only pertain to cells on that sheet to stay as formulas?
If so, that is going to be very tricky. Especially if you want to maintain formatting.

 

[Rickinnocal]

That is because you have different file names!
You need to change "Book1" and "Book2" to the name of your actual files.
You may also need to include the file extension in with the file name....

I did change the file names, but not the extensions, I'll try that.

 

[Rickinnocal]

Are you saying that you want the formula that reference other sheets to be hard-coded, but the formulas that only pertain to cells on that sheet to stay as formulas?
If so, that is going to be very tricky. Especially if you want to maintain formatting.

Yes, exactly. The data that is copied over is the result of calculations across multiple sheets in the source workbook. The destination workbook does not have access to the source worksheets, so that data needs to be copied as values.
The formulae on the destination sheet, however, need to stay as formulae, because the client may over type my data based on lab results, and the calculations need to be performed on the new data.

 

[Joe4]

Yes, exactly. The data that is copied over is the result of calculations across multiple sheets in the source workbook. The destination workbook does not have access to the source worksheets, so that data needs to be copied as values.
The formulae on the destination sheet, however, need to stay as formulae, because the client may over type my data based on lab results, and the calculations need to be performed on the new data.

I cannot think of a good way to do that.

The only way I can think of is to link through each cell, one at a time, and try to determine which ones access other sheets, and which ones don't, and the either copy the formula or value for each one individually.