Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 11 Pro : Microsoft 365)
隠しシートのコピー
投稿日時: 22/08/26 12:20:25
投稿者: S.Kos

みなさま、こんにちは。
 
シートをコピーしようとして ws.Copy を用いました。
このとき、シートの表示状況によって、成功したりしなかったり…
 
試行錯誤の結果、
 ws.Visible = xlSheetVisible  → 全く問題なくコピーできる
 ws.Visible = xlSheetVeryHidden → コピーできない
とまでは掴めました。
 
これが ws.Visible = xlSheetHidden であるとき、できたりできなかったりで、その切り分けがハッキリしません。
 
ws.Visible = xlSheetHidden であるときの ws.Copy の動きを明示できる人はおられませんか?
 

回答
投稿日時: 22/08/26 13:42:37
投稿者: Suzu

こんにちは。
 
再現する為の情報が無いとコメントも難しいです。
 
少なくとも

引用:
できたりできなかったり

については、どのような状況で、どのようにして確認されたのでしょうか?
 
 
手元2019です。
 
Set ws = ThisWorkBook.Worksheets(1)
ws.Visible = xlSheetVeryHidden
ws.Copy After:=ThisWorkBook.Worksheets(ThisWorkBook.Worksheets.Count)
では
実行時エラー '1004'
'Copy' メソッドは失敗しました;'_Worksheet' オブジェクト
 
と実行エラーとなり、コピーに失敗しました。
校閲 - ブックの統計情報 でも、シート数の変化はありません。
 
 
続いて
Worksheets("Sheet1").Visible = xlSheetVeryHidden
Worksheets("Sheet2").Visible = xlSheetVisible
Worksheets(Array("Sheet1", "Sheet2")).Copy
は、エラーは発生しません。
新規ブックに、Sheet2 がコピーされておりますが
 
・校閲 - ブックの統計情報 では シート数は 1 となっている
・VBE の プロジェクト に、Sheet1 オブジェトは無い。
・For Each ws In Worksheets:ws.Visible = xlSheetVisible:Next を実行もSheet1は表示されない
 
から、Sheet2 のみコピーされている事は確認できました。

回答
投稿日時: 22/08/26 14:32:08
投稿者: sk

S.Kos さんの引用:
シートをコピーしようとして ws.Copy を用いました。

S.Kos さんの引用:
これが ws.Visible = xlSheetHidden であるとき、できたりできなかったりで、
その切り分けがハッキリしません。

引数 Before と 引数 After を両方とも省略しているか否かの
違いではないでしょうか。
 
・1 つのブックにおいて、全てのシートを非表示化させることは出来ない
 
・Worksheet オブジェクトの Copy メソッドを呼び出す際に
 引数 Before と 引数 After の両方を省略した場合、
 自動的に新規ブックが作成され、そのブックに対して
 その Worksheet オブジェクトのみがコピーされる。
 
・この時、もしコピー元のワークシートが非表示化されていた場合は
 コピー後の状態が上記の制約に引っかかってしまう
 (唯一のシートを非表示化しようとしている)ため
 コピーに失敗することになる(実行時エラー 1004 )。
 新規ブックも作成されない。
 
Suzu さんの引用:
Worksheets("Sheet1").Visible = xlSheetVeryHidden
Worksheets("Sheet2").Visible = xlSheetVisible
Worksheets(Array("Sheet1", "Sheet2")).Copy
は、エラーは発生しません。

・上記の場合は Worksheet オブジェクトではなく
  Sheets オブジェクト(コレクション)の Copy メソッドを
 (引数の指定を省略して)呼び出している。
 
・もし Sheets オブジェクト(コレクション)に含まれる全てのアイテムが
 「非表示化されているシート」である場合、上記と同様の理由により
 Copy メソッドは失敗することになる。
 (表示されているシートが 1 つ以上あれば成功する)

投稿日時: 22/08/26 18:51:42
投稿者: S.Kos

みなさま、早速のRES、ありがとうございます。
文字情報だけで「不具合の様子」を伝えることの難しさを、改めて思わされてます。
 
> ws.Visible = xlSheetVisible  → 全く問題なくコピーできる
> ws.Visible = xlSheetVeryHidden → コピーできない
>とまでは掴めました。
>
この表記で、以降に続く
  ws.Copy After:=ActiveWorkbook.Worksheets(Worksheets.Count)
を満たしている(?)ことを察して貰える・・・、と思ってました。
 
改めて記します
 
・新しいブック(fPath)に、
・現在開いているブックのLgBuffシートをコピーし、
・コピー先でのシート名をsNameに変えて保存
 
Private Sub Copy2NewBook(fPath As String, sName As String)
  Dim wb As Workbook, ws As Worksheet
   
  Set wb = Workbooks.Add
 
  Set ws = ThisWorkbook.Worksheets("LgBuff")
  ws.Visible = xlSheetVisible        '<----------------------【※1】
  ws.Copy After:=ActiveWorkbook.Worksheets(Worksheets.Count)
   
  ActiveSheet.Name = sName
   
  ActiveWorkbook.Worksheets("Sheet1").Delete '<----------------------【※2】
  ActiveWorkbook.Close SaveChanges:=True, Filename:=fPath
   
End Sub
 
 
【※1】なら問題なくコピー完了
ここが ws.Visible = xlSheetHidden なら、
 ・インデックスが有効範囲にありません、とメッセージして止まり
 ・デバッグボタン押し下げで【※2】が黄色表示

回答
投稿日時: 22/08/26 21:17:47
投稿者: hatena
投稿者のウェブサイトに移動

skさんの回答の
・1 つのブックにおいて、全てのシートを非表示化させることは出来ない
という制約のほかに下記の制約もあります。
・非表示シートはアクティブにできない。
 

  Set ws = ThisWorkbook.Worksheets("LgBuff")
  ws.Visible = xlSheetHidden            '非表示シート
  ws.Copy After:=ActiveWorkbook.Worksheets(Worksheets.Count)

  '↓非表示シートはアクティブにならないので、このシートはSheet1 
  ActiveSheet.Name = sName

  '↓Sheet1は上のコードで名前を変更されているので、存在しないためにエラーになる。
  ActiveWorkbook.Worksheets("Sheet1").Delete
  ActiveWorkbook.Close SaveChanges:=True, Filename:=fPath

 

回答
投稿日時: 22/08/26 21:29:15
投稿者: hatena
投稿者のウェブサイトに移動

結局、一つのブックにおいて非表示のシートのみにすることはできないので、
コピーしたシートは再表示させるしかない。
 
あと、ActiveSheet, ActiveWorkbook は今回のようにバグのもとになりやすいので、なるべく使用はさけたほうがいいでしょう。
 
 

Private Sub Copy2NewBook(fPath As String, sName As String)
  Dim wb As Workbook, ws As Worksheet
   
  Set wb = Workbooks.Add
 
  Set ws = ThisWorkbook.Worksheets("LgBuff")
  ws.Visible = xlSheetHidden
  ws.Copy After:=ActiveWorkbook.Worksheets(Worksheets.Count)
   
  wb.Worksheets("LgBuff").Visible = xlSheetVisible
  wb.Worksheets("LgBuff").Name = sName
  
  wb.Worksheets("Sheet1").Delete
  wb.Close SaveChanges:=True, Filename:=fPath
   
End Sub

投稿日時: 22/08/27 09:46:35
投稿者: S.Kos

hatenaさん、丁寧なご教示、感謝です。
 
ws.copyそのものではなく、その後にモンダイがあった・・・
納得しました。
 
不具合の切り出し(認識箇所)が間違っており、コードの全景を示さなかったため、みなさまにはご迷惑を掛けました。
にも拘わらずRESをいただいた、suzeさん、skさんには、改めて御礼申し上げつつ、ここで閉じます。
 
ありがとうございました。