◆テンプレートシートの書式と列幅を別のシートにコピーする

  VBAでアプリケーションを作成するときに、作成する表の書式をテンプレートから
  コピーできるようにしておくと、一層汎用性を高めることができます。

  テンプレートシートから書式をコピーする方法の説明です。

Sub test1()

    Dim i As Integer
    
    'Excel 2002以上ではこんなことをしなくても
    'xlPasteColumnWidths の定数がそのまま使えます
    Const pxlPasteColumnWidths = 8
    
    'C列からH列目までの書式のCopy
    For i = 3 To 8
        'シート1の選択
        Sheets(1).Select
    
        'i列の2行目のセルをCopy
        Cells(2, i).Select
        Selection.Copy
    
        'シート2の選択
        Sheets(2).Select
    
        'i列全体を選択して書式だけCopy
        Columns(i).Select
        Selection.PasteSpecial Paste:=xlPasteFormats
        
        '幅だけCopy
        Selection.PasteSpecial Paste:=pxlPasteColumnWidths
        
        'Copyモードの解除
        Application.CutCopyMode = False
    
    Next

End Sub

  上記はテンプレートシート( Sheets(1)) の Range("C2")からRange("H2")までの
  書式と列幅を Sheets(2) にコピーするマクロです。

  Copyメソッドはクリップボードを使用しています。そのため、アクティブなシートの
  コピーしかできないので、その都度シートを変更してやる必要があります。

  この方法は画面がめまぐるしく変わり、スピードも遅くなります。

  PasteSpecial は、エクセルのシートを右クリックしたときに表示される
  「形式を選択して張り付ける」に当たります。




  もっとスマートな方法を説明します。

Sub test2()

    Dim i As Integer
    
    'シート2の選択
    Sheets(2).Select
    
    'C列からH列目までの書式のCopy
    For i = 3 To 8
    
        'i列全体を選択して書式だけCopy
        Columns(i).Select
        
        '書式をCopy
        Paste_Format 2, i
    
    Next

End Sub

Sub Paste_Format(xRow, xCol)

  'セルの表示形式の設定
  Selection.NumberFormatLocal = Sheets(1).Cells(xRow, xCol).NumberFormatLocal

  '列の幅の設定
  Selection.ColumnWidth = Sheets(1).Cells(xRow, xCol).ColumnWidth

  'セルの文字配置の設定
  With Selection
    .HorizontalAlignment = Sheets(1).Cells(xRow, xCol).HorizontalAlignment
    .VerticalAlignment = Sheets(1).Cells(xRow, xCol).VerticalAlignment
    .WrapText = Sheets(1).Cells(xRow, xCol).WrapText
    .Orientation = Sheets(1).Cells(xRow, xCol).Orientation
    .AddIndent = Sheets(1).Cells(xRow, xCol).AddIndent
    .IndentLevel = Sheets(1).Cells(xRow, xCol).IndentLevel
    .ShrinkToFit = Sheets(1).Cells(xRow, xCol).ShrinkToFit
    .ReadingOrder = Sheets(1).Cells(xRow, xCol).ReadingOrder
    .MergeCells = Sheets(1).Cells(xRow, xCol).MergeCells
  End With

  'セルのフォントの設定
  With Selection.Font
    .Name = Sheets(1).Cells(xRow, xCol).Font.Name
    .FontStyle = Sheets(1).Cells(xRow, xCol).Font.FontStyle
    .Size = Sheets(1).Cells(xRow, xCol).Font.Size
    .Strikethrough = Sheets(1).Cells(xRow, xCol).Font.Strikethrough
    .Superscript = Sheets(1).Cells(xRow, xCol).Font.Superscript
    .Subscript = Sheets(1).Cells(xRow, xCol).Font.Subscript
    .OutlineFont = Sheets(1).Cells(xRow, xCol).Font.OutlineFont
    .Shadow = Sheets(1).Cells(xRow, xCol).Font.Shadow
    .Underline = Sheets(1).Cells(xRow, xCol).Font.Underline
    .ColorIndex = Sheets(1).Cells(xRow, xCol).Font.ColorIndex
  End With

  'セルの背景色の設定
  With Selection.Interior
    .ColorIndex = Sheets(1).Cells(xRow, xCol).Interior.ColorIndex
    .Pattern = Sheets(1).Cells(xRow, xCol).Interior.Pattern
    .PatternColorIndex = Sheets(1).Cells(xRow, xCol).Interior. _
                                                         PatternColorIndex
  End With

End Sub

 これでスピードは2倍以上速くなります。
 
 しかも画面はチラチラしません。

   Selection.NumberFormatLocal = Sheets(1).Cells(xRow, xCol).NumberFormatLocal

 この部分を、

   = Workbooks(Temp_Book).Sheets(Temp_Sheet).Cells(xRow, xCol).NumberFormatLocal

  のように変更すれば、別の Book から書式や列幅のコピーができます。

  example31 サンプルを掲載してあります 。