Excel VBA (sub or function not defined error)

Question

I am working on the following code in VBA excel and i get the compile error "sub or function not defined" The code is meant to copy the cells and paste their transpose at a certain offset. Any help would be highly appreciated.

Code

Sub copy_paste()
ActiveCells.Copy
Offset(-1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True
Offset(3, -1).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Offset(-2, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True
Offset(1, 0).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.ClearContents

End Sub

Thanking in advance, here is the code along with the error:

I'd recommend to use `Otpion Explicit`. Also use the macro recorder only in case you would like to find names of methods and properties. And also [avoid select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). This is a good [site](https://excelmacromastery.com/vba-articles/) for beginners — Storax, May 31 '20 at 19:05

FaneDuru · Answer 1 · 2020-05-31T19:49:11.443

Try this code, please. It avoids selecting, which consumes Excel resources, without any benefit. Offset makes sense only if it references a range:

Sub testCopy()
    Dim sh As Worksheet, rng As Range
    Set sh = ActiveSheet 'use here your sheet
    Set rng = ActiveCell 'use here what range you need
    rng.Copy
    rng.Offset(-1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                :=False, Transpose:=True
    sh.Range(rng.Offset(3, -1), rng.Offset(3, -1).End(xlToRight)).Copy

    rng.Offset(-2, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                :=False, Transpose:=True
    sh.Range(rng.Offset(1, 0), rng.Offset(1, 0).End(xlToRight)).ClearContents
End Sub

score 0 · Answer 2 · answered May 31 '20 at 19:08

0

Use offset in vba like this : Range("A1").Offset(1, 1).Select .

in your code you used offset without refrence range address

answered May 31 '20 at 19:08

Sharif Lotfi

544
6
13

Excel VBA (sub or function not defined error)

2 Answers2