wimgielis.com

Smart copy/paste operations

• Jan. 16, 2014

	Example files with this article:
	Smart copy/paste operations

Introduction

TM1 models very often use Excel spreadsheets or websheets as reports or data input collection at the front-end. Both types of sheets are just Excel spreadsheets that we give a TM1 flavor by adding such formulas like DBRW or SUBNM. Spreadsheets can be started from scratch or can be based upon a slice of a cube view. When we use DBRW formulas, I advise you to generalize the formula such that it can be easily copy/pasted and filled down/right. Many cells in the template have the same DBRW formula - its references only point to different cells with a judicious use of Excel absolute and relative cell references (the $ signs in cell references, hit F4 to toggle between states). This is all pretty known stuff for the target audience of these TM1 articles.

But… when we set up such reports we use a lot of Excel functionality, which might make copy/paste or filldown a nightmare or even impossible. Think about hidden rows or columns. Or blocks of formulas separated by blocks of blank cells or blocks of fixed text/constants. We do not want to copy paste over these cells but neither do we want to manually select areas where to copy/paste. Don’t you hate it when you select a lot of contiguous cell ranges holding down the Ctrl key, and the last but one goes wrong. Darn!

Excel functionality

This is where 2 of my favourite Excel tools show up: Special cells and copy/paste special. While copy/paste special operations are generally known among Excel users, Special cells are not. Believe me.

Copy/paste special

Copy/paste cells is standard practice in Excel spreadsheet usage. Excel will copy the contents of a cell (a constant, a formula, data validation, a cell comment, …), all formatting: basically everything. Which is not what you want from time to time. Your manager might not have TM1 Perspectives installed so a report with hundreds of DBRW formulas will not be useful. If you copy/paste the report as values, anyone can have a look at it.

The screenshot shows that you can copy/paste cells and all of their properties. But you can also ONLY paste the formula you happened to copy: for instance only pasting a DBRW formula over 1000 cells without pasting the formats. Did you know that you could paste only validation from a range of cells to other cells? Or cell comments? And have you ever set the column width of 100 columns in 1 operation? Or change fixed budget figures for next year by adding a 2% increase in a big number of cells? (use the Multiply option for that). Or changed textnumbers to real numbers? (textnumbers are numbers seen as text and no real numbers; copy a blank cell or a cell containing a real 0 and paste special with the option of Add). Lastly, Copy/Paste Special/Transpose IS known by a lot of Excel users (luckily). To sum up, this is all useful stuff to speed up every day Excel work. No VBA-code is needed.

Here is a nifty trick from your Excel MVP: Copy/Paste Special/Values can also be done by dragging the source cell(s) to the destination cell(s), with the RIGHT mouse button pressed and starting from a border of the source cell(s) area. Release when you reach the destination cell and choose "Copy Here as Values Only".

Special cells

It gets even more interesting and more time saving tricks are coming on. Using the dialog screen above, we are able to select for example all cells with a numeric value as opposed to text values. Being a hard value or the result of a formula? Which cells contain data validation? Where are the cells with errors in let’s say a table of 10000 rows by 50 columns? (an autofilter could do this too but it will be more tedious). What about pasting in only the visible cells skipping hidden rows and columns? Or only pasting in empty cells? Lastly, if you paste the contents of a webpage in Excel, oftentimes a lot of web objects survive the copy/paste action. By choosing Objects as special cells we can select them all at once, press the Delete button and they are gone! (watch out not to select too much, or limit the area on which the Special cells method operates).

Combining the tools

So why not combining these 2 useful tools? The logic is as follows:

1. select the cell to be copied, for example a cell with a DBRW formula
2. hit Ctrl + C
3. select a potentially very large range of cells
4. hit F5 or Ctrl + G to bring up the Go To dialog screen
5. select Special… in the lower left corner
6. choose the cells containing Formulas and Numbers (uncheck other options) and choose OK
7. in one of the cells that is the result of the above search action, do a context click with the right mouse button and choose Paste Special (or equivalent ways using the menu or ribbon)
8. choose to paste only the Formulas
9. you are done and saved yourself potentially a lot of effort

An example

A practical example of a report is shown below.

Whenever you need to copy/paste a DBRW formula to all cells, it will take quite some time because of the hidden rows and columns and the empty cells. They should not receive a formula! You can simply:

1. copy cell F69
2. select the range F69:S124
3. go to the visible cells in the Special cells dialog (shortcut key is Alt + ;)
4. again the Special cells dialog but now for the formula cells - leave the previous selection untouched
5. and paste only the formulas by using the Paste special dialog

Done in half a minute or less! You save even more time when cells with constants or text entries appear inbetween numeric cells/columns. Take this trick with you in all your Excel work, not only TM1 work.

A VBA approach

What can be done manually can also be automated using VBA-code. Here is one of my favourite tools to speed up common Excel copy/paste operations. Feel free to digress. Usage: select a non-empty cell to be copied and run the code. The code will paste formula cells over only formula cells, cells with constants over only cells with constants, the same holds true for cells containing text, a number, a logical value or an error (only paste likewise). You can indicate to paste in hidden cells yes or no, paste the formatting yes or no, column widths yes or no. And so on. Please take a seat and play around with it. As always, the same code can be downloaded in the Excel file on top of the page.

Sub SmartCopyPaste()
' Wim Gielis
' https://www.wimgielis.com
'''''
' VBA code to copy paste in a smart way
' 02/24/12
'''''

    Dim rng1 As Range, rng2 As Range, rngDestination As Range
    Dim bLimitDestinationCells As Boolean
    Dim lTargetType As Long
    Dim lTargetPasteType As Long
    Dim lTargetValue As Long

    On Error GoTo EscapeSub

    Set rng1 = Selection.Cells(1)

    If Not IsEmpty(rng1) Then

        Set rng2 = Application.InputBox("Please select the greater area containing the destination cells", _
		"Destination cells", Selection.CurrentRegion.Address(0, 0), Type:=8)

        bLimitDestinationCells = MsgBox("Do you want to limit the destination cells to match the source cell contents", _
		vbYesNo, "Limit destination cells") = vbYes

        If rng2.Count Then

            'check the source cell, and determine what destination cells and pastetype method we need
            Select Case True
            Case fCellHasValidation(rng1) And Not rng1.HasFormula    'copy the validation of the cell to cells with a constant
                lTargetType = xlCellTypeConstants    ' = 2
                lTargetPasteType = xlPasteValidation    ' = 6

            Case rng1.HasFormula   'copy a formula to cells with a formula
                lTargetType = xlCellTypeFormulas    ' = -4123
                lTargetPasteType = xlPasteFormulas    ' = -4123

            Case rng1.Formula = rng1.Value    'copy a constant to cells with a constant
                lTargetType = xlCellTypeConstants    ' = 2
                lTargetPasteType = xlValues    ' = -4163

            End Select

            lTargetValue = Evaluate("=TYPE(" & rng1.Address & ")")

            'paste special
            If lTargetValue < 64 Then    'copy cells with formulas, constants, logical values and error values - skip arrays

                Set rngDestination = rng2

                If bLimitDestinationCells Then Set rngDestination = rngDestination.SpecialCells(lTargetType, lTargetValue)

                If fHiddenCells(rng2) And MsgBox("Paste also in hidden cells?", vbYesNo, "Visible cells") = vbYes Then _
			Set rng2 = rng2.SpecialCells(xlCellTypeVisible)    ' = 12

                'do the copy/paste operation(s)
                rng1.Copy
                rngDestination.PasteSpecial lTargetPasteType

                'transfer the cell value when we copy/paste data validation ?
                If rng1.Validation.Value Then
                    If bLimitDestinationCells And lTargetType = xlCellTypeConstants And lTargetPasteType = xlPasteValidation Then
                        If MsgBox("Should the destination cells match the value of the source cell?", vbYesNo, _
				"Data validation cell value match") = vbYes Then
					rngDestination.PasteSpecial xlValues
                        End If
                    End If
                End If

                'do we want to have the same formatting?
                If MsgBox("Paste the formatting?", vbYesNo, "Formatting") = vbYes Then _
			rngDestination.PasteSpecial xlFormats    ' = -4122

                'do we want to have the same column widths?
                If MsgBox("Paste the column widths?", vbYesNo, "Column width") = vbYes Then _
			rngDestination.PasteSpecial xlPasteColumnWidths    ' = 8

            End If

        End If

    End If

EscapeSub:
    Application.CutCopyMode = False

End Sub

Private Function fCellHasValidation(rng As Range)
    On Error Resume Next
    fCellHasValidation = rng.SpecialCells(xlCellTypeSameValidation).Count > 0
    On Error GoTo 0

End Function

Private Function fHiddenCells(rng As Range)

    On Error Resume Next
    fHiddenCells = rng.Count > rng.SpecialCells(xlCellTypeVisible).Count
    On Error GoTo 0

End Function

I hope (and almost assume) that I have saved you quite some valuable time with the tricks above.

• « Previous TM1 article
• Next TM1 article »