set range as multiple ranges VBA

[twl2009]

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

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff; min-height: 13.0px}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993; background-color: #ffffff}span.s1 {color: #011993}span.s2 {color: #000000}</style>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




[COLOR=#011993][FONT=Menlo]End[/FONT][/COLOR][FONT=Menlo] [/FONT][COLOR=#011993][FONT=Menlo]Sub[/FONT][/COLOR]/CODE]

 

[Joe4]

Duplicate: https://www.mrexcel.com/forum/excel...al-basic-applications-generate-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).