Help with event code

[JMay]

Note the 3rd line of code from the end where
I instruct my code to copy my entire sheet1 in order
To paste it to a newly created sheet (NewSheet)
Why does a new workbook get created with the copy?

TIA,


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("d2")) Is Nothing Then
Exit Sub
End If
If Target.Address = "$D$2" Then
Sheets.Add After:=Worksheets(Worksheets.Count - 2)
End If
NewSheet = ActiveSheet.Name
Sheets("Sheet1").Activate
ActiveSheet.Copy '' <<<< ????? Why does this line Open a New
' Workbook an Copy my data into it <<the new ' workbook?
Sheets(NewSheet).Activate
ActiveSheet.Paste
End Sub

 

[Dave Peterson]

activesheet.copy
copies the sheet (like ctrl-dragging the tab). It's not copying the cells on
the worksheet.

You could use:
activesheet.cells.copy

But I think I'd do something like:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewSheet As Worksheet 'not a string

If Intersect(Target, Me.Range("d2")) Is Nothing Then
Exit Sub
End If

'no need to check, since your code already got out if
'you weren't in D2
Set NewSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count - 2))

Me.Cells.Copy _
NewSheet.Range("a1")

'or
'worksheets("Sheet1").cells.copy _
' destination:=newwks.range("a1")

End Sub

Or even:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewSheet As Worksheet 'not a string

If Intersect(Target, Me.Range("d2")) Is Nothing Then
Exit Sub
End If

Me.Copy _
After:=Worksheets(Worksheets.Count - 2)

End Sub

But I wasn't sure what sheet is sheet1 or if you wanted the code to go with it.

 

[JMay]

Thanks Dave; Got it !

activesheet.copy
copies the sheet (like ctrl-dragging the tab). It's not copying the cells on
the worksheet.

You could use:
activesheet.cells.copy

But I think I'd do something like:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewSheet As Worksheet 'not a string

If Intersect(Target, Me.Range("d2")) Is Nothing Then
Exit Sub
End If

'no need to check, since your code already got out if
'you weren't in D2
Set NewSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count - 2))

Me.Cells.Copy _
NewSheet.Range("a1")

'or
'worksheets("Sheet1").cells.copy _
' destination:=newwks.range("a1")

End Sub

Or even:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewSheet As Worksheet 'not a string

If Intersect(Target, Me.Range("d2")) Is Nothing Then
Exit Sub
End If

Me.Copy _
After:=Worksheets(Worksheets.Count - 2)

End Sub

But I wasn't sure what sheet is sheet1 or if you wanted the code to go with it.