Macro canīt be fired from another worksheet

Hello,

I've got the following macro in my workbook, and it works correct if started from the macro dialog box.
Iīve assigned the macro to a button on another worksheet, but it fails to fire the macro.

Am I right in assuming the macro needs editing (worksheet defining) in order to be fired from another worksheet?
I haven't found any reference to this problem on the net.

Sub Stack()
Dim iRow, iCol, iTargetRow, iMaxRow As Long
iMaxRow = 10000
iTargetRow = 4
Columns(55).Clear
For iCol = 39 To 43
For iRow = 4 To iMaxRow
If Cells(iRow, iCol) > "" Then
Cells(iTargetRow, 55) = Cells(iRow, iCol)
iTargetRow = iTargetRow + 1
Else: Exit For
End If
Next
Next
End Sub

'Change iMaxrow = 500 to the actual maximum row for your data.

'Uncomment the If and End If lines to prevent blank cells from being included in the stack.
'Uncomment the Else line also to skip to the next column when the first blank cell in the current column is encountered.
'Uncommenting all three lines would allow you to use a very high iMaxRow value without extending the macro run time but could lose data if there could be any "internal" blank cells.

Vincent

There are a couple things you could do to improve this.
1. Declare all your variables. For instance, iCol is not declared as anything so if you want it to be of a Long variable you should declare it as such. Hence, your Dim line should look something like:

Dim iRow As Long, iCol As Long, iTargetRow As Long, iMaxRow As Longl

2. Declare the worksheet like:

Dim Ws As Worksheet
Set Ws = ActiveSheet

Your procedure could look something like:

Option Explicit
Sub Stack()
    Dim Ws As Worksheet
    Dim iRow As Long, iCol As Long, iTargetRow As Long, iMaxRow As Long

    Set Ws = ActiveSheet
    iMaxRow = 10000
    iTargetRow = 4
    Ws.Columns(55).Clear
    For iCol = 39 To 43
        For iRow = 4 To iMaxRow
            If Ws.Cells(iRow, iCol) > "" Then
                Ws.Cells(iTargetRow, 55) = Ws.Cells(iRow, iCol)
                iTargetRow = iTargetRow + 1
            Else: Exit For
            End If
        Next iRow
    Next iCol
End Sub

Let me know how it goes.