【VBA】シートがコピーできないとき、そのままコピーするのではなく、内容をコピーする（数値は数値、関数は関数に）

シートをコピーするとき、Worksheet.Copyと書きますが、エラーが出る場合があります。

保護をかけられていたり、古いエクセル（拡張子が.xls）だったりするのが原因です。

そんなときは、セルを1つ1つコピーしてその場をしのぎます。

Sub CopyValuesAndFormulas(sourceWs As Worksheet, targetWs As Worksheet, copyRange As Range)

    Dim cell As Range
    Dim sourceCell As Range

    Debug.Print "範囲から値と数式をコピー: " & copyRange.Address

    ' 指定された範囲のセルをループする
    For Each cell In copyRange
        Set sourceCell = sourceWs.Range(cell.Address)

        On Error Resume Next

        ' 関数または値のチェック
        If sourceCell.HasFormula Then
            targetWs.Range(cell.Address).Formula = sourceCell.Formula
            Debug.Print "コピーされた数式: " & sourceWs.Name & " " & cell.Address & ": " & sourceCell.Formula
        Else
            targetWs.Range(cell.Address).Value = sourceCell.Value
            Debug.Print "コピーされた値: " & sourceWs.Name & " " & cell.Address & ": " & sourceCell.Value
        End If

        On Error GoTo 0
    Next cell
End Sub

Sub Test()

    Dim sourceWs As Worksheet
    Dim targetWs As Worksheet
    Set sourceWs = ThisWorkbook.Sheets("Sheet1") ' コピー元のシート
    Set targetWs = ThisWorkbook.Sheets("Sheet2") ' コピー先のシート

    Dim rangeToCopy As Range
    Set rangeToCopy = sourceWs.Range("A1:B10") ' コピーしたい範囲

    CopyValuesAndFormulas sourceWs, targetWs, rangeToCopy

    MsgBox "コピーが完了しました。"

End Sub

セルをコピーするときは.Valueたまに.Textを使いますが、関数の場合は.Formulaとなります。

セルに幅や罫線はコピーされませんが、単純にシートをコピーできないときは使ってみてください。

エクセルは新しいエクセル（拡張子が.xlsx）で保存しましょう！