I Bad: VBA Confusion

Question

I have a macro that works manually and with Ctrl + I, but when I put a button, the macro doesn't work.

Sub Duplicar()
'Updateby Extendoffice 20160922
    Dim xRow As Long
    Dim VInSertNum As Variant
    xRow = 26
    Application.ScreenUpdating = False
    Do While (Cells(xRow, "A") <> "")
        VInSertNum = Cells(xRow, "K")
        If ((VInSertNum > 1) And IsNumeric(VInSertNum)) Then
           Range(Cells(xRow, "A"), Cells(xRow, "K")).Copy
           Range(Cells(xRow + 1, "A"), Cells(xRow + VInSertNum - 1, "K")).Select
           Selection.Insert Shift:=xlDown
           xRow = xRow + VInSertNum - 1
        End If
        xRow = xRow + 1
    Loop
    Application.ScreenUpdating = False
End Sub

Welcome to SO. *the Macro doesn't work* is not enough information. Is the button linked to the macro? Are macros enabled in the file? Also, last line of your code should be `Application.ScreenUpdating = True` — Foxfire And Burns And Burns, May 13 '19 at 11:21
Hi. u are not referecing a sheet. are u putting the button on the sheet that u whant to duplicate? — Luis Curado, May 13 '19 at 11:22
@ZéMiguel If you follow the approach in the answer below it will always work no matter on which sheet you put the button. — Pᴇʜ, May 13 '19 at 11:34

score 2 · Answer 1 · edited May 13 '19 at 11:36

You need to reference in which worksheet your Cells() and Range() are. Otherwise, Excel cannot know that and starts guessing which worksheet you mean (and it fails guessing).

So set a variable to a worksheet

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

and use it to reference all Cells(), Range(), Rows(), etc. For example, like

Do While ws.Cells(xRow, "A").Value <> ""

Do this for all of them.

Also see How to avoid using Select in Excel VBA. Instead of using .Select, always use the range directly. For example, instead of:

Range("A1").Select
Selection.Insert

do directly

Range("A1").Insert

Finally your last line should be Application.ScreenUpdating = True, not False.

I Bad: VBA Confusion

1 Answers1