Results 1 to 2 of 2

set range as multiple ranges VBA

This is a discussion on set range as multiple ranges VBA within the Excel Questions forums, part of the Question Forums category; Im trying to use multiple ranges within the range 'Multirange', but have got something wrong. Was all working fine before ...

  1. #1
    Board Regular
    Join Date
    Jan 2016
    Posts
    212

    Default set range as multiple ranges VBA

    Im trying to use multiple ranges within the range 'Multirange', but have got something wrong. Was all working fine before with a single range.

    Thanks

    [CODE]Private Sub Worksheet_Change(ByVal Target As Range)




    Application.ScreenUpdating = False




    Dim r1 As Range, r2 As Range, r3 As Range, r4 As Range
    Dim MultiRange As Range
    Dim isect As Range




    If Target.Count > 1 Then Exit Sub




    If Target.Address(False, False) = "B2" Then Call TitleCheck




    If Target.Address(False, False) = "F2" Then Call DateCheck


    Set r1 = Range("A11:G23")
    Set r2 = Range("A26:G34")
    Set r3 = Range("A38:G46")
    Set r4 = Range("A50:G58")
    Set MultiRange = Union(r1, r2, r3, r4)
    Set isect = Intersect(Target, MultiRange)
    If isect Is Nothing Then Exit Sub




    If Application.WorksheetFunction.CountIf(MultiRange, Target) = 1 Then
    Set MyActiveCell = ActiveCell
    Sheets("Timesheet").Cells.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveSheet.Name = Target.Value & " Timesheet"
    End If


    Sheets("Labour Week").Activate
    MyActiveCell.Select


    Application.ScreenUpdating = True




    EndSub/CODE]

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    39,183

    Default Re: set range as multiple ranges VBA

    Duplicate: https://www.mrexcel.com/forum/excel-...new-sheet.html

    Please do not post the same question multiple times. All clarifications, directly-related follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #12 here: http://www.mrexcel.com/forum/showthread.php?t=99490).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com