Overwriting a table with another query
 

Hi there,
So I am receiving this error:
'1004' A table cannot overlap a range that contains another table...

I am creating a vb macro to obtain data and write this data to different
specific sheets ( which may already contain data )

I am importing data from microsoft query. The table is then created from
this data, and is dynamic in size, tho when data exists, it doesnt seem to
work.

Im assuming the best way to get around this error, is to delete the table
that exists, if there is one. Is there some specific VB code that will allow
me to do this? Any help is greatly appreciated! Thanks!

- jacques

Overwriting a table with another query
 

For Each itm In ActiveSheet.Names

itm.Delete

Next itm

"Jacques Q" wrote:

Hi there,
So I am receiving this error:
'1004' A table cannot overlap a range that contains another table...

I am creating a vb macro to obtain data and write this data to different
specific sheets ( which may already contain data )

I am importing data from microsoft query. The table is then created from
this data, and is dynamic in size, tho when data exists, it doesnt seem to
work.

Im assuming the best way to get around this error, is to delete the table
that exists, if there is one. Is there some specific VB code that will allow
me to do this? Any help is greatly appreciated! Thanks!

- jacques

Overwriting a table with another query
 

Ok so here is my actual code.

Sheets("RickSales").Select

For Each itm In ActiveSheet.Names
itm.delete
Next itm

It runs without error, tho it still doesnt seem to delete anything, i.e find
any items in the ActiveSheet, any other ideas? Thanks again!

- Jacques


"joel" wrote:

For Each itm In ActiveSheet.Names

itm.Delete

Next itm

"Jacques Q" wrote:

Hi there,
So I am receiving this error:
'1004' A table cannot overlap a range that contains another table...

I am creating a vb macro to obtain data and write this data to different
specific sheets ( which may already contain data )

I am importing data from microsoft query. The table is then created from
this data, and is dynamic in size, tho when data exists, it doesnt seem to
work.

Im assuming the best way to get around this error, is to delete the table
that exists, if there is one. Is there some specific VB code that will allow
me to do this? Any help is greatly appreciated! Thanks!

- jacques

Overwriting a table with another query
 

hi
it's not finding items ON the sheets. it's finding named ranges and deleting
them.
excel considers microsoft query as a named range.
do you still have named ranges in the name box? or in the define name dialog?
can you run your original code without error now?

i ran the code on a test file. worked.

Regards
FSt1


"Jacques Q" wrote:

Ok so here is my actual code.

Sheets("RickSales").Select

For Each itm In ActiveSheet.Names
itm.delete
Next itm

It runs without error, tho it still doesnt seem to delete anything, i.e find
any items in the ActiveSheet, any other ideas? Thanks again!

- Jacques


"joel" wrote:

For Each itm In ActiveSheet.Names

itm.Delete

Next itm

"Jacques Q" wrote:

Hi there,
So I am receiving this error:
'1004' A table cannot overlap a range that contains another table...

I am creating a vb macro to obtain data and write this data to different
specific sheets ( which may already contain data )

I am importing data from microsoft query. The table is then created from
this data, and is dynamic in size, tho when data exists, it doesnt seem to
work.

Im assuming the best way to get around this error, is to delete the table
that exists, if there is one. Is there some specific VB code that will allow
me to do this? Any help is greatly appreciated! Thanks!

- jacques

Overwriting a table with another query
 

First, thanks for the quick replies!
On another note, is it relevant if im using excel 2007?
The named ranges are still there in the name box, and all the tables are
still on the sheet. Still runs without error. Again I appreciate any other
ideas, thanks!

- jacques

"FSt1" wrote:

hi
it's not finding items ON the sheets. it's finding named ranges and deleting
them.
excel considers microsoft query as a named range.
do you still have named ranges in the name box? or in the define name dialog?
can you run your original code without error now?

i ran the code on a test file. worked.

Regards
FSt1


"Jacques Q" wrote:

Ok so here is my actual code.

Sheets("RickSales").Select

For Each itm In ActiveSheet.Names
itm.delete
Next itm

It runs without error, tho it still doesnt seem to delete anything, i.e find
any items in the ActiveSheet, any other ideas? Thanks again!

- Jacques


"joel" wrote:

For Each itm In ActiveSheet.Names

itm.Delete

Next itm

"Jacques Q" wrote:

Hi there,
So I am receiving this error:
'1004' A table cannot overlap a range that contains another table...

I am creating a vb macro to obtain data and write this data to different
specific sheets ( which may already contain data )

I am importing data from microsoft query. The table is then created from
this data, and is dynamic in size, tho when data exists, it doesnt seem to
work.

Im assuming the best way to get around this error, is to delete the table
that exists, if there is one. Is there some specific VB code that will allow
me to do this? Any help is greatly appreciated! Thanks!

- jacques

Overwriting a table with another query
 

Make sure the correct page is active. You can replace activesheet with
Sheets("RickSales"). Make sure you don't have any other workbooks opened.
Check if you have any megeed cells. highlight the entire worksheet and go to
format - Cells - alignment and make sure merged cells isn't checked.

For Each itm In Sheets("RickSales").Names
itm.delete
Next itm


"Jacques Q" wrote:

First, thanks for the quick replies!
On another note, is it relevant if im using excel 2007?
The named ranges are still there in the name box, and all the tables are
still on the sheet. Still runs without error. Again I appreciate any other
ideas, thanks!

- jacques

"FSt1" wrote:

hi
it's not finding items ON the sheets. it's finding named ranges and deleting
them.
excel considers microsoft query as a named range.
do you still have named ranges in the name box? or in the define name dialog?
can you run your original code without error now?

i ran the code on a test file. worked.

Regards
FSt1


"Jacques Q" wrote:

Ok so here is my actual code.

Sheets("RickSales").Select

For Each itm In ActiveSheet.Names
itm.delete
Next itm

It runs without error, tho it still doesnt seem to delete anything, i.e find
any items in the ActiveSheet, any other ideas? Thanks again!

- Jacques


"joel" wrote:

For Each itm In ActiveSheet.Names

itm.Delete

Next itm

"Jacques Q" wrote:

Hi there,
So I am receiving this error:
'1004' A table cannot overlap a range that contains another table...

I am creating a vb macro to obtain data and write this data to different
specific sheets ( which may already contain data )

I am importing data from microsoft query. The table is then created from
this data, and is dynamic in size, tho when data exists, it doesnt seem to
work.

Im assuming the best way to get around this error, is to delete the table
that exists, if there is one. Is there some specific VB code that will allow
me to do this? Any help is greatly appreciated! Thanks!

- jacques

Overwriting a table with another query
 

Still no luck, no merged cells. Macro still runs, but still does nothing.

"joel" wrote:

Make sure the correct page is active. You can replace activesheet with
Sheets("RickSales"). Make sure you don't have any other workbooks opened.
Check if you have any megeed cells. highlight the entire worksheet and go to
format - Cells - alignment and make sure merged cells isn't checked.

For Each itm In Sheets("RickSales").Names
itm.delete
Next itm


"Jacques Q" wrote:

First, thanks for the quick replies!
On another note, is it relevant if im using excel 2007?
The named ranges are still there in the name box, and all the tables are
still on the sheet. Still runs without error. Again I appreciate any other
ideas, thanks!

- jacques

"FSt1" wrote:

hi
it's not finding items ON the sheets. it's finding named ranges and deleting
them.
excel considers microsoft query as a named range.
do you still have named ranges in the name box? or in the define name dialog?
can you run your original code without error now?

i ran the code on a test file. worked.

Regards
FSt1


"Jacques Q" wrote:

Ok so here is my actual code.

Sheets("RickSales").Select

For Each itm In ActiveSheet.Names
itm.delete
Next itm

It runs without error, tho it still doesnt seem to delete anything, i.e find
any items in the ActiveSheet, any other ideas? Thanks again!

- Jacques


"joel" wrote:

For Each itm In ActiveSheet.Names

itm.Delete

Next itm

"Jacques Q" wrote:

Hi there,
So I am receiving this error:
'1004' A table cannot overlap a range that contains another table...

I am creating a vb macro to obtain data and write this data to different
specific sheets ( which may already contain data )

I am importing data from microsoft query. The table is then created from
this data, and is dynamic in size, tho when data exists, it doesnt seem to
work.

Im assuming the best way to get around this error, is to delete the table
that exists, if there is one. Is there some specific VB code that will allow
me to do this? Any help is greatly appreciated! Thanks!

- jacques