Help with event code
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 |
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 |
Help with event code
Thanks Dave; Got it !
" wrote in message : 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 |
All times are GMT +1. The time now is 03:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com