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

 

[mattbird]

Hi,

I have re-pasted the code (ALT+F11):

PrivateSub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("C:C")
' Only look at single cell changes
If Target.Count > 1 ThenExitSub
' Only look at that range
If Intersect(Target, rng) IsNothingThenExitSub
' Action if Condition(s) are met (do your thing here...)
Range("D2:I2").Copy Target.Offset(, 1)
EndSub

I then put the cursor at the start of the 'Range("D2:I2").Copy Target.Offset(, 1)' and pressed F9 which put a maroon dot in front of the line as well as highlighting the 'Range("D2:I2").Copy Target.Offset(, 1)' in maroon. I then m ade an entry in column C the text stayed black? I have copied my full table below which starts at cell A to cell N. The info I need to be copied to a new row are in row 17 column H, J, K and M.

Department No

Nick Name

Name

Department

Gender

Height (cm)

Weight (Kg)

BMI

WC (cm)

BMI Catergory

Overall Health Risk

Assessment Date

Review Date

Remarks

#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!

<colgroup><col><col><col span="3"><col span="2"><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

I think the code is not working as the code you gave me relates to the first table I inserted which was only some of the table. the table above is the full table. Other than that I think I was imputing it in the right way? Your help is greatly appreciated.

 

[Andrew Poulsom]

Right click the worksheet tab and choose View code. Paste the code into the window on the right.

 

[Smitty]

I think the code is not working as the code you gave me relates to the first table I inserted which was only some of the table.

As for the code not working, is it in the right place? (See Andrew's comments)

With regards to a different table range than you original posted, then you'll need to adjust the code accordingly:

PrivateSub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range, i.e. Range("A, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("G:G")
' Only look at single cell changes
If Target.Count > 1 ThenExitSub
' Only look at that range
If Intersect(Target, rng) IsNothingThenExitSub
' Action if Condition(s) are met (do your thing here...)
Range("H17:M17").Copy Target.Offset(, 1)
End Sub

Where entries in column G will now trigger the copy/paste.

 

[mattbird]

I have pasted the code into the window on the right.

 

[mattbird]

I have pasted code into the window on the right, selected Range("H17:M17").Copy Target.Offset(, 1) and pressed F9 which highlighted (maroon) the line and placed a dot next it. I have then gone back to the worksheet and typed a number in cell G17 and then inserted a new row which appears above instead of below but still the new row has not copied its blank. I must be doing something wrong.

 

[Smitty]

Were there any formulas in row 17? If not, then all you'd copy is a blank row. Note that my original code referenced row 2 to copy, which one would assume should always have the formulas you wanted.

Also, the Change event will only respond to an entry in column G, it won't detect a row insertion.

 

[mattbird]

Yes there are formulas in Cell H17, J17, K17 and M17. Is it suppose to copy these formulas to the row below ie row 18 once I have entered a figure in G17?

 

[Smitty]

Yes there are formulas in Cell H17, J17, K17 and M17. Is it suppose to copy these formulas to the row below ie row 18 once I have entered a figure in G17?

No, the way it's set up now is if you enter something in column G, the H17:M17 will be copied to H17:M17. So if you enter something in G17, then H17:M17 would be copied to H17:M17. Like I said, that's why I went with row 2 originally.

 

[mattbird]

What I would like it to do is once I have filled out each of the columns in row 17 it automatic sets up row 18 with the same formulas so all I have to do is enter the persons info and data and the same for each row after. Otherwise I have to copy the cells with the formulas in and past them into the next rows every time I enter a new persons data. Hopefully I am not confusing you?

 

[Smitty]

Then just add a row to the Offset:

Range("H17:M17").Copy Target.Offset(1, 1)

Now if you make a change in G17, H17:M17 will be copied to G18. But that's assuming that you have formulas in row 17 to copy.

You might also want to add:

Target.Offset(1,-6).Select

To take you to A18.