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.

excel vba copy paste keep all formula format column width

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub CopyAllAndColumnWidths()
  3. ''
  4. ActiveSheet.Range("Y2:AE14").Copy
  5. ActiveSheet.Range("AG2").PasteSpecial Paste:=xlPasteAll
  6. ActiveSheet.Range("AG2").PasteSpecial Paste:=xlPasteColumnWidths
  7. Application.CutCopyMode = False
  8. ''
  9. 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:

excel excel vba copy paste keep all formula format column width


You can find similar Excel Questions and Answer hereunder

1) How can I set non-contiguous print area using VBA?

2) Millions thousands custom number formatting in Excel

3) Vba to return week numbers in Excel

4) How to change desktop background in Excel

5) How can I export a chart as a gif file?

6) Concatenate number with text but keeping in number format in Excel

7) Determine if hyperlinks are valid in Excel

8) How can I get users to select a file for processing using my macro?

9) How can I hide a sheet completely from users (the sheet should not even appear in Unhide dialog box)?

10) How can I copy and rename a WorkSheet using VBA?

11) Formula does not calculate in Excel

12) How do I add a shape using VBA?

13) How can I sort data using VBA?

14) How can I get the count of number of series in a Chart using VBA?

15) How do I enter a formula in a cell using VBA (using Relative Reference)?