Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event macro dismiss table1 copy reinstate Table1
I get an error on this line where I try to reinstate Table1 to the Summary sheet
row 3 on down to last row of data. ..ListObjects.Add(xlSrcRange, Range("$A$3:$D$" & tRow), , xlYes).Name = _ "Table1" This line is used in another non-event code and works there. It is inside a With Sheets("Summary") statement in that code also. Thanks, Howard Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Count 1 Then Exit Sub If Intersect(Target, Range("A:D")) Is Nothing Then Exit Sub If LCase(ActiveSheet.Name) = "summary" Then GoTo done If LCase(ActiveSheet.Name) = "begin blad" Then GoTo done With Application .EnableEvents = False .ScreenUpdating = False End With Dim aRow As Long Dim aRng As Range Dim tRow As Long, i As Long aRow = Target.Row Set aRng = Range("A" & aRow).Resize(1, 4) If Application.WorksheetFunction.CountA(aRng) = 4 Then With Sheets("Summary") For i = 1 To .ListObjects.Count .ListObjects(i).Unlist Next aRng.Copy .Range("A" & Rows.Count).End(xlUp)(2) '**** reinstate Table1 to Summary sheet ***** tRow = Cells(Rows.Count, "A").End(xlUp).Row .ListObjects.Add(xlSrcRange, Range("$A$3:$D$" & tRow), , xlYes).Name = _ "Table1" .ListObjects("Table1").TableStyle = "TableStyleLight2" End With End If On Error GoTo aft_error aft_error: With Application .EnableEvents = True .ScreenUpdating = True End With done: End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event macro dismiss table1 copy reinstate Table1
Hi Howard,
Am Sat, 28 Nov 2015 03:11:38 -0800 (PST) schrieb L. Howard: If LCase(ActiveSheet.Name) = "summary" Then GoTo done I guess the line above causes the error and there are some dots missing. Why do write the code into a Worksheet_Change event? If Col A get changed or a value is inserted the macro fires. Also in the Cols B, C and D. If always all cells are filled restrict the macro for only column D: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Intersect(Target, Range("D:D")) Is Nothing _ Or Target.Count 1 Then Exit Sub If LCase(ActiveSheet.Name) < "summary" And _ LCase(ActiveSheet.Name) < "begin blad" Then With Application .EnableEvents = False .ScreenUpdating = False End With On Error GoTo aft_error Dim aRow As Long Dim aRng As Range Dim tRow As Long, i As Long aRow = Target.Row Set aRng = Range("A" & aRow).Resize(1, 4) With Sheets("Summary") For i = 1 To .ListObjects.Count .ListObjects(i).Unlist Next .Range("A" & Rows.Count).End(xlUp)(2) _ .Resize(1, 4).Value = aRng.Value '**** reinstate Table1 to Summary sheet ***** tRow = .Cells(.Rows.Count, "A").End(xlUp).Row .ListObjects.Add(xlSrcRange, .Range("$A$3:$D$" & tRow), , xlYes) _ .Name = "Table1" .ListObjects("Table1").TableStyle = "TableStyleLight2" End With End If aft_error: With Application .EnableEvents = True .ScreenUpdating = True End With End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event macro dismiss table1 copy reinstate Table1
On Saturday, November 28, 2015 at 4:21:05 AM UTC-8, Claus Busch wrote:
Hi Howard, Am Sat, 28 Nov 2015 03:11:38 -0800 (PST) schrieb L. Howard: If LCase(ActiveSheet.Name) = "summary" Then GoTo done I guess the line above causes the error and there are some dots missing. Why do write the code into a Worksheet_Change event? If Col A get changed or a value is inserted the macro fires. Also in the Cols B, C and D. If always all cells are filled restrict the macro for only column D: Regards Claus B. Hi Claus, There seems to be a need to have a code to move all data from all the sheets (the ones named A, B, C, D, E... etc.) to a sheet named Summary some times, and other times there is a need to only move the "new entry" from any of the A, B, C... etc, sheets to Summary. The "new entry" to the sheets appears to be done with a UserForm which will fill columns A, B, C & D on any of the sheets. So I Counta(aRng) for a total of 4. Each of the A B C sheets hold names that start with A for the A Sheet, names that start with B for the B sheet and so on. The OP is very limited in english and the workbook usage is my best guess from the example. I wanted to be sure all columns were filled, A, B, C & D before transfering the data. I could not get a clear answer on that from th OP. Most likely it will always be 4 per row and I think the D:D will be fine. The code works well, thanks for smoothing it out. Howard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event macro dismiss table1 copy reinstate Table1
Hi Howard,
Am Sat, 28 Nov 2015 05:18:24 -0800 (PST) schrieb L. Howard: The "new entry" to the sheets appears to be done with a UserForm which will fill columns A, B, C & D on any of the sheets. So I Counta(aRng) for a total of 4. if you want to make sure that all cells are filled change the copy part of the code to: If Application.CountA(aRng) = 4 Then .Range("A" & Rows.Count).End(xlUp)(2) _ .Resize(1, 4).Value = aRng.Value End If Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event macro dismiss table1 copy reinstate Table1
if you want to make sure that all cells are filled change the copy part
of the code to: If Application.CountA(aRng) = 4 Then .Range("A" & Rows.Count).End(xlUp)(2) _ .Resize(1, 4).Value = aRng.Value End If Regards Claus B. Hi Claus, I put the counta snippet in the same place as the other copy code and it does not respond. I made sure all the sheets we enable events set back to True by running a little enable snippet on them. Howard Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Intersect(Target, Range("D:D")) Is Nothing _ Or Target.Count 1 Then Exit Sub If LCase(ActiveSheet.Name) < "summary" And _ LCase(ActiveSheet.Name) < "begin blad" Then With Application .EnableEvents = False .ScreenUpdating = False End With On Error GoTo aft_error Dim aRow As Long Dim aRng As Range Dim tRow As Long, i As Long aRow = Target.Row Set aRng = Range("A" & aRow).Resize(1, 4) With Sheets("Summary") For i = 1 To .ListObjects.Count .ListObjects(i).Unlist Next 'Write the last entry col A to col D to Summary sheet if there are 4 entries '.Range("A" & Rows.Count).End(xlUp)(2) _ .Resize(1, 4).Value = aRng.Value If Application.CountA(aRng) = 4 Then .Range("A" & Rows.Count).End(xlUp)(2) _ .Resize(1, 4).Value = aRng.Value End If 'reinstate Table1 to Summary sheet tRow = .Cells(.Rows.Count, "A").End(xlUp).Row .ListObjects.Add(xlSrcRange, .Range("$A$3:$D$" & tRow), , xlYes) _ .Name = "Table1" '.ListObjects("Table1").TableStyle = "TableStyleLight2" End With End If aft_error: With Application .EnableEvents = True .ScreenUpdating = True End With End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event macro dismiss table1 copy reinstate Table1
Hi Claus,
Disregard last post, I went back to the sheet to do further trouble shooting and all is working fine. Has to be my demon version of Excel, can't be my fault.<g Thanks, Howard |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event macro dismiss table1 copy reinstate Table1
Howard,
Nothing to add to Claus' suggestion, but FWIW... I suggest you put the 'task' part of your code in a standard module and just call it from the event. You can pass any refs it needs from the event itself. This approach keeps your event code maintenance minimal, and keeps your 'task' code central so it can be used by all sheets. I see you're using the Workbook_SheetChange event so it fires whenever any sheet change occurs. This may not always be desireable when the process run is dependant on conditional criteria specific to some sheets and not others. Example: In the change event behind several sheets... Option Explicit Private Sub WorkSheet_Change(ByVal Target As Range) Call MySub(Target) End Sub In a standard module... Option Explicit Sub MySub(Rng As Range, Optional Wks As Worksheet) 'In case of sheet event, Wks can be omitted 'In case of workbook_sheet event, this may be required If Wks Is Nothing Then Set Wks = ActiveSheet 'task code follows End Sub ...now you have a universal procedure that can be called from a sheet or workbook module as you do here. The call from Workbook code would be... Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) If InStr(gsDetailShts, Sh.Name) 0 Then Call MySub(Target, Sh) If InStr(gsOtherShts, Sh.Name) 0 Then Call MyOtherSub(Target, Sh) End Sub ...where gsDetailShts/gsOtherShts are global scope constants containing a delimited list of sheets that use the called task procedures. These lists could also be stored in a global variable that you load from a hidden sheet in a project where users add worksheets as needed. (Assumes your project has a built-in mechanism for adding new sheets) Normally, the global variables get loaded at startup and updated when sheets are added/deleted. This may seem a bit too complex for your current project, but adopting good 'structured programming' habits goes a long way towards building more robust solutions very quickly! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event macro dismiss table1 copy reinstate Table1
Hi Garry,
Actually I was going to keep the "task" part in a module, mostly because there was already a script there and was doing almost the same thing as I wanted in the event code. Just modify it a bit and that would have done the job. Then it seemed to be a bit of "over do" to call from each sheet, and there would be sheets from "A" to "Z". I was thinking correctly, but went the other way. Thanks for the tip. Howard |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event macro dismiss table1 copy reinstate Table1
Hi Garry,
Actually I was going to keep the "task" part in a module, mostly because there was already a script there and was doing almost the same thing as I wanted in the event code. Just modify it a bit and that would have done the job. Then it seemed to be a bit of "over do" to call from each sheet, and there would be sheets from "A" to "Z". I was thinking correctly, but went the other way. Thanks for the tip. Howard I appreciate the feedback! Glad to see you're making good progress with things...<g -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OnTime Change_Event macro AND copy to sheet2 macro trouble | Excel Programming | |||
Web Login, Table1 | Excel Programming | |||
Cannot Dismiss the MsgBox | Excel Programming | |||
reinstate smart tag | Excel Discussion (Misc queries) | |||
Dismiss a dialog box (Via a Macro) | Excel Programming |