Having trouble sharing Excel tables with other workbooks?

[mattbird]

Hi,

Is there anyone how knows how to share an Excel table? I have a table which I would like to share with other workbooks but when I click the share workbook it says:

'This workbook can not be shared because it contains Excel tables or XML maps. To share this workbook, convert the table ranges and remove the XML maps.'

I would like to use the Excel table as it automatically transfer formulas to the next row, if I convert the table range, I will have to copy and past my formulas into a new row every time I add one. I am not sure what XML maps are?

I am currently using Excel 2007 on Win8.

Please could someone help......

Matt

 

[RoryA]

FWIW, this

Set rng = Target.Parent.Range("G:G")

can just be this

Set rng = Range("G:G")

since that's equivalent to Me.Range("G:G")

 

[Smitty]

FWIW, this

Set rng = Target.Parent.Range("G:G")

can just be this

Set rng = Range("G:G")

since that's equivalent to Me.Range("G:G")

Yeah, I used to do that, but I've had it that way for years ever since Tom Urtis suggested it.

 

[RoryA]

Can I ask why? I don't see any benefit - am I missing something?

 

[Smitty]

Can I ask why? I don't see any benefit - am I missing something?

I think the way he explained it was that it was a more explicit reference, which I thought was overkill since the Change event is already referring to the target sheet. I'll see if I can dig up the post where he gave the justification, but it's been years (it was here though).

 

[mattbird]

Still noting is happening I think I have followed your instruction correctly but unsure now? Can I send you a copy of my table so you can see it for yourself? if so can you give me the link to copy it to? Thanks

Matt

 

[mattbird]

Smitty,

did you received my email with the table attached? If not let me know and I will have to re-send it.

 

[Smitty]

Check your e-mail.

 

[mattbird]

Hi,

I think it is working. I'm going to have a mess around filling in the table and I will let you know. Thanks for you help so far. One question, when I protected the sheet it says I cant use the copy code how do I protect the cells that I don't want others to click on?

 

[Smitty]

You can add this to the existing code:

Activesheet.Unprotect "PasswordGoesHere"
' Code
Activesheet.Protect "PasswordGoesHere"

You might want to post the code as it stands now, just in case someone else finds it helpful.

 

[mattbird]

I have added it to the bottom of the existing code. but it still stops the cell from copying if I protect the sheet first. If I fill out the sheet whilst unprotected it then protects the sheet after column M has data in it, but the next row when I fill it out it comes up with the same message as before and won't copy the cells. Where about's is the best place to place the new code in the existing code?