Копирование формул в макросе excel

I have an existing VBA code that copies an Excel worksheet from my source workbook (Sourcewb) into a new destination workbook (Destwb) but pastes values only. I need a specific range (D31:E38) in the Destwb to include the formulas from the source workbook. I found this code:

Range("A1:I1105").Copy Sheets("Sheet2").Range("B2")

On this site (another question) that seems related but don’t know how to modify it to work in my application. I have added a comment line » ‘Insert total formulas in Calc sheet» for where I think the additional code would go. Here is my existing code:

Set Sourcewb = ActiveWorkbook

'Copy the sheet to a new workbook
Sheets("Calculation").Copy
Set Destwb = ActiveWorkbook

'Determine the Excel version and file extension/format
With Destwb
    If Val(Application.Version) < 12 Then
        'You use Excel 97-2003
        FileExtStr = ".xls": FileFormatNum = -4143
    Else
        'You use Excel 2007-2013
            FileExtStr = ".xlsx": FileFormatNum = 51
        End If
End With

'Change all cells in the worksheet to values if you want
With Destwb.Sheets(1).UsedRange
    Application.CutCopyMode = False
    ActiveSheet.Unprotect
        .Cells.Copy
        .Cells.PasteSpecial xlPasteValues
        .Cells(1).Select
End With
Application.CutCopyMode = False

'Insert total formulas in Calc sheet

'Save the new workbook and close it
TempFilePath = Sheets("Calculation").Range("L4").Value
TempFileName = Range("L3").Value

With Destwb
    .SaveAs TempFilePath & "" & TempFileName & FileExtStr, FileFormat:=FileFormatNum
    .Close SaveChanges:=True
End With

MsgBox "You can find the new file in " & TempFilePath

You can try with: ActiveSheet.PasteSpecial Paste:=xlFormulas

ActiveSheet.Unprotect
...
    .Cells.Copy
    .Cells.PasteSpecial xlPasteValues
    .Cells.PasteSpecial xlFormulas
    .Cells(1).Select
End With

• Using Find and Replace

|

• Filling a Column or Row

|

• Pasting a Formula into Multiple Cells

|

• Using Relative and Absolute Cell References

|

• Video

|

• Q&A

|

• Tips

|

• Warnings

1. 

   1

   Open your workbook in Excel. Sometimes, you have a large spreadsheet full of formulas, and you want to copy them exactly. Changing everything to absolute cell references would be tedious, especially if you just want to change them back again afterward. Use this method to quickly move formulas with relative cell references elsewhere without changing the references.^[1]
   In our example spreadsheet, we want to copy the formulas from column C to column D without changing anything.

   Example Spreadsheet

   	Column A	Column B	Column C	Column D
   row 1	944	Frogs	=A1/2
   row 2	636	Toads	=A2/2
   row 3	712	Newts	=A3/2
   row 4	690	Snakes	=A4/2

   • If you’re just trying to copy the formula in a single cell, skip to the last step («Try alternate methods») in this section.

2. 

   2

   Press Ctrl+H to open the Find window. The shortcut is the same on Windows and macOS.

   Advertisement

3. 

   3

   Find and replace «=» with another character. Type «=» into the «Find what» field, and then type a different character into the «Replace with» box. Click Replace All to turn all formulas (which always begin with an equal’s sign) into text strings beginning with some other character. Always use a character that you have not used in your spreadsheet. For example, replace it with # or &, or a longer string of characters, such as ##&.

   Example Spreadsheet

   	Column A	Column B	Column C	Column D
   row 1	944	Frogs	##&A1/2
   row 2	636	Toads	##&A2/2
   row 3	712	Newts	##&A3/2
   row 4	690	Snakes	##&A4/2

   • Do not use the characters * or ?, since these will make later steps more difficult.

4. 

   4

   Copy and paste the cells. Highlight the cells you want to copy, and then press Ctrl + C (PC) or Cmd + C (Mac) to copy them. Then, select the cells you want to paste into, and press Ctrl + V (PC) or Cmd + V (Mac) to paste. Since they are no longer interpreted as formulas, they will be copied exactly.

   Example Spreadsheet

   	Column A	Column B	Column C	Column D
   row 1	944	Frogs	##&A1/2	##&A1/2
   row 2	636	Toads	##&A2/2	##&A2/2
   row 3	712	Newts	##&A3/2	##&A3/2
   row 4	690	Snakes	##&A4/2	##&A4/2

5. 

   5

   Use Find & Replace again to reverse the change. Now that you have the formulas where you want them, use «Replace All» again to reverse your change. In our example, we’ll look for the character string «##&» and replace it with «=» again, so those cells become formulas once again. You can now continue editing your spreadsheet as usual:

   Example Spreadsheet

   	Column A	Column B	Column C	Column D
   row 1	944	Frogs	=A1/2	=A1/2
   row 2	636	Toads	=A2/2	=A2/2
   row 3	712	Newts	=A3/2	=A3/2
   row 4	690	Snakes	=A4/2	=A4/2

6. 

   6

   Try alternate methods. If the method described above doesn’t work for some reason, or if you are worried about accidentally changing other cell contents with the «Replace all» option, there are a couple other things you can try:

   • To copy a single cell’s formula without changing references, select the cell, then copy the formula shown in the formula bar near the top of the window (not in the cell itself). Press Esc to close the formula bar, then paste the formula wherever you need it.
   • Press Ctrl and ` (usually on the same key as ~) to put the spreadsheet in formula view mode. Copy the formulas and paste them into a text editor such as Notepad or TextEdit. Copy them again, then paste them back into the spreadsheet at the desired location. Then, press Ctrl and ` again to switch back to regular viewing mode.

Advertisement

1. 

   1

   Type a formula into a blank cell. Excel makes it easy to propagate a formula down a column or across a row by «filling» the cells. As with any formula, start with an = sign, then use whichever functions or arithmetic you’d like. We’ll use a simple example spreadsheet, and add column A and column B together. Press Enter or Return to calculate the formula.

   Example Spreadsheet

   	Column A	Column B	Column C
   row 1	10	9	19
   row 2	20	8
   row 3	30	7
   row 4	40	6

2. 

   2

   Click the lower right corner of the cell with the formula you want to copy. The cursor will become a bold + sign.

3. 

   3

   Click and drag the cursor across the column or row you’re copying to. The formula you entered will automatically be entered into the cells you’ve highlighted. Relative cell references will automatically update to refer to the cell in the same relative position rather than stay exactly the same. Here’s our example spreadsheet, showing the formulas used and the results displayed:

   Example Spreadsheet

   	Column A	Column B	Column C
   row 1	10	9	=A1+B1
   row 2	20	8	=A2+B2
   row 3	30	7	=A3+B3
   row 4	40	6	=A4+B4

   Example Spreadsheet

   	Column A	Column B	Column C
   row 1	10	9	19
   row 2	20	8	28
   row 3	30	7	37
   row 4	40	6	46

   • You can also double-click the plus sign to fill the entire column instead of dragging. Excel will stop filling out the column if it sees an empty cell. If the reference data contains a gap, you will have to repeat this step to fill out the column below the gap.
   • Another way to fill the entire column with the same formula is to select the cells directly below the one containing the formula and then press Ctrl + D.^[2]
     

Advertisement

1. 

   1

   Type the formula into one cell. As with any formula, start with an = sign, then use whichever functions or arithmetic you’d like. We’ll use a simple example spreadsheet, and add column A and column B together. When you press Enter or Return, the formula will calculate.

   Example Spreadsheet

   	Column A	Column B	Column C
   row 1	10	9	19
   row 2	20	8
   row 3	30	7
   row 4	40	6

2. 

   2

   Select the cell and press Ctrl+C (PC) or ⌘ Command+C (Mac). This copies the formula to your clipboard.

3. 

   3

   Select the cells you want to copy the formula to. Click on one and drag up or down using your mouse or the arrow keys. Unlike with the column or row fill method, the cells you are copying the formula to do not need to be adjacent to the cell you are copying from. You can hold down the Control key while selecting to copy non-adjacent cells and ranges.

4. 

   4

   Press Ctrl+V (PC) or ⌘ Command+V (Mac) to paste. The formulas now appear in the selected cells.

Advertisement

1. 

   1

   Use a relative cell reference in a formula. In an Excel formula, a «cell reference» is the address a cell. You can type these in manually, or click on the cell you wish to use while you are entering a formula. For example, the following spreadsheet has a formula that references cell A2:

   Relative References

   	Column A	Column B	Column C
   row 2	50	7	=A2*2
   row 3	100
   row 4	200
   row 5	400

2. 

   2

   Understand why they’re called relative references. In an Excel formula, a relative reference uses the relative position of a cell address. In our example, C2 has the formula “=A2”, which is a relative reference to the value two cells to the left. If you copy the formula into C4, then it will still refer to two cells to the left, now showing “=A4”.

   Relative References

   	Column A	Column B	Column C
   row 2	50	7	=A2*2
   row 3	100
   row 4	200		=A4*2
   row 5	400

   • This works for cells outside of the same row and column as well. If you copied the same formula from cell C1 into cell D6 (not shown), Excel would change the reference «A2» to a cell one column to the right (C→D) and 5 rows below (2→7), or «B7».

3. 

   3

   Use an absolute reference instead. Let’s say you don’t want Excel to automatically change your formula. Instead of using a relative cell reference, you can make it absolute by adding a $ symbol in front of the column or row that you want to keep the same, no matter where you copy the formula too.^[3]
   Here are a few example spreadsheets, showing the original formula in larger, bold text, and the result when you copy-paste it to other cells:

   • Relative Column, Absolute Row (B$3): The formula has an absolute reference to row 3, so it always refers to row 3:

     	Column A	Column B	Column C
     row 1	50	7	=B$3
     row 2	100	=A$3	=B$3
     row 3	200	=A$3	=B$3
     row 4	400	=A$3	=B$3

   • Absolute Column, Relative Row ($B1): The formula has an absolute reference to column B, so it always refers to column B.

     	Column A	Column B	Column C
     row 1	50	7	=$B1
     row 2	100	=$B2	=$B2
     row 3	200	=$B3	=$B3
     row 4	400	=$B4	=$B4

   • Absolute Column & Row ($B$1): The formula has an absolute reference to column B of row 1, so it always refers to column B of row 1.

     	Column A	Column B	Column C
     row 1	50	7	=$B$1
     row 2	100	=$B$1	=$B$1
     row 3	200	=$B$1	=$B$1
     row 4	400	=$B$1	=$B$1

4. 

   4

   Use the F4 key to switch between absolute and relative. Highlight a cell reference in a formula by clicking it and press F4 to automatically add or remove $ symbols. Keep pressing F4 until the absolute or relative references you’d like are selected, then press Enter or Return.

Advertisement

Advertisement

• If you copy a formula to a new cell and see a green triangle, Excel has detected a possible error. Examine the formula carefully to see if anything went wrong.^[4]
  

• If you accidentally did replace the = character with ? or * in the «copying a formula exactly» method, searching for «?» or «*» will not give you the results you expect. Correct this by searching for «~?» or for «~*» instead.^[5]
  

• Select a cell and press Ctrl‘ (apostrophe) to fill it with the formula directly above it.

• Different versions of Excel may not show exactly the same screenshots in the same ways as are displayed here.

Did this summary help you?