Sheets in workbook are A, B, C, D, E, F, G plus the two that are excluded in the code.

Total combined rows with data for all A to G sheets is about 52. (about 7 per sheet.)

If I run SheetsCopy1 I get about 5000 rows copied to Summary sheet, multiple repeats of correct data.

If I run SheetsCopy2 I see each sheet range to copy is properly selected and the Msgbox shows the correct sheet name.

The DeBug.Print lists the proper sheets A to G.

Code is in a standard module, and the downloaded example workbook looks to be German, saved as ...xlsm.

What little tid-bit am I overlooking here?

Thanks,
Howard

Sub SheetsCopy1()
Dim ws As Worksheet
Dim Rng As Range

For Each ws In ThisWorkbook.Sheets

If (ws.Name < "Summary") And (ws.Name < "Begin blad") Then

With ws

ws.Activate
Set Rng = Range("A2", Range("D2").End(xlDown))
Rng.Copy Sheets("Summary").Range("A" & Rows.Count).End(xlUp)(2)

End With

End If

Next
End Sub

Sub SheetsCopy2()
Dim ws As Worksheet
Dim Rng As Range
Dim lRow As Long

For Each ws In ThisWorkbook.Sheets

If (ws.Name < "Summary") And (ws.Name < "Begin blad") Then
'Debug.Print ws.Name
With ws
ws.Activate
Set Rng = Range("A2", Range("D2").End(xlDown))
Rng.Select
MsgBox ws.Name
End With

End If

Next
End Sub

Hi Howard,
Any time I have to consolidate data from multiple sheets to a 'summary'
sheet I use the following logic:

Dim wks As Worksheet, vData, lNextRow&

With Sheets("Summary")
'Get the next row position on the summary sheet
lNextRow = .Cells(1, .Rows.Count).End(xlUp).Row + 1

For Each wks In ThisWorkbook.Sheets
'Load the data range on each consolidation sheet into an array.
If (wks.Name < "Summary") And (wks.Name < "Begin blad") Then
vData = wks.Range("A2", wks.Range("D2").End(xlDown))

'Assign the array to the next row position
.Cells(lNextRow, 1).Resize(Ubound(vData), _
Ubound(vdata), 2) = vData

'Increment the next row position
lNextRow = lNextRow + Ubound(vData)
End If
Next 'wks
End With 'Sheets("Summary")

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

,
Any time I have to consolidate data from multiple sheets to a 'summary'
sheet I use the following logic:

Dim wks As Worksheet, vData, lNextRow&

With Sheets("Summary")
'Get the next row position on the summary sheet
lNextRow = .Cells(1, .Rows.Count).End(xlUp).Row + 1

For Each wks In ThisWorkbook.Sheets
'Load the data range on each consolidation sheet into an array.
If (wks.Name < "Summary") And (wks.Name < "Begin blad") Then
vData = wks.Range("A2", wks.Range("D2").End(xlDown))

'Assign the array to the next row position
.Cells(lNextRow, 1).Resize(Ubound(vData), _
Ubound(vdata), 2) = vData

'Increment the next row position
lNextRow = lNextRow + Ubound(vData)
End If
Next 'wks
End With 'Sheets("Summary")

--
Garry

Hi Garry,

Thanks for taking a look see.

This line errors with xlUP error value -4162

lNextRow = .Cells(1, .Rows.Count).End(xlUp).Row + 1

Tried with 15 +- rows of data already on Summary sheet and with only the headers on the sheet. Both give same error...?

Summary is a table for the A B C D columns, and the line failed, changed the table to a range, same result. Not sure that would make any difference anyway.

I also know that Excel has a personal vendetta out for me, given my luck with recent projects!

Howard

My bad! (aircode<g)

This..
lNextRow = .Cells(1, .Rows.Count).End(xlUp).Row + 1

...should be...

lNextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Thursday, November 26, 2015 at 5:03:00 PM UTC-8, GS wrote:
My bad! (aircode<g)

This..
lNextRow = .Cells(1, .Rows.Count).End(xlUp).Row + 1

..should be...

lNextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

--
Garry

Hi Garry,

Thanks, actually if I had studied the code harder, I should have seen that.

I did have to make a change on this line for the Resize columns.

..Cells(lNextRow, 1).Resize(UBound(vData), 4) = vData

Works great now.

Thanks for the array approach, I like the array shot at it, but it still whacks me most often.

Howard

On Thursday, November 26, 2015 at 5:03:00 PM UTC-8, GS wrote:
My bad! (aircode<g)

This..
lNextRow = .Cells(1, .Rows.Count).End(xlUp).Row + 1

..should be...

lNextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

--
Garry

Hi Garry,

Thanks, actually if I had studied the code harder, I should have seen
that.

I did have to make a change on this line for the Resize columns.

.Cells(lNextRow, 1).Resize(UBound(vData), 4) = vData

Works great now.

Thanks for the array approach, I like the array shot at it, but it
still whacks me most often.

Howard

Why did you hardcode the cols? The code uses the cols in vData, which
is 4 based on your range of A:D! You should always let the code use the
array sizes for the data...

Ubound(vData), 2) = 4 since it specifies the 2nd dim (#cols)

...because the range it holds spans cols A:D. You likely forgot that
loading a worksheet range into a variant results a 2D array.

I don't even use hard values for the source range because the data
resides in a dynamic range named "InputData" on every sheet. I didn't
want to take you there with this thread in case it went over your head,
but I do this...

vData = wks.Range("InputData")

...so the code is 100% reusable as well as better self-documenting.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Thursday, November 26, 2015 at 6:11:01 PM UTC-8, GS wrote:
On Thursday, November 26, 2015 at 5:03:00 PM UTC-8, GS wrote:
My bad! (aircode<g)

This..
lNextRow = .Cells(1, .Rows.Count).End(xlUp).Row + 1

..should be...

lNextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

--
Garry

Hi Garry,

Thanks, actually if I had studied the code harder, I should have seen
that.

I did have to make a change on this line for the Resize columns.

.Cells(lNextRow, 1).Resize(UBound(vData), 4) = vData

Works great now.

Thanks for the array approach, I like the array shot at it, but it
still whacks me most often.

Howard

Why did you hardcode the cols? The code uses the cols in vData, which
is 4 based on your range of A:D! You should always let the code use the
array sizes for the data...

Ubound(vData), 2) = 4 since it specifies the 2nd dim (#cols)

..because the range it holds spans cols A:D. You likely forgot that
loading a worksheet range into a variant results a 2D array.

I don't even use hard values for the source range because the data
resides in a dynamic range named "InputData" on every sheet. I didn't
want to take you there with this thread in case it went over your head,
but I do this...

vData = wks.Range("InputData")

..so the code is 100% reusable as well as better self-documenting.

HTH

--
Garry


Only changed it because that line erroed out, I tried 4 and it works with the 4.

Howard

Only changed it because that line erroed out, I tried 4 and it works
with the 4.

Something wrong there! I ran the code with dummy data and it works just
fine...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Thursday, November 26, 2015 at 6:28:10 PM UTC-8, GS wrote:
Only changed it because that line erroed out, I tried 4 and it works
with the 4.

Something wrong there! I ran the code with dummy data and it works just
fine...

--
Garry

What would I look for on the sheet/s or workbook?

Howard

On Thursday, November 26, 2015 at 6:28:10 PM UTC-8, GS wrote:
Only changed it because that line erroed out, I tried 4 and it
works with the 4.

Something wrong there! I ran the code with dummy data and it works
just fine...

--
Garry

What would I look for on the sheet/s or workbook?

Howard

Can't say for sure without looking at your file. If there's any reason
vData is not a 2D array then that line will definitely throw an error.
Try this on any sheet...


Sub test()
Dim v
v = Range("A1:D2")
Debug.Print "rows:=" & UBound(v)
Debug.Print "cols:=" & UBound(v, 2)
End Sub

...and it should return...

rows:=2
cols:=4

...in the IW. Note that the range doesn't need to contain any data.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Can't say for sure without looking at your file. If there's any reason
vData is not a 2D array then that line will definitely throw an error.
Try this on any sheet...


Sub test()
Dim v
v = Range("A1:D2")
Debug.Print "rows:=" & UBound(v)
Debug.Print "cols:=" & UBound(v, 2)
End Sub

..and it should return...

rows:=2
cols:=4

Garry

It returned 2 & 4 for every sheet, Summary & A to G, and the same for a new workbook made with same names and the data copied to each sheet.

I did notice while the .cells... line was yellow highlighted that the .End(xlDown) was showing -4162 when mouse over it.



vData = wks.Range("A2", wks.Range("D2").End(xlDown))

'Assign the array to the next row position
.Cells(lNextRow, 1).Resize(UBound(vData), UBound(vData), 2) = vData

Then another time it showed a date. Column D header is "Datum" and custom formatted to dd-mm-yyy.

I have no idea why it showed -4162 that one time.

Howard

Can't say for sure without looking at your file. If there's any
reason vData is not a 2D array then that line will definitely throw
an error. Try this on any sheet...


Sub test()
Dim v
v = Range("A1:D2")
Debug.Print "rows:=" & UBound(v)
Debug.Print "cols:=" & UBound(v, 2)
End Sub

..and it should return...

rows:=2
cols:=4

Garry

It returned 2 & 4 for every sheet, Summary & A to G, and the same
for a new workbook made with same names and the data copied to each
sheet.

I did notice while the .cells... line was yellow highlighted that the
.End(xlDown) was showing -4162 when mouse over it.



vData = wks.Range("A2", wks.Range("D2").End(xlDown))

'Assign the array to the next row position
.Cells(lNextRow, 1).Resize(UBound(vData), UBound(vData), 2) =
vData

Then another time it showed a date. Column D header is "Datum" and
custom formatted to dd-mm-yyy.

I have no idea why it showed -4162 that one time.

Howard

Have a look in the ObjectBrowser for XlDirection, then click each item
listed to see its value. Here's what you should see...

xlDown = -4121
xlToLeft = -4159
xlToRight = -4161
xlUp - -4162

Sounds like you might need to close/reopen Excel to see if these
anomolies go away!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Howard,

Am Thu, 26 Nov 2015 20:55:57 -0800 (PST) schrieb L. Howard:

you have a parenthis on the wron place

.Cells(lNextRow, 1).Resize(UBound(vData), UBound(vData), 2) = vData
.Cells(lNextRow, 1).Resize(UBound(vData), UBound(vData, 2)) = vData



Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Howard,

another suggestion:

Sub SheetsCopy()
Dim varData As Variant
Dim LRow As Long, i As Long

For i = 65 To 71
With Sheets(Chr(i))
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
varData = .Range("A2:D" & LRow)
Sheets("Summary").Cells(Rows.Count, 1).End(xlUp)(2) _
.Resize(UBound(varData), UBound(varData, 2)) = varData
End With
Next
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Howard,

Am Thu, 26 Nov 2015 20:55:57 -0800 (PST) schrieb L. Howard:

you have a parenthis on the wron place

.Cells(lNextRow, 1).Resize(UBound(vData), UBound(vData), 2) =
vData
.Cells(lNextRow, 1).Resize(UBound(vData), UBound(vData, 2)) = vData



Regards
Claus B.

..Ubound(vdata), 2) should be Ubound(vdata, 2))
Nice catch! Thank you, Claus<g

Sorry Howard, ..my bad!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Howard,

another suggestion:

Sub SheetsCopy()
Dim varData As Variant
Dim LRow As Long, i As Long

For i = 65 To 71
With Sheets(Chr(i))
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
varData = .Range("A2:D" & LRow)
Sheets("Summary").Cells(Rows.Count, 1).End(xlUp)(2) _
.Resize(UBound(varData), UBound(varData, 2)) = varData
End With
Next
End Sub


Regards
Claus B.

Absolutely the way to go with production code; -eliminates the need to
increment the next position! It was my intent, though, to explain the
logic in a step-by-step fashion...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Howard,

Am Fri, 27 Nov 2015 08:01:18 +0100 schrieb Claus Busch:

For i = 65 To 71

if you don't want to hardcode the ascii numbers and for more clarity and
logic you can change the code to:

Sub SheetsCopy()
Dim varData As Variant
Dim LRow As Long, i As Long

For i = Asc("A") To Asc("G")
With Sheets(Chr(i))
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
varData = .Range("A2:D" & LRow)
Sheets("Summary").Cells(Rows.Count, 1).End(xlUp)(2) _
.Resize(UBound(varData), UBound(varData, 2)) = varData
End With
Next
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Howard,

Am Fri, 27 Nov 2015 08:01:18 +0100 schrieb Claus Busch:

For i = 65 To 71

if you don't want to hardcode the ascii numbers and for more clarity
and logic you can change the code to:

Sub SheetsCopy()
Dim varData As Variant
Dim LRow As Long, i As Long

For i = Asc("A") To Asc("G")
With Sheets(Chr(i))
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
varData = .Range("A2:D" & LRow)
Sheets("Summary").Cells(Rows.Count, 1).End(xlUp)(2) _
.Resize(UBound(varData), UBound(varData, 2)) = varData
End With
Next
End Sub


Regards
Claus B.

I'm reading sheets A,B,C,D,E,F,G as being sheets 1,2,3,4,5,6,7!

So then using my reusable procedure...

Sub ConsolidateSheets()
' Consolidates data from all sheets into a Summary sheet
Dim vData, wks As Worksheet

For Each wks In ThisWorkbook.Sheets
If (wks.Name < "Summary") Then
'Load the data range into an array.
vData = wks.Range("InputData")
'Assign the array to the next row position
Sheets("Summary").Cells(.Rows.Count, 1).End(xlUp)(2) _
.Resize(UBound(vData), UBound(vData, 2)) = vData
End If
Next 'wks
End Sub

...needs only slight revision to exclude additional sheets. Typically,
though, I will store detail sheetnames in a constant...

In the declarations section of m_OpenClose (module):
Const gsDetailShts$ = "Wks1,Wks2"

Sub Consolidate_DetailShts()
' Consolidates data from detail sheets into a Summary sheet
Dim v
For each v in Split(gsDetailShts, ",")
vData = Sheets(v).Range("InputData")
Sheets("Summary").Cells(.Rows.Count, 1).End(xlUp)(2) _
.Resize(UBound(vData), UBound(vData, 2)) = vData
Next 'v
End Sub

...where the sheets to be consolidated are few in a multi-sheet wkb.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Sorry Howard, ..my bad!

--
Garry


Hi Garry and Claus,

Thanks for clearing that up, I thought my Excel version was cursed and was having a ton of fun messing with me! (I still think it is out to get me though.)

Garry, the array code is an excellent reference for me. And the trouble shooting was a pretty good learning thing too.

Claus, neat and clever with the ASCII numbers codes. There will actually be A to Z sheets, each with names beginning with that letter.

Howard

Hi Howard,

Am Fri, 27 Nov 2015 00:14:34 -0800 (PST) schrieb L. Howard:

Claus, neat and clever with the ASCII numbers codes. There will actually be A to Z sheets, each with names beginning with that letter.

the loop with the ASCII numbers only works for sheets with only this
character. If you loop from A to Z and look for Left(Sheet.Name,1) you
will include "Summary" and "Begin Blad"


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Howard,

Am Fri, 27 Nov 2015 00:14:34 -0800 (PST) schrieb L. Howard:

Claus, neat and clever with the ASCII numbers codes. There will
actually be A to Z sheets, each with names beginning with that
letter.

the loop with the ASCII numbers only works for sheets with only this
character. If you loop from A to Z and look for Left(Sheet.Name,1)
you will include "Summary" and "Begin Blad"


Regards
Claus B.

I think you mean 'exclude'?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Am Fri, 27 Nov 2015 03:26:09 -0500 schrieb GS:

I think you mean 'exclude'?

Howard wrote that the sheets have names *beginning* with A to Z.
Then he cannot work with ASCII numbers because he had to look for
Left(Sheet.Name,1) and that would INCLUDE the sheets SUMMARY and BEGIN
BLADS.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Garry,

Am Fri, 27 Nov 2015 03:26:09 -0500 schrieb GS:

I think you mean 'exclude'?

Howard wrote that the sheets have names *beginning* with A to Z.
Then he cannot work with ASCII numbers because he had to look for
Left(Sheet.Name,1) and that would INCLUDE the sheets SUMMARY and
BEGIN BLADS.


Regards
Claus B.

Well yes, but I thought these 2 sheets were excluded in the If..Then!
So what I meant is those 2 sheets still need to be excluded. Your
statement that they would be included is accurate and so I was thinking
you meant to add they need to be excluded. Regardless, I prefer
explicit inclusion as exampled in Consolidate_DetailShts() -OR-
explicit exclusion as exampled in ConsolidateSheets().<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

BTW, it 3:40am here and so I need to 'bag some Zs' before doing much
more...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Am Fri, 27 Nov 2015 03:41:27 -0500 schrieb GS:

BTW, it 3:40am here and so I need to 'bag some Zs' before doing much
more...

then it is time to go to bed. Here it is 9:45 am.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi all,

The sheets are single letter A to G on sample workbook, A to Z in real workbook.

I did not submit the code: For i = Asc("A") To Asc("G").

Another question, please.

Summary sheet has a Table, which seems to me to be column A. I can go to name box and Table1 is listed.

If I click on Table1 it hi-lites column A.

All columns are filtered (has the drop down arrows).

Are there common rules on how to copy to a table as we are here. I anticipate some problems here. If you delete the data in the table/filtered area, the next copy goes to the first empty row below the table. That could be 50 + rows down.

If you delete all the data filled ROWS of the table, then you have the Headers and a empty 1st row of the table, and the copy goes to the first row below that empty table row.

Perhaps, the table and filter should be "Turned Off" and old data deleted, to leave a row of Headers, then do the copy, then reinstate the table and filter???

Howard

Hi Howard,

Am Fri, 27 Nov 2015 01:10:34 -0800 (PST) schrieb L. Howard:

If I click on Table1 it hi-lites column A.

All columns are filtered (has the drop down arrows).

I don't know exactly how it is named in the english Version. But try:
Right clikc to column A = Table = Convert to Range


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi all,

The sheets are single letter A to G on sample workbook, A to Z in
real workbook.

I did not submit the code: For i = Asc("A") To Asc("G").

Another question, please.

Summary sheet has a Table, which seems to me to be column A. I can
go to name box and Table1 is listed.

If I click on Table1 it hi-lites column A.

All columns are filtered (has the drop down arrows).

Are there common rules on how to copy to a table as we are here. I
anticipate some problems here. If you delete the data in the
table/filtered area, the next copy goes to the first empty row below
the table. That could be 50 + rows down.

If you delete all the data filled ROWS of the table, then you have
the Headers and a empty 1st row of the table, and the copy goes to
the first row below that empty table row.

Perhaps, the table and filter should be "Turned Off" and old data
deleted, to leave a row of Headers, then do the copy, then reinstate
the table and filter???

Howard

The *Table object* is problematic for coding (IMO) because this object
has its own rules, and is primarily used for data analysis scenarios,
not for storing raw data. You have to delete the table to remove it.

When writing new data to a summary sheet, filters should be turned off
so inbound data is correctly positioned. Filtering shouldn't really
matter since the rows are contiguous in col1 (or should be) since the
1st col usually contains the *PrimaryKey* in database tables. (I think
it's a good practice to follow the rules<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

Hi all,

The sheets are single letter A to G on sample workbook, A to Z in
real workbook.

I did not submit the code: For i = Asc("A") To Asc("G").

Another question, please.

Summary sheet has a Table, which seems to me to be column A. I can
go to name box and Table1 is listed.

If I click on Table1 it hi-lites column A.

All columns are filtered (has the drop down arrows).

Are there common rules on how to copy to a table as we are here. I
anticipate some problems here. If you delete the data in the
table/filtered area, the next copy goes to the first empty row
below the table. That could be 50 + rows down.

If you delete all the data filled ROWS of the table, then you have
the Headers and a empty 1st row of the table, and the copy goes to
the first row below that empty table row.

Perhaps, the table and filter should be "Turned Off" and old data
deleted, to leave a row of Headers, then do the copy, then
reinstate the table and filter???
Howard

The *Table object* is problematic for coding (IMO) because this
object has its own rules, and is primarily used for data analysis
scenarios, not for storing raw data. You have to delete the table to
remove it.

When writing new data to a summary sheet, filters should be turned
off so inbound data is correctly positioned. Filtering shouldn't
really matter since the rows are contiguous in col1 (or should be)
since the 1st col usually contains the *PrimaryKey* in database
tables. (I think it's a good practice to follow the rules<g)

Claus' suggestion is, as I recall, the way to delete a table object so
it's just a range table of data.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I don't know exactly how it is named in the english Version. But try:
Right clikc to column A = Table = Convert to Range


Regards
Claus B.

Hi Claus, yes, its the same on English version, but the macro recorder does not record that action, strange I think.

Howard

The *Table object* is problematic for coding (IMO) because this object
has its own rules, and is primarily used for data analysis scenarios,
not for storing raw data. You have to delete the table to remove it.

When writing new data to a summary sheet, filters should be turned off
so inbound data is correctly positioned. Filtering shouldn't really
matter since the rows are contiguous in col1 (or should be) since the
1st col usually contains the *PrimaryKey* in database tables. (I think
it's a good practice to follow the rules<g)

--
Garry


Hi Garry,

This gives me sound advice to pass on if the OP has trouble copying to his table. Basically, turn off the filter, and copy to a range NOT a table.

Reinstate filters/tables as needed with the new data.

Thanks

Howard

I don't know exactly how it is named in the english Version. But
try: Right clikc to column A = Table = Convert to Range


Regards
Claus B.

Hi Claus, yes, its the same on English version, but the macro
recorder does not record that action, strange I think.

Howard

That's because most (if not all) of the Table object is not exposed to
VBA, thus the problem with coding it.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Howard,

Am Fri, 27 Nov 2015 01:43:23 -0800 (PST) schrieb L. Howard:

Hi Claus, yes, its the same on English version, but the macro recorder does not record that action, strange I think.

do it this way:

Dim i As Integer
With Sheets("Summary")
For i = 1 To .ListObjects.Count
.ListObjects(1).Unlist
Next
End With


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

The *Table object* is problematic for coding (IMO) because this
object has its own rules, and is primarily used for data analysis
scenarios, not for storing raw data. You have to delete the table
to remove it.

When writing new data to a summary sheet, filters should be turned
off so inbound data is correctly positioned. Filtering shouldn't
really matter since the rows are contiguous in col1 (or should be)
since the 1st col usually contains the *PrimaryKey* in database
tables. (I think it's a good practice to follow the rules<g)

--
Garry


Hi Garry,

This gives me sound advice to pass on if the OP has trouble copying
to his table. Basically, turn off the filter, and copy to a range
NOT a table.

Reinstate filters/tables as needed with the new data.

Thanks

Howard

It could also be that Table1 is just a DefinedName given to colA, so
look there before drawing any conclusions...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Howard,

Am Fri, 27 Nov 2015 10:51:13 +0100 schrieb Claus Busch:

sorry typo:

.ListObjects(1).Unlist
.ListObjects(i).Unlist


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Yes, that's one way to access a Table object by code...

.ListObjects(1).Unlist

...but I think you meant...

.ListObjects(i).Unlist

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

do it this way:

Dim i As Integer
With Sheets("Summary")
For i = 1 To .ListObjects.Count
.ListObjects(1).Unlist
Next
End With


Regards
Claus B.

Hi Claus,

Thanks, got it, (typo noted).

Thanks,
Howard