Help with event code
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 wrote:
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
|