macro to work on every excel sheet vba

Question

Below code works at one sheet named "Tools" but when the button in moved to another sheet it does not. How it should be changed to work regardless of the sheet name or specifically Sheet name "Tools"

Sub NameRangeTop(Optional ByRef rngRange As Range)

If rngRange Is Nothing Then
    Set rngRange = Application.Selection
Else
    rngRange.Select
End If

Dim ActiveRange As Range
Dim NumRows, NumColumns, iCount As Long

Dim CurSheetName As String
CurSheetName = ActiveSheet.Name

Set ActiveRange = Selection.CurrentRegion
ActiveRange.Select
NumRows = ActiveRange.Rows.Count
NumColumns = ActiveRange.Columns.Count

If NumRows = 1 And NumColumns = 1 Then
MsgBox "No active cells in the surrounding area. Try running the macro from a different location", vbCritical, "Local Range Naming"
Exit Sub
End If

If NumRows = 1 Then
    Set ActiveRange = ActiveRange.Resize(2)
    NumRows = 2
End If

For iCount = 1 To NumColumns
ActiveRange.Resize(NumRows - 1).Offset(1, 0).Columns(iCount).Name = CurSheetName & "!" & ActiveRange.Rows(1).Columns(iCount).Value
Next

ActiveRange.Resize(NumRows - 1).Offset(1, 0).Select

End Sub

Thanks

score 0 · Answer 1 · answered Jun 02 '18 at 12:53

The first if statement may give problems if you are passing a range to the sub. Before you can Select rngRange you must be on the sheet that contains that range , so use:

If rngRange Is Nothing Then
    Set rngRange = Application.Selection
Else
    rngRange.Parent.Activate
    rngRange.Select
End If

But in general you don't need to use Select at all, see:

Avoid using Select

You also need to insure that the data on each sheet id consistent with your naming methos.

macro to work on every excel sheet vba

1 Answers1