copy custom tablestyle into new workbook

[fb7894]

I can't figure out how to import a custom table style from Workbook A into Workbook B with VBA code??

[p45cal]

I don't think there's a built-in way of doing this. You can work around this though and copy one tablestyle with this macro (see comments in the code too):
Sub blah()
Set SourceWbk = Workbooks("Book3.xlsm") 'or the next line, or some such:
'Set SourceWbk = ThisWorkbook '(the workbook that the code is in.)
Set DestnWbk = Workbooks("Book4.xlsx") 'adjust to suit.

Set TempSht = DestnWbk.Sheets.Add
With SourceWbk.Sheets.Add
With .ListObjects.Add
.TableStyle = "my Table Style 777" '<<< use your style name here.
.Range.Copy TempSht.Cells(1)
End With
Application.DisplayAlerts = False
TempSht.Delete
.Delete
Application.DisplayAlerts = True
End With
End Sub
This just adds a new sheet temporarily to the workbook with the tablestyle you want to copy from, adds a table to it, applies your custom tablestyle, then copies that table to a newly created temporary sheet in the destination workbook, then promptly deletes both temporary sheets. You're left with that custom tablestyle in the destination workbook.

[mdmackillop]

Nice workaround.

[snb]

I don't see any problem in:


Sub M_snb()
Workbooks(3).Sheets(1).ListObjects(1).TableStyle = Workbooks(2).TableStyles("snb")
End Sub

Nor in:


Sub M_snb()
Workbooks(3).TableStyles.Add Workbooks(2).TableStyles("snb")
End Sub

[p45cal]

I don't see any problem in:…except they don't seem to work over here. (The name seems to move across (the Tablestyle.name is the new name), but not the formats etc., that is, there is no change in the appearance of the table, and it's not listed among the styles in the destination workbook.)

[snb]

It's producing the desired result in Excel 2010.


Sub M_snb()
ThisWorkbook.Sheets(1).ListObjects(1).TableStyle = "snb"

With Workbooks.Add
ThisWorkbook.Sheets(1).ListObjects(1).Range.Copy .Sheets(1).Cells(1)
.TableStyles("snb").ShowAsAvailableTableStyle = True
End With
End Sub

[p45cal]

In Excel 2013:
Re. msg#4: Your first snippet, translated to:
Workbooks("Book4.xlsx").Sheets(1).ListObjects(1).TableStyle = Workbooks("Book3.xlsm").TableStyles("my Table Style 777")
seems to copy the cell formats OK, but there is no addition of TableStyles to the workbook collection.

Your second snippet,translated to:
Workbooks("Book4.xlsx").TableStyles.Add Workbooks("Book3.xlsm").TableStyles("my Table Style 777")
seems to add a new TableStyle to the TableStyles collection, with the new name, but it has no formatting.
If the 2nd snippet is followed by:
Workbooks("Book4.xlsx").TableStyles("my Table Style 777").ShowAsAvailableTableStyle = Trueit shows up in the list of tablestyles, but again, no formatting is within it.

So far I haven't been able to make up a combination of the two snippets to produce what the OP wanted.

Edit post posting: didn't see you amended your post, looking at it again now.

[p45cal]

It's producing the desired result in Excel 2010.


Sub M_snb()
ThisWorkbook.Sheets(1).ListObjects(1).TableStyle = "snb"

With Workbooks.Add
ThisWorkbook.Sheets(1).ListObjects(1).Range.Copy .Sheets(1).Cells(1)
.TableStyles("snb").ShowAsAvailableTableStyle = True
End With
End Sub
That's not the same! Your:

ThisWorkbook.Sheets(1).ListObjects(1).Range.Copy .Sheets(1).Cells(1)is miles away from either your earlier:
Workbooks(3).Sheets(1).ListObjects(1).TableStyle = Workbooks(2).TableStyles("snb") or your:
Workbooks(3).TableStyles.Add Workbooks(2).TableStyles("snb")and very close to my:
.Range.Copy TempSht.Cells(1)

ps. by copying the table you don't need the .ShowAsAvailableTableStyle line.