vba copy paste keep all formula format column width in Excel
Q161. In Excel, how do I copy a Table from one location to another and retain all formulas, formats and columnwidths?
For example, I have a table with formulas and formatting as below - I want to duplicate this table in another location without losing formulas, formatting and columnwidths.
To do it in Excel, here is the answer:
- Option Explicit
- Sub CopyAllAndColumnWidths()
- ActiveSheet.Range("AG2").PasteSpecial Paste:=xlPasteAll
- ActiveSheet.Range("AG2").PasteSpecial Paste:=xlPasteColumnWidths
- Application.CutCopyMode = False
- End Sub
a) Line 5 is used to copy all the contents of source data range to destination along with formats and formulas.
b) Line 6 is used to apply source column widths to destination.
c) Line 7 is to clear the Clipboard.
Result after Macro execution: