Overview: This is just a short document on how to use a Macro to ‘clone’ the column widths of a Pivot Table or Table Chart. This macro can save a lot of time if you are like me and want you columns tidy, but hate trying to do this with a mouse. The attached document has the content below, and additional screenshots.
Step-by-Step To implement (it is easy):
Step 1: Create a new Input Box titled vChartSource
New Sheet Object -> InputBox
Step 2: Select New Variable (red box above) and name the variable vChartSource
Step 3: Create a new Input Box titled vChartTarget
Step 4: Select New Variable (red box above) and name the variable vChartTarget
Step 5: Create a new Button titled ‘Set Column Width V2’
New Sheet Object -> Button
Step 6: Select the Actions Tab and Click Edit Module
Step 7: Copy and Paste in the code below, and press OK
SUB SetColumnWidthsV2
Dim vChartTarget 'as String
Dim vChartSource 'as String
vChartTarget = ActiveDocument.Variables("vChartTarget").GetContent().String
vChartSource = ActiveDocument.Variables("vChartSource").GetContent().String
set chartTarget = ActiveDocument.GetSheetObject(vChartTarget)
set chartSource = ActiveDocument.GetSheetObject(vChartSource)
pwidths = chartSource.GetPixWidths(1,1).PixWidths
chartTarget.SetPixWidth 1, pwidths(0)
END SUB
Step 8: Pick ‘SetColumnWidthsV2’ in the Macro Name Drop down box and press OK
Step 9: You are now ready to go, all you need is to enter the Object IDs of your source and target tables and press the button. The column widths will automatically adjust.
