| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection after copy sheet failed
After copying a sheet, I cannot select a cell anymore and I cannot figure out
why. My Sub starts as follows : Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Target.Address = "$N$30" And Range("Year").Value < Range("LastYear").Value Then Dim NewYear As VbMsgBoxResult Dim TabName As String NewYear = MsgBox("Save a copy?", vbYesNoCancel) If NewYear= vbCancel Then Range("Year").Value = Range("LastYear").Value Range("$N$30").Select Exit Sub ElseIf NewYear= vbYes Then TabName = "Archive " & Range("LastYear").Value Sheets("Current year").Select Sheets("Current year").Copy after:=Sheets("Current year") Sheets("Current year (2)").Name = TabName Range("A1:AI53").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Rows("54:500").Select Selection.Delete End If The line : Range("A1:AI53").Select Causes error : Run-time error '1004': Select method of Range class failed Anyone any idea? Thanks in advance, Henk |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selection after copy sheet failed
Select the desired sheet before you select the range
Sheets("Sheetname").Select Range("A1:AI53").Select If this post helps click Yes --------------- Jacob Skaria "Henk" wrote: After copying a sheet, I cannot select a cell anymore and I cannot figure out why. My Sub starts as follows : Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Target.Address = "$N$30" And Range("Year").Value < Range("LastYear").Value Then Dim NewYear As VbMsgBoxResult Dim TabName As String NewYear = MsgBox("Save a copy?", vbYesNoCancel) If NewYear= vbCancel Then Range("Year").Value = Range("LastYear").Value Range("$N$30").Select Exit Sub ElseIf NewYear= vbYes Then TabName = "Archive " & Range("LastYear").Value Sheets("Current year").Select Sheets("Current year").Copy after:=Sheets("Current year") Sheets("Current year (2)").Name = TabName Range("A1:AI53").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Rows("54:500").Select Selection.Delete End If The line : Range("A1:AI53").Select Causes error : Run-time error '1004': Select method of Range class failed Anyone any idea? Thanks in advance, Henk |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selection after copy sheet failed
Hi there,
Instead of using -- Range("A1:A153").Select Use -- ActiveSheet.Range("A1:A153").Select as you are writing the code in the same sheet, so its not needed to define it. define it when you write the code in ThisWorkbook code window. Good Luck, Sanjay You "Jacob Skaria" wrote: Select the desired sheet before you select the range Sheets("Sheetname").Select Range("A1:AI53").Select If this post helps click Yes --------------- Jacob Skaria "Henk" wrote: After copying a sheet, I cannot select a cell anymore and I cannot figure out why. My Sub starts as follows : Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Target.Address = "$N$30" And Range("Year").Value < Range("LastYear").Value Then Dim NewYear As VbMsgBoxResult Dim TabName As String NewYear = MsgBox("Save a copy?", vbYesNoCancel) If NewYear= vbCancel Then Range("Year").Value = Range("LastYear").Value Range("$N$30").Select Exit Sub ElseIf NewYear= vbYes Then TabName = "Archive " & Range("LastYear").Value Sheets("Current year").Select Sheets("Current year").Copy after:=Sheets("Current year") Sheets("Current year (2)").Name = TabName Range("A1:AI53").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Rows("54:500").Select Selection.Delete End If The line : Range("A1:AI53").Select Causes error : Run-time error '1004': Select method of Range class failed Anyone any idea? Thanks in advance, Henk |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selection after copy sheet failed
tHenks Sanjay,
This indeed helped me, but I immediately ran into another problem. The sheet that is copied contains a lot of VB code, including the Worksheet_Change macro copying the sheet. So, the moment I am (the macro is) making changes to the copy of the sheet the same macro on that sheet is started (and errored). I have made a workaround by an if statement asking whether the sheet the macro is started from is the original sheet, which works perfectly. But, in fact I do want to delete all code from the copy of the sheet, directly after the moment it is created. I do know how to delete a module or form, but how to delete code from a specific sheet? Do you know? "Sanjay" wrote: Hi there, Instead of using -- Range("A1:A153").Select Use -- ActiveSheet.Range("A1:A153").Select as you are writing the code in the same sheet, so its not needed to define it. define it when you write the code in ThisWorkbook code window. Good Luck, Sanjay You "Jacob Skaria" wrote: Select the desired sheet before you select the range Sheets("Sheetname").Select Range("A1:AI53").Select If this post helps click Yes --------------- Jacob Skaria "Henk" wrote: After copying a sheet, I cannot select a cell anymore and I cannot figure out why. My Sub starts as follows : Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Target.Address = "$N$30" And Range("Year").Value < Range("LastYear").Value Then Dim NewYear As VbMsgBoxResult Dim TabName As String NewYear = MsgBox("Save a copy?", vbYesNoCancel) If NewYear= vbCancel Then Range("Year").Value = Range("LastYear").Value Range("$N$30").Select Exit Sub ElseIf NewYear= vbYes Then TabName = "Archive " & Range("LastYear").Value Sheets("Current year").Select Sheets("Current year").Copy after:=Sheets("Current year") Sheets("Current year (2)").Name = TabName Range("A1:AI53").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Rows("54:500").Select Selection.Delete End If The line : Range("A1:AI53").Select Causes error : Run-time error '1004': Select method of Range class failed Anyone any idea? Thanks in advance, Henk |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selection after copy sheet failed
Dear Jacob,
tHenks for your prompt reply. I did not try this one, but tried the solution Sanjay suggested herunder. Please read my reply on that. Regards, Henk "Jacob Skaria" wrote: Select the desired sheet before you select the range Sheets("Sheetname").Select Range("A1:AI53").Select If this post helps click Yes --------------- Jacob Skaria "Henk" wrote: After copying a sheet, I cannot select a cell anymore and I cannot figure out why. My Sub starts as follows : Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Target.Address = "$N$30" And Range("Year").Value < Range("LastYear").Value Then Dim NewYear As VbMsgBoxResult Dim TabName As String NewYear = MsgBox("Save a copy?", vbYesNoCancel) If NewYear= vbCancel Then Range("Year").Value = Range("LastYear").Value Range("$N$30").Select Exit Sub ElseIf NewYear= vbYes Then TabName = "Archive " & Range("LastYear").Value Sheets("Current year").Select Sheets("Current year").Copy after:=Sheets("Current year") Sheets("Current year (2)").Name = TabName Range("A1:AI53").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Rows("54:500").Select Selection.Delete End If The line : Range("A1:AI53").Select Causes error : Run-time error '1004': Select method of Range class failed Anyone any idea? Thanks in advance, Henk |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selection after copy sheet failed
Dear Henk
When you work with WorkSheet Change event always disable the events. as below. Try and feedback... Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False 'your code 'your code end here Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "Henk" wrote: tHenks Sanjay, This indeed helped me, but I immediately ran into another problem. The sheet that is copied contains a lot of VB code, including the Worksheet_Change macro copying the sheet. So, the moment I am (the macro is) making changes to the copy of the sheet the same macro on that sheet is started (and errored). I have made a workaround by an if statement asking whether the sheet the macro is started from is the original sheet, which works perfectly. But, in fact I do want to delete all code from the copy of the sheet, directly after the moment it is created. I do know how to delete a module or form, but how to delete code from a specific sheet? Do you know? "Sanjay" wrote: Hi there, Instead of using -- Range("A1:A153").Select Use -- ActiveSheet.Range("A1:A153").Select as you are writing the code in the same sheet, so its not needed to define it. define it when you write the code in ThisWorkbook code window. Good Luck, Sanjay You "Jacob Skaria" wrote: Select the desired sheet before you select the range Sheets("Sheetname").Select Range("A1:AI53").Select If this post helps click Yes --------------- Jacob Skaria "Henk" wrote: After copying a sheet, I cannot select a cell anymore and I cannot figure out why. My Sub starts as follows : Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Target.Address = "$N$30" And Range("Year").Value < Range("LastYear").Value Then Dim NewYear As VbMsgBoxResult Dim TabName As String NewYear = MsgBox("Save a copy?", vbYesNoCancel) If NewYear= vbCancel Then Range("Year").Value = Range("LastYear").Value Range("$N$30").Select Exit Sub ElseIf NewYear= vbYes Then TabName = "Archive " & Range("LastYear").Value Sheets("Current year").Select Sheets("Current year").Copy after:=Sheets("Current year") Sheets("Current year (2)").Name = TabName Range("A1:AI53").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Rows("54:500").Select Selection.Delete End If The line : Range("A1:AI53").Select Causes error : Run-time error '1004': Select method of Range class failed Anyone any idea? Thanks in advance, Henk |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selection after copy sheet failed
Jacob,
That's a good one!! And so simple! I did not know this existed. Solves a very lot of other anoying problems. Many tHenks !!! But still I do want to delete the VB code from the copy of the sheet. Any brlliant ideas about that? "Jacob Skaria" wrote: Dear Henk When you work with WorkSheet Change event always disable the events. as below. Try and feedback... Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False 'your code 'your code end here Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "Henk" wrote: tHenks Sanjay, This indeed helped me, but I immediately ran into another problem. The sheet that is copied contains a lot of VB code, including the Worksheet_Change macro copying the sheet. So, the moment I am (the macro is) making changes to the copy of the sheet the same macro on that sheet is started (and errored). I have made a workaround by an if statement asking whether the sheet the macro is started from is the original sheet, which works perfectly. But, in fact I do want to delete all code from the copy of the sheet, directly after the moment it is created. I do know how to delete a module or form, but how to delete code from a specific sheet? Do you know? "Sanjay" wrote: Hi there, Instead of using -- Range("A1:A153").Select Use -- ActiveSheet.Range("A1:A153").Select as you are writing the code in the same sheet, so its not needed to define it. define it when you write the code in ThisWorkbook code window. Good Luck, Sanjay You "Jacob Skaria" wrote: Select the desired sheet before you select the range Sheets("Sheetname").Select Range("A1:AI53").Select If this post helps click Yes --------------- Jacob Skaria "Henk" wrote: After copying a sheet, I cannot select a cell anymore and I cannot figure out why. My Sub starts as follows : Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Target.Address = "$N$30" And Range("Year").Value < Range("LastYear").Value Then Dim NewYear As VbMsgBoxResult Dim TabName As String NewYear = MsgBox("Save a copy?", vbYesNoCancel) If NewYear= vbCancel Then Range("Year").Value = Range("LastYear").Value Range("$N$30").Select Exit Sub ElseIf NewYear= vbYes Then TabName = "Archive " & Range("LastYear").Value Sheets("Current year").Select Sheets("Current year").Copy after:=Sheets("Current year") Sheets("Current year (2)").Name = TabName Range("A1:AI53").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Rows("54:500").Select Selection.Delete End If The line : Range("A1:AI53").Select Causes error : Run-time error '1004': Select method of Range class failed Anyone any idea? Thanks in advance, Henk |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selection after copy sheet failed
Refer the below link on 'Deleting A Module From A Project' by Chip Pearson
http://www.cpearson.com/Excel/vbe.aspx -- If this post helps click Yes --------------- Jacob Skaria "Henk" wrote: Jacob, That's a good one!! And so simple! I did not know this existed. Solves a very lot of other anoying problems. Many tHenks !!! But still I do want to delete the VB code from the copy of the sheet. Any brlliant ideas about that? "Jacob Skaria" wrote: Dear Henk When you work with WorkSheet Change event always disable the events. as below. Try and feedback... Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False 'your code 'your code end here Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "Henk" wrote: tHenks Sanjay, This indeed helped me, but I immediately ran into another problem. The sheet that is copied contains a lot of VB code, including the Worksheet_Change macro copying the sheet. So, the moment I am (the macro is) making changes to the copy of the sheet the same macro on that sheet is started (and errored). I have made a workaround by an if statement asking whether the sheet the macro is started from is the original sheet, which works perfectly. But, in fact I do want to delete all code from the copy of the sheet, directly after the moment it is created. I do know how to delete a module or form, but how to delete code from a specific sheet? Do you know? "Sanjay" wrote: Hi there, Instead of using -- Range("A1:A153").Select Use -- ActiveSheet.Range("A1:A153").Select as you are writing the code in the same sheet, so its not needed to define it. define it when you write the code in ThisWorkbook code window. Good Luck, Sanjay You "Jacob Skaria" wrote: Select the desired sheet before you select the range Sheets("Sheetname").Select Range("A1:AI53").Select If this post helps click Yes --------------- Jacob Skaria "Henk" wrote: After copying a sheet, I cannot select a cell anymore and I cannot figure out why. My Sub starts as follows : Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Target.Address = "$N$30" And Range("Year").Value < Range("LastYear").Value Then Dim NewYear As VbMsgBoxResult Dim TabName As String NewYear = MsgBox("Save a copy?", vbYesNoCancel) If NewYear= vbCancel Then Range("Year").Value = Range("LastYear").Value Range("$N$30").Select Exit Sub ElseIf NewYear= vbYes Then TabName = "Archive " & Range("LastYear").Value Sheets("Current year").Select Sheets("Current year").Copy after:=Sheets("Current year") Sheets("Current year (2)").Name = TabName Range("A1:AI53").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Rows("54:500").Select Selection.Delete End If The line : Range("A1:AI53").Select Causes error : Run-time error '1004': Select method of Range class failed Anyone any idea? Thanks in advance, Henk |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selection after copy sheet failed
Jacob,
Someone else sent me the same link. Here is my answer : Joel, Thanks for your prompt reply. I had been there before and after my visit I manged to delete Modules and Forms from my VBA projects, but now I want to delete all code from a specific Worksheet. Chip does not tell how to do that. Any idea? Regards, Henk "Jacob Skaria" wrote: Refer the below link on 'Deleting A Module From A Project' by Chip Pearson http://www.cpearson.com/Excel/vbe.aspx -- If this post helps click Yes --------------- Jacob Skaria "Henk" wrote: Jacob, That's a good one!! And so simple! I did not know this existed. Solves a very lot of other anoying problems. Many tHenks !!! But still I do want to delete the VB code from the copy of the sheet. Any brlliant ideas about that? "Jacob Skaria" wrote: Dear Henk When you work with WorkSheet Change event always disable the events. as below. Try and feedback... Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False 'your code 'your code end here Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "Henk" wrote: tHenks Sanjay, This indeed helped me, but I immediately ran into another problem. The sheet that is copied contains a lot of VB code, including the Worksheet_Change macro copying the sheet. So, the moment I am (the macro is) making changes to the copy of the sheet the same macro on that sheet is started (and errored). I have made a workaround by an if statement asking whether the sheet the macro is started from is the original sheet, which works perfectly. But, in fact I do want to delete all code from the copy of the sheet, directly after the moment it is created. I do know how to delete a module or form, but how to delete code from a specific sheet? Do you know? "Sanjay" wrote: Hi there, Instead of using -- Range("A1:A153").Select Use -- ActiveSheet.Range("A1:A153").Select as you are writing the code in the same sheet, so its not needed to define it. define it when you write the code in ThisWorkbook code window. Good Luck, Sanjay You "Jacob Skaria" wrote: Select the desired sheet before you select the range Sheets("Sheetname").Select Range("A1:AI53").Select If this post helps click Yes --------------- Jacob Skaria "Henk" wrote: After copying a sheet, I cannot select a cell anymore and I cannot figure out why. My Sub starts as follows : Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Target.Address = "$N$30" And Range("Year").Value < Range("LastYear").Value Then Dim NewYear As VbMsgBoxResult Dim TabName As String NewYear = MsgBox("Save a copy?", vbYesNoCancel) If NewYear= vbCancel Then Range("Year").Value = Range("LastYear").Value Range("$N$30").Select Exit Sub ElseIf NewYear= vbYes Then TabName = "Archive " & Range("LastYear").Value Sheets("Current year").Select Sheets("Current year").Copy after:=Sheets("Current year") Sheets("Current year (2)").Name = TabName Range("A1:AI53").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Rows("54:500").Select Selection.Delete End If The line : Range("A1:AI53").Select Causes error : Run-time error '1004': Select method of Range class failed Anyone any idea? Thanks in advance, Henk |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Selection copy and insert to other sheet (Macro) | Excel Discussion (Misc queries) | |||
| Selection copy and insert to other sheet | Excel Discussion (Misc queries) | |||
| Selection.Copy on Temporarily unhidden excel sheet - possible bug? | Excel Programming | |||
| Copy Selection to new sheet | Excel Programming | |||
| Selection Failed | Excel Programming | |||
Linear Mode
