Past with link and Transpose

Question

I made some codes below, is to past with link and transpose in worksheet. But when I run second procedure('WT_Transpose'), an error occured: Object variable not set (Error 91).

If you run each procedure in order in the editor it worked normally. But why can't recognize the rngMulti object when I run it using shortcut keys in the worksheet? Is there any way to share assigned variables between procedures?

Option Explicit
Dim rngMulti As Range

Private Sub Ctrl_Copy()
'Shortcut: Ctrl+c

On Error GoTo Err_Step
    
    Selection.Copy
    Set rngMulti = Selection
    
Err_Step:

End Sub

Private Sub WL_Transpose()
'Shortcut: Alt+Shift+v

Dim r, c As Long
Dim xr, xc As Long
Dim i, j As Long
Dim text As String
Dim xcell As Range

r = rngMulti.Rows.Count
c = rngMulti.Columns.Count
xr = c
xc = r

ReDim arr(1 To xr, 1 To xc)

    For xr = 1 To c
    For xc = 1 To r

    arr(xr, xc) = Split(rngMulti.Cells(xc, xr).Address(external:=True), "]")(1)

    Next xc
    Next xr

ActiveCell.Resize(xr - 1, xc - 1).Value = arr
ActiveCell.Resize(xr - 1, xc - 1).Select

For Each xcell In Selection

xcell.Formula = "='" & xcell.Formula

Next xcell

End Sub

Which line has the error? It's possible something happened to clear your global variable `rngMulti ` between the copy and paste calls. https://stackoverflow.com/questions/3815547/what-is-the-difference-between-dim-global-public-and-private-as-modular-field — Tim Williams, Aug 14 '23 at 15:59
@TimWilliams Those are in the same module, and error occured : r = rngMulti.Rows.Count. The variable 'rngMulti ' is not recognized in the second procedure. — MIDEUM CHO, Aug 14 '23 at 23:14
Are you certain this line is running? `Set rngMulti = Selection` — Tim Williams, Aug 14 '23 at 23:29
I modified the code by declaring runMulti as a module level variable(public), it's working properly. Copied range with Ctrl+c, could paste with links and tranposed. — MIDEUM CHO, Aug 16 '23 at 02:17
Sorry, I confused. You're right, it was already a module-level variable.. I think it was related with the module in the add-in. Just delete it, and It's working.. — MIDEUM CHO, Aug 16 '23 at 03:41

score 1 · Answer 1 · answered Aug 14 '23 at 15:48

1

Change

Dim rngMulti As Range

For this

Public rngMulti As Range

I think it will solve your problem

answered Aug 14 '23 at 15:48

Marcos Cassaro

684
5
2

I think those procedures are in the same module, so the level of variable is not the point. Actually, another error occured, and I think it was related with the module that was in the addin made by someone else. So I deleted this add, and it's working properly. Thank you. – MIDEUM CHO Aug 16 '23 at 03:44

Past with link and Transpose

1 Answers1