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

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

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

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

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

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

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

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

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