Paste Method Of Worksheet Class Failed for formulas pasted from separate document

Question

I am new to Macros/VBA; I run regular analysis on a constantly updating spreadsheet and wanted to practice developing macros for a process that is very repetitive. I've gone through to record a Macro and I'm getting the Paste Method error. VBA code is below. I am copying formulas from another document; can I embed the formulas directly in the VBA code to avoid the paste error altogether?

Sub SLA()
'
' SLA Macro
'

'
    Range("Table_Tracker[[#Headers],[Docs]]").Select
    Selection.Copy
    Range("Table_Tracker[#All]").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("N:R").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "m/d/yyyy"
    Columns("S:S").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "1 Day SLA"
    Columns("T:T").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("T1").Select
    ActiveCell.FormulaR1C1 = "3 Day SLA"
    Columns("U:U").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.FormulaR1C1 = "Prepare EDD"
    Columns("V:V").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.FormulaR1C1 = "Analyze EDD"
    Columns("W:W").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("W1").Select
    ActiveCell.FormulaR1C1 = "Case Completion"
    Range("A1").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$AK$1030"), , xlYes).Name _
        = "Table6"
    Range("Table6[#All]").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    Range("S2").Select
    ActiveSheet.Paste
    Range("T2").Select
    ActiveSheet.Paste
    Range("U2").Select
    ActiveSheet.Paste
    Range("V2").Select
    ActiveSheet.Paste
    Range("W2").Select
    ActiveSheet.Paste

End SubSub SLA()

The error appears in the line after Range("S2")

[Obligatory link for macro-recorder code](https://stackoverflow.com/q/10714251/1188513) — Mathieu Guindon, Dec 13 '17 at 18:33

SilentRevolution · Answer 1 · 2017-12-13T18:34:07.583

0

Yes, you can embed formulae in VBA by using the Range.Formula property explained here.

For example:

ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Formula = "=SUM(A2:A101)"

The string can be any formula, and can consist of various parts to include VBA variables.

For example:

Sub writeFormula()
    Dim i As Long

    With ThisWorkbook.Worksheets("Sheet1")
        For i = 1 To 100
            .Cells(i, 1).Formula = "=SUM(" & .Range(.Cells(i, 2), .Cells(i, 20)).Address & ")"
        Next
    End With

End Sub

This writes the formula =SUM($B$1:$T$1) in cell A1 and =SUM($B$2:$T$2) in A2 and so on all the way down to =SUM($B$100:$T$100) in cell A100.

edited Dec 13 '17 at 18:34

answered Dec 13 '17 at 18:27

SilentRevolution

1,495
1
16
31

Thanks for this; how would I incorporate your code in my existing VBA as the Macro I'm trying to create includes inserting new columns, new column headers and there are 5 different formulas being inserted – premiumcopypaper Dec 13 '17 at 18:33
With the data provided, it is quite hard to tell how to do this exactly. Hence the general explanation. Also because it is mostly a recorded macro it is a bit harder to decipher what actually happens. – SilentRevolution Dec 13 '17 at 18:40

Paste Method Of Worksheet Class Failed for formulas pasted from separate document

1 Answers1