VBA to generate new sheet

[twl2009]

Hi, I need a bit of code that when any text is entered into range A11:G23 if that specific text isnt matched in the range anywhere it creates a new sheet (a copy of sheet2) and names it with the text entered.

Is this possible?

Thanks

 

[twl2009]

A11
I get
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px 'Helvetica Neue'}</style>Method 'CountIf' of object 'WorksheetFunction' failed

 

[Joe4]

OK. I did not test down to that part, I was just testing the building of your ranges.
I don't think you can use non-contiguous ranges in the COUNTIF function. So I think you will need to have a block work through each range individually.

 

[twl2009]

<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>I think Im getting somewhere, but am struggling with the countIf part. I feel that it needs 'If Appli....countif(rng1, target) = 1' And ...Rng2 =0 ...And ..Rng3 =0 etc but everything Ive tried doesnt work.

Any pointers???




Private Sub Worksheet_Change(ByVal Target As Range)




Application.ScreenUpdating = False




Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim Rng4 As Range
Dim isect1 As Range
Dim isect2 As Range
Dim isect3 As Range
Dim isect4 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 Rng1 = Range("A11:G22")
Set Rng2 = Range("A26:G34")
Set Rng3 = Range("A38:G46")
Set Rng4 = Range("A50:G58")
Set isect1 = Intersect(Target, Rng1)
Set isect2 = Intersect(Target, Rng2)
Set isect3 = Intersect(Target, Rng3)
Set isect4 = Intersect(Target, Rng4)


If isect1 Is Nothing And isect2 Is Nothing And isect3 Is Nothing And isect4 Is Nothing Then Exit Sub




If Application.WorksheetFunction.CountIf(Rng1, 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




End Sub

 

[Joe4]

You didn't quite finish the job. You only did the following block for the first range:

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

What about the other three ranges? I believe you need to repeat this block for those three as well.

 

[twl2009]

Yep, I have tried having 4 blocks for the 4 ranges, Unfortunately I get a 'subscript out of range' error. But also would it not generate a new sheet when a name is entered in Rng2 even if it already existed in Rng1, for example.

Is the logic not ..

if it is in Rng1 once, but not in Rng234, or if its in Rng2 once but not in Rng134, ....etc
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff}</style>

 

[twl2009]

Or is there a way to have

'Something1' = Countif(Rng1, Target)
'Something2' = Countif(Rng2, target)
Etc

And then a way of asking if something 1,2,3,4 = 1

Or is this wishful foolishness.

Thanks

 

[JLGWhiz]

Or is there a way to have

'Something1' = Countif(Rng1, Target)
'Something2' = Countif(Rng2, target)
Etc

And then a way of asking if something 1,2,3,4 = 1

Or is this wishful foolishness.

Thanks

I think you could probably use something like

If Application.CountIf(ActiveSheet.UsedRange, Target.Value) = 1 Then

The UsedRange syntax would incorporate all of your separate ranges.

 

[twl2009]

Thanks, that gives me

'Subscript out of range'

 

[JLGWhiz]

Thanks, that gives me

'Subscript out of range'

It would be nice to see how you used it. The subscript out of range message indicates that VBA cannot find the Object and it should be able to find the ActiveSheet and UsedRange without a problem. That leaves the Target variable. So if there is not Target variable initialized then I assume the statement is not used in the Worksheet_Change macro.

 

[twl2009]

Thanks JLGWhiz. I know spot that the sheet name had been slightly altered at some point. It works across all the ranges now, except that if the same text is put in anywhere twice it gives a 'Object required' error message.