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("Y2:AE14").Copy
- ActiveSheet.Range("AG2").PasteSpecial Paste:=xlPasteAll
- ActiveSheet.Range("AG2").PasteSpecial Paste:=xlPasteColumnWidths
- Application.CutCopyMode = False
- ''
- End Sub
Description:
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:
You can find similar Excel Questions and Answer hereunder
1) How do I add a symbol like Triangle / Inverted Triangle for indicating trends in a cell using VBA?
2) How can I hide Formula Bar and Headings using VBA?
3) Import txt file in Excel
4) How can I set up ListBox using VBA to allow users to select multiple values?
5) How to copy files in Excel VBA is explained here
6) How to rename multiple sheets easily with VBA
7) I have a macro that takes a lot of time for execution - how can I keep the user informed that the macro is running?
8) How can I loop through all WorkSheets and get their names using VBA?
9) How can I set the fill color, font color and set number format of cell to date?
10) How can I loop through all ActiveX checkboxes in WorkSheet and set them to Unchecked status?