none
Excel VBA copy format between workbooks

    Question

  • 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 _
            Destination:=Worksheets("file2.xls!sheet").Range("A1:IL36")

     

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

     

    r.con

    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("Source.xls").Sheets("Sheet1").Range("A1:C40").Copy
    Workbooks("Destination.xls").Sheets("Sheet1").Range("A1:C40").PasteSpecial (xlPasteAll)

    'copies the column widths and their formats
    Workbooks("Source.xls").Sheets("Sheet1").Range("A:C").Copy
    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.

     

    Rodcon

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

    Try:

    Workbooks("Source.xls").Sheets("Sheet1")...

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