Results 1 to 8 of 8

Only perform macro if a cell from a specific range is selected (SOLVED)

Threaded View

  1. #1
    Registered User
    Join Date
    03-08-2011
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2007
    Posts
    34

    Only perform macro if a cell from a specific range is selected (SOLVED)

    Hello,

    I have 2 problems with a specific macro I created. The macro creates a new worksheet that is a copy of my template sheet and renames it to the value in the original cell on my "Masterlist". My problem is I only want this to happen when a new item is entered in range("b11:b"). Right now it happens when you enter new text in any cell in the "Masterlist" sheet.

    My second issue is when I delete an entry on the "Masterlist" it also creates a new copy and then renames it. How could I alter my code to avoid this.

    Thank you for any help.

    Dave


    Private Sub Worksheet_Change(ByVal Target As Range)
        
        Application.ScreenUpdating = False
        Dim wsNew As Worksheet
        Dim MySheetName As String
        If Target.Cells.Count > 1 Then Exit Sub
        MySheetName = Target.Value
        Sheets("Template").Visible = True
        On Error Resume Next
        If Not Intersect(Target, Range("b11:b")) Is Nothing Then
            Set wsNew = Sheets(Target.Text)
            If wsNew Is Nothing Then Sheets("Template").Copy After:=Sheets("Masterlist")
            ActiveSheet.Name = MySheetName
            Sheets("Template").Visible = False
            
        End If
    Application.ScreenUpdating = True
    
    End Sub
    Last edited by daivd_vachon; 10-12-2012 at 03:48 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1