Vba copy paste keep all formula format column width in Excel
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: