Hi guys
I am using the following code to redefine all the named ranges every time a worksheet changes. Is it possible to restrict this to happen only after another macro has finished its operation - the problem is that i have a macro that flicks between sheets regularly to copy and paste data into order. I need to be able to restrict the sheetSelectionChange to only happen after my other macro has completed
Thanks heaps
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Call AddNamedRanges(ThisWorkbook.Name)
End Sub
The code below searches for top and bottom borders for the used range in the worksheet it is called. when it knows the row #'s for the range it extends the selection to Row H then names the range according to the value found in the top left cell of the selected range
'----------------------------------------------------------------------------------
Sub AddNamedRanges(WbNm As String)
Dim Cll As Range
Dim RngStart As Range
Workbooks(WbNm).Activate
For Each Cll In ActiveSheet.UsedRange.Columns(1).Cells
If Cll.Borders(xlEdgeTop).LineStyle = xlContinuous Then
Set RngStart = Cll
ElseIf Cll.Borders(xlEdgeBottom).LineStyle = xlContinuous And Not RngStart Is Nothing Then
ActiveWorkbook.Names.Add Name:=Replace(RngStart.Value, " ", ""), RefersTo:=ActiveSheet.Range(RngStart, Cll.Offset(0, 7))
Set RngStart = Nothing
End If
Next Cll
End Sub
Bookmarks