# Excel VBA - Paste special formats & values to another workbook



## Gram123 (Mar 15, 2001)

I have created a fairly complex Excel template file and I've hit a point of confusion.

With the file, the user can drop a couple of large blocks of data into it from external files, and click a button and the VBA will process them, stripping out unnecessary data etc, moving the data to a sheet which applies lots of formulae, populating a couple of pivot tables and saving.

Performing this processing is the kind of job you set running last thing before you leave work, and deal with the file the following morning. Both of the data files it uses can contain 16 columns and upwards of 50,000 lines of data, and my file includes about 30 formulae fields on one sheet and 10 on the other. Many of these are lookups from 1 sheet to the other. In the last full test, the processing took about 90 mins to run, popped up my msgbox saying it was going to save, and then took nearly 40 mins more.
The resulting file is around 50Mb, and if closed, can take over half an hour to re-open.

I also have a "cleanse" macro which strips most of the data back out, but the last couple of times I've run this on a file full of data, it's killed it, leaving it corrupt / broken.

So far, so horrible.

I've tried to do various things to speed it up:

- Amending many of my formulas, such as stopping the doubling up of VLOOKUPs to hide #N/A errors, converting most vlookups to INDEX-MATCH, etc
- Amending the VBA, including cutting the use of Select / Selection, turning off screen updating, using calculation manual etc
- Tried using xlsb file format to reduce size.
- Ensured the PC's page file is big enough and not running any other applications whilst the file processes.
- And so on...

My VBA knowledge is still pretty limited, and whilst I'm understanding more as I go on, I know the document undoubtedly still has some poor design. And obviously, with that sort of quantity of data, I know it's going to be fairly slow anyway.

Anyway, my current issue is......

When the file is replete with data, I'm thinking maybe I ought to convert all the formulae to values (presumably this will massively speed up operation of the file). However, I would also need to retain (or reapply) all that formulae for the next month's processing. So it seems like I need to Save As the document with a new name, and in this version, copy-paste special the formulae, and in the original document, leave the formulae intact and run the "cleanse" macro (which, after stripping out most of the data, also Saves As a new version of the template, ready for use the subsequent month).

So, can you "Save As", but also keep the original file open (does SaveCopyAs do this?). Can you close one workbook (the one you saved) from within the code, and then continue working on the original (make it Active)?

For the copying & pasting, I think I need to do something like the following to retain formats and column widths as well as pasting as values:

Sub Formula_to_Values() 
Worksheets.Select 
Cells.Select 
With Selection
.Copy
.PasteSpecial Paste:=xlPasteFormats
.PasteSpecial Paste:=xlPasteColumnWidths
.PasteSpecial Paste:=xlPasteValues
End With

ActiveSheet.Select 
Application.CutCopyMode = False

End Sub​
Combined with saving a copy of the file, something like:

Sub Export_Sheets()

Dim ws As Worksheet, wbNew As Workbook

For Each ws In ThisWorkbook.Worksheets
ws.UsedRange.Value = ws.UsedRange.Value
ws.Copy
Set wbNew = ActiveWorkbook
wbNew.SaveCopyAs Filename:= "C:\Users\xyz\Desktop\Sales Reports\Data Files\Sales Report data " & Format(Date, "yyyy-mm-dd") & ".xlsm"
Next ws
Application.ScreenUpdating = True

End Sub​
Sorry this is so rambly. My head's swimming with it all, so I've maybe not explained it very clearly.
I could really do with some kindly (/ masochistic) soul who's up for a challenge to take a look at the document in general, and help me sort out the formulae and VBA to simplify & speed it up...


----------



## Keebellah (Mar 27, 2008)

Hi, I've read (and think I got most of it) but just for my interpretation.
You say you have an Excel file with a large ammount of formulae, vba code etc whihc servers as a template, corect?
Okay, if this assumption is correct that means that this file, apart from all the formulae and all is empty (no data), correct again?
If this is the case what you need to do save it as a template file, it will automatically be save under the templates in Excel.
No macro needed for that.
When you open a template file it will always open as <name of the template file>1, 2 or whichever during a session.
When closing it will always promt for a (new name) thus avoiding you overwriting the template file. Do you think this would work? Have you tried this?
If not, let me know, I use other methods but this is the simplest one and you do not need to select every sheet, you just save as and the filename you want.
Let me know, If i got the time, I'm what you might call a masochist  I love these vba challenges


----------



## Garf13LD (Apr 17, 2012)

Why don't you just "feed" the formula with your macro, and then do a paste special values only.
with 50,000 rows, I don't recommend you store much formula inside.

To know the formula, select a cell with formula and in the immediate window of VBE type
?activecell.formulaR1C1

below is a sample of my code I did recently with about 30,000 rows.

```
Set found = Range(cell, cell.End(xlDown)).Offset(0, 1)
    found.FormulaR1C1 = "=VLOOKUP(RC[-1],Details!C[-1]:C,2,0)"
    found.Copy
    found.PasteSpecial xlPasteValues
    
    Set found = found.Offset(0, 1)
    found.FormulaR1C1 = "=SUMIF(Details!C[-2],RC[-2],Details!C[3])"
    found.Copy
    found.PasteSpecial xlPasteValues
    
    Set found = found.Offset(0, 1)
    found.FormulaR1C1 = "=COUNTIFS(Details!C[-3],RC[-3],Details!C,""Sales / Invoices"")"
    found.Copy
    found.PasteSpecial xlPasteValues
    
    Set found = found.Offset(0, 1)
    found.FormulaR1C1 = "=IF(RC[-1],ROUND(RC[-2]/RC[-1],2),0)"
    found.Copy
    found.PasteSpecial xlPasteValues
```


----------



## Gram123 (Mar 15, 2001)

Hi guys, thanks for your input. I got dragged away to another site to work on another project (new thread coming shortly!).
I will be coming back to this one at some point, though, I think, so I'll resurrect it then.

Cheers again.


----------

