マクロを使った進捗管理表について

Question

実現したいこと

マクロ初心者で、以前勤務していた同僚がつくった別件のコードを参考にして作成しています。進捗管理表作成のため、下記②のコードを入力中です（①②ともマクロを起動させるボタンは「元データ」シートに表示しています）。

①別のexcelファイルから「元データ」シートへA～M列のデータを抽出し（マクロ完成済）、②N～R列のデータを手入力した後「進捗管理表」シートにA～R列を（複数行同時に）転記（S～V列はこの段階でまだ未確定なのでブランク）、③後日、S～V列を「進捗管理表」シートに直に手入力するのが一連の作業です。

[A][B][C][D][E][F][G][H][I][J][K][L][M] （抽出）
[N][O][P][Q][R]（手入力）
-----ここまでを「元データ」シートから「進捗管理表」シートへ転記
[S][T][U][V]（後日直に手入力）
※「元データ」シートでは、A５が起点で、「進捗管理表」シートでは、A２が起点ですが、保存したい内容は全く同じです。

現在その進捗管理表は通常のexcelファイルで運用しているのですが、操作が苦手な同僚が、行や列をうっかり削除してしまったり、誤って別のセルを上書きしてしまうミスが続いており、入力済の部分に触れずにデータを更新できないかと考えています。このため、シートの保護＆解除のコードを追加しています。アドバイスをいただけると幸いです。

発生している問題・分からないこと

データが転記されません。

エラーメッセージ

Range’メソッドは失敗しました:’_Worksheet’オブジェクト

該当のソースコード

Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("元データ")
Set ws2 = ThisWorkbook.Worksheets("進捗管理表")
Dim cmax As Long
cmax = ws2.Range(“A”&Rows.Count).End(xlUp).Row+1
ws2.Unprotect
ws2.UsedRange.Locked = True
ws2.Range(“A”&cmax).Resize(196,18).Value = ws1.Range(“A5:R200”).Value
ws2.Range(“S”&cmax).Resize(196,4).Locked = False
ws2.Protect

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

With ws2～End Withを使用するとエラーは出ないのですが、やはりデータは転記されません。

補足

N～V列を修正したいときのために、下記のコードを別に登録しています。マクロを起動させるボタンは「進捗管理表」シートに表示しています。

Sub sample()
Dim sh, alert
alert = MsgBox(“N~V列を編集可能にしますか？”, vbQuestion & vbYesNo)

If alert = vbYes Then
Set sh = Sheets(“進捗管理表”)
sh.Unprotect
sh.Range(“N1:V”&Rows.Count).Locked = False
sh.Protect
Set sh = Nothing
End If
End Sub

Comments

[Refrain]

エラーはどこで発生したか分かりますか？

[tatsu99]

>N～V列を修正したいときのために、下記のコードを別に登録しています。
>Set sh = Sheets(“工程管理表”)
"進捗管理表"の他に“工程管理表”のシートもあるのでしょうか。
これは、"進捗管理表"の誤りではないでしょうか。
上記の他に
sh.Range(“N:V”&Rows.Count).Locked = False
の個所でエラーが発生します。
この提示されたスクリプトは、実際に動作していますか。

[meg_]

該当のソースコードを試しましたが「Range’メソッドは失敗しました」のエラーは発生しませんでした。「該当のソースコード」で本当にエラーは発生しましたか？

[monaxia2416]

お返事遅くなり申し訳ありません。

Refrainさま、meg_さま
すみません。もう一度確認したら、エラーは発生しませんでした。が、やはりデータは転記されませんでした。ただ、「進捗管理表」シートの保護を解除した後、「元データ」シートに戻って②を起動するボタンを押すと、「進捗管理表」シートに保護がかかるため、保護の動作は有効になってるみたいです。

tatsu99さま
すみません。”進捗”管理表の間違いです（>__<
ご指摘のとおりエラーが発生しましたｗ

[sk.exe]

> ①②ともマクロを起動させるボタンは「元データ」シートに表示

そもそも、「②を起動させるボタンを押した時に」そのマクロが
実際に呼び出されていることを正しく確認できているのでしょうか。

> エラーは発生しませんでした。が、やはりデータは転記されませんでした。

> 「進捗管理表」シートを保護にした後、「元データ」シートに戻って
> ②を起動するボタンを押すと、「進捗管理表」シートに保護がかかる

[進捗管理表]に最初から保護が掛かっていて、かつボタンをクリックしても
実際にはそのマクロが実行されていないのであれば、そういう結果にはなる
（というか、何の変化も起こっていない）でしょうけど。

もしその辺りを十分に確認できていないのであれば、とりあえず
②のプロシージャの宣言部分をブレークポイントに設定した上、
「②を起動させるボタン」をクリックしてみて下さい。

設定されたブレークポイントでコードの実行が一時停止しないのであれば、
そのボタンとマクロの紐づけが正しく行われていないだけなのではないかと。

[monaxia2416]

sk.exeさま

ご返信ありがとうございます。「「進捗管理表」シートの保護を解除した後、～」でした…たびたびすみません。下記教えていただいたコードも含めて週明けに確認してみます。

[meg_]

> すみません。もう一度確認したら、エラーは発生しませんでした。が、やはりデータは転記されませんでした。
こちらではデータ転記されます。該当のソースコードのみのマクロファイルを作成して、元データシートにダミーのデータを入力してデータが転記されることを確認されてはどうでしょうか？

[monaxia2416]

meg_さま
ご返信ありがとうございます。どうやら初歩的なことでつまづいているようですね…週明けに作り直してみます。

[monaxia2416]

みなさま、お騒がせしました。
職場のパソコンにインストールされているのがOffice2016だったので、どうやら互換性の問題だったようです…。

ところで「進捗管理表」シートの方で、S～V列を手入力した後、入力済みのセルを保護するため、補足欄に記載したコードを下記のとおり修正して追加してみたのですが、うまくいきません…（エラーは出ないのですが、手入力したセルが保護されていないようです）。
（３行目）alert = MsgBox(“空白セル以外をロックしますか？”, vbQuestion & vbYesNo)
（７行目）sh.UsedRange.SpecialCells(xlCellTypeBlanks).Locked = False
↑該当のソースコードに記載した９行目をこのUsedRange～に修正したら正しく動作します。

Answer 1

N～R列のデータを手入力した後「進捗管理表」シートにA～R列を（複数行同時に）転記

「元データ」シートでは、A５が起点で、「進捗管理表」シートでは、A２が起点です

行や列をうっかり削除してしまったり、誤って別のセルを上書きしてしまうミスが続いており、入力済の部分に触れずにデータを更新

Sub CopySourceData()

    Dim wsSource As Worksheet
    Dim rngSource As Range
    Dim lngStartRow As Long
    Dim lngLastRow As Long
    
    Set wsSource = ThisWorkbook.Worksheets("元データ")
    
    With wsSource
    
        lngStartRow = 5
        lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        If lngStartRow > lngLastRow Then
            MsgBox "データ行が見つかりません。" & vbCrLf & lngStartRow & "行目以降のA列の内容を確認して下さい", _
                   vbInformation, _
                   "複写対象データなし"
            Set wsSource = Nothing
            Exit Sub
        End If
    
        Set rngSource = .Range(.Cells(lngStartRow, "A"), _
                               .Cells(lngLastRow, "R"))
    
    End With
    
    Dim wsDestination As Worksheet
    Dim lngTargetRow As Long
    
    Set wsDestination = ThisWorkbook.Worksheets("進捗管理表")
    
    With wsDestination
        
        lngTargetRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    
        .Unprotect
        .UsedRange.Locked = True
        
        .Cells(lngTargetRow, "A").Resize(rngSource.Rows.Count, _
                                         rngSource.Columns.Count).Value = rngSource.Value
        .Cells(lngTargetRow, "S").Resize(rngSource.Rows.Count, 4).Locked = False
        
        .Protect
        
        .Select
        .Cells(lngTargetRow, "S").Select
    
    End With
    
    Set rngSource = Nothing
    Set wsSource = Nothing
    Set wsDestination = Nothing

End Sub

N～V列を修正したいときのために、下記のコードを別に登録しています。
マクロを起動させるボタンは「進捗管理表」シートに表示しています。

Sub UnlockInputColumnsOfProgressSheet()

    Dim lngAnswer As Long
    
    lngAnswer = MsgBox("N～V列を編集可能にしますか？", _
                       vbQuestion + vbYesNo + vbDefaultButton2, _
                       "確認")
    
    If lngAnswer = vbNo Then
        Exit Sub
    End If
    
    With ThisWorkbook.Worksheets("進捗管理表")
        .Unprotect
        .Range("N2:V" & .Rows.Count).Locked = False
        .Protect
    End With
    
    MsgBox "N～V列のロックを解除しました。", _
           vbInformation, _
           "解除完了"
    
End Sub