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

 

[Smitty]

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("G:G, I:I")<br>        <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Column<br>                <SPAN style="color:#00007F">Case</SPAN> 7<br>                    ActiveSheet.Unprotect "123"<br>                        Range("H13").Copy Cells(Target.Row, "H")<br>                    ActiveSheet.Protect "123"<br>                <SPAN style="color:#00007F">Case</SPAN> 9<br>                    ActiveSheet.Unprotect "123"<br>                        Range("J13:K13").Copy Cells(Target.Row, "J")<br>                        Range("M13").Copy Cells(Target.Row, "M")<br>                    ActiveSheet.Protect "123"<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[mattbird]

That's working well thanks. Do I still need to press F9 and the F8 or just Alt Q?

 

[Smitty]

If the code does what you want, then just remove the breakpoints, and exit back to Excel. From there it will all respond automatically.

 

[mattbird]

That's working thanks. I have another question: if I was to link a cell from one workbook to another say for instance Cell G17 (Weight), this would then show on the other workbook, is there away of allowing the other person to change the same cells data which would then show on my workbook? or can it only be done one way?

 

[Smitty]

It "can" be done both ways, but you'd need to do something to write the data back to the other workbook. Doable yes, practical no.

If you're really looking for a platform to allow multiple users to collaborate and collect data, then you might want to look at Access.

 

[mattbird]

The only issue is that I don't have Access and neither does the system I will put the table on I only have Excel. I asked because this would allow me to have a master workbook I could control the table with and have the ability to enter and receive data in stead of having to go into all workbook to add new data, which would make it easier to control. How would I go about doing it?

 

[mattbird]

Having an issue with the code I saved it last night and re-opened it to day and it doesn't copy the code any more, I tried re-entering the code but nothing, so started on a previous saved copy (before code entered) and it worked again so I saved it took out my USB and then re entered it and loaded the template up and its not copying the code just tabs to the next data input cell? whys it doing this?

 

[Smitty]

Have you stepped through the code to see if it's firing?

It's may possible that you turned off Events, which you can check in the Immediate window with ?Application.EnableEvents

As for you earlier question you could use MS Query to pull data from other workbooks to consolidate it.

 

[mattbird]

Do I have to add Application.EnableEvents to the bottom of the code?

 

[Smitty]

You only need to turn events back on if you've turned them off. Did you try the Immediate Window test?