Excel VBA copy format between workbooks


  • Can anyone help work out how to copy the format (column width, colour,...) from a range in one Excel .xls to an equal range in another .xls.  Currently using the following format


    Worksheets("file1.xls!sheet").Range("A1:IL36").Copy _


    Works among sheets within one .xls but does not copy format, only contents



    Thursday, July 19, 2007 2:09 PM

All replies

  • To copy the column widths I think you need to copy the entire column and then select the entire column in the destination workbook and do a PasteSpecial.

    Something like

    Sub Test()

    'copies the range and its formats
    Workbooks("Destination.xls").Sheets("Sheet1").Range("A1:C40").PasteSpecial (xlPasteAll)

    'copies the column widths and their formats
    Workbooks("Destination.xls").Sheets("Sheet1").Range("A:C").PasteSpecial (xlPasteFormats)

    End Sub

    Thursday, July 19, 2007 3:57 PM
  • Thanks very much,


         The code seems right but now I get my old error of "Run-time error '9': Subscript out of range".  I have used other code to attempt the same function with the same results.



    Thursday, July 19, 2007 4:43 PM
  • In your code you refer to workbooks and sheets in a way I have not seen.



    Make sure you include the .xls after the filename or you can get errors.
    Thursday, July 19, 2007 5:29 PM