[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『表示シートの最後尾(右端)を知る方法』（知りたがり）
左端にWorksheets("Master")があり、そこから右へ複数のシートがあり
そのシートの不特定数が非表示になっているブックがあります。
そのブックの最後尾(右端)にMasterシートをコピーしようとすると、
実際の最後尾が非表示になっているときは表示シートの右端にコピーされるため
コピーしたシートがWorksheets(Worksheets.Count)ではSetできないので
Public Function シート最後尾(ByVal wb As Workbook) As Long
'非表示シートを検索し、表示シートの最後尾を返す
Dim ws As Worksheet
Dim Flag As Boolean
Dim 最後尾 As Long
Flag = False
For Each ws In wb.Worksheets
Select Case ws.Visible
Case xlSheetVisible
If Flag Then
Flag = False
最後尾 = 0
End If
Case xlSheetHidden, xlSheetVeryHidden
If 最後尾 = 0 Then
Flag = True
最後尾 = ws.Index - 1
End If
End Select
Next ws
シート最後尾 = 最後尾
End Function
のようなプロシージャで表示シートの最後尾を取得しています。
もっと簡単に取得できる方法等はないでしょうか？
＜ 使用 Excel：Microsoft365、使用 OS：Windows10 ＞
シートのコピー直後であれば ActiveSheet でコピーしたシートをとれないだろうか？ （ねむねむ） 2023/04/26(水) 13:14:20
Sub 実験01()
Stop 'ブレークポイントの代わり
Dim tmp As Boolean
With Worksheets(Worksheets.Count)
tmp = .Visible
.Visible = True
Worksheets("Master").Copy after:=Worksheets(.Index)
.Visible = tmp
End With
MsgBox "コピー挿入したシートは【" & Worksheets(Worksheets.Count).Name & "】です"
End Sub
（もこな２） 2023/04/26(水) 15:33:32
お勉強に参加します^^;
Sub Sample()
Dim str As String
Dim x As Long
str = Worksheets(Worksheets.Count).Name
x = Worksheets(Worksheets.Count).Index
'' 最後尾(右端)にMasterシートをコピー
ActiveSheet.Copy after:=Sheets(x) '' ← 特定シートは、Sheet12
Debug.Print str, x
'**************************************************************
Rem : 処理前の[ Debug.Print ] 結果
Rem : 左から並び順はグチャグチャ
Rem : 左から [ Master , 6, 7, 8, 9,10, 2, 3, 4 ,5, 11, 12 ]
Rem : 非表示は、[ 8, 3, 5, 11, 12 ]
Rem : Debug.Print 結果: [ Sheet12 , 12 ]
Rem : 実行すると( 処理後 )
Rem : Sheet13 が作成され、シート名は、(Master(2))となる
'**************************************************************
' ※注意
' ActiveSheet.Copy after:=Sheets(x)と書くと他のBOOKが
' セレクトされていると... あッ失敗!! やらかす人多し(笑)
' webで、良く紹介されるパターンなので
End Sub
（もこな２）さんのように書いた方が無難ですな
Worksheets("Master").Copy After:=Sheets(x)
（あみな） 2023/04/26(水) 16:24:49
ねむねむさん＞
確かにActiveSheetだと直後ならいけますね。
ただ出来るだけActive○○は使いたくないんですよね。
もこな２さん＞
そのやり方だときちんと最後尾に作成されますね。
非表示のままコピーするとAfter:=.Worksheets(.Worksheets.count)なのに
何故か表示シートと非表示シートの間に作成されますからね・・・
あみなさん＞
それだとシート名はSheet 13でもIndexは13にならなくないですか？
それがSetできなかった理由なんですよね・・・
よく見たら自分の作成したプロシージャも表示と非表示の間に挟まってますね。
後ろから探しても同じになりそうですし、もこな２さんのアイデアが一番目的に近い気がしますね。
（知りたがり） 2023/04/26(水) 17:43:24
Setする事が目的なのであれば、 明らかにIndexを把握できる場所にCopyして、 変数にSetしてから最後尾にMoveしても良いのかもですね。
Dim s As Worksheet
Sheets("Master").Copy Sheets(1)
Set s = Sheets(1)
Debug.Print s.Index; s.Name
s.Move after:=Sheets(Sheets.Count)
Debug.Print s.Index; s.Name
まあ「最後尾にMove」と言ったところで、 CopyでもMoveでも非表示シートが無視されちゃう事に変わりはないので あんまり状況は変わらないのかも知れませんけど... ^^;
（白茶） 2023/04/26(水) 20:24:18
コピーだけ出来たら良いかと勘違いしました (o_ _)o ﾍﾟｺ
Set もなんですね（ ; ; ）
新たな作戦を、検討したのですが…無謀でしたｗ
Sub Sample() '' これはエラーになりますｗ
Dim ws As Worksheet
Set ws = Worksheets.Add
Debug.Print ws.Name
Worksheets("Master").Copy Worksheets(ws.Name)
End Sub
（あみな） 2023/04/26(水) 20:38:16
これはいけるような
Sub Sample()
Dim ws As Worksheet
Set ws = Worksheets.Add
Worksheets("Master").Range("A1").Copy _
Destination:=Worksheets(ws.Name).Range("A1")
Debug.Print ws.Name
MsgBox ws.[A1]
End Sub
※今日は、もう遅いので寝ます...皆様、おやすみなさい。 （あみな） 2023/04/26(水) 23:12:01
白茶さん＞
仕様としか言いようがありませんが、なぜ非表示は無視されてしまうんですかね・・・
xlSheetVeryHiddenの時だけならまだわかる気もするのですが。
行列の非表示のときのCountのように表示部分だけのCountにもなりませんしね。
よくわからない仕様です。
あみなさん＞
色々試してもらってありがとうございます。
（知りたがり） 2023/04/27(木) 07:33:03
私、2016ですが、 >実際の最後尾が非表示になっているときは表示シートの右端にコピーされる この現象再現しないです。
ちゃんと最後に追加されますが、私なにか勘違いしてますか？ （´・ω・`） 2023/04/27(木) 08:36:25
（´・ω・`）さん再現しないのか〜
私、2021ですが、 >実際の最後尾が非表示になっているときは表示シートの右端にコピーされる この現象になる。
↓これダメ
Sub 実験02()
With Worksheets(Worksheets.Count)
Worksheets("Master").Copy after:=Worksheets(.Index)
End With
End Sub
今も、（白茶）さんの 2023/04/26(水) 20:24:18 マクロでしたけど...オモイッキリなる
（あみな） 2023/04/27(木) 09:23:41
基本、（もこな）おね〜さまの方法が、優勝ですね。(きっと)
BOOKのシート構成を、下記のイメージとして挑戦しました。 これなら、完成度90% ( 自己評価 )
・左から、[ Master､注文､在庫､リスト ]のシート全4枚として ・最後尾の[ リスト ]シートは､非表示になっている。(基本)
Sub Sample()
Dim ws As Worksheet
Set ws = Worksheets.Add
Worksheets("リスト").Visible = True
Worksheets("Master").[A1:E10].Copy _
Destination:=Worksheets(ws.Name).[A1:E10]
ws.Move after:=Sheets(Sheets.Count)
ws.Name = "発注書" ''( 新規シート名は仮の話です )
Worksheets("リスト").Visible = False
MsgBox ws.Name & "へコピーしました"
End Sub
※ 流れ
・[ リスト ]を Visible = True して
・ Worksheets("Master")をコピー
・[ Move after ] 君で、最後尾に移動させて
・新規のシートに名前を[ 発注書 ]と付けて
・[ リスト ] Visible = False して終了
・既にSet 済なので、お好きにどうぞってイメージ
（あみな） 2023/04/27(木) 09:37:26
ごめんなさい 私が勘違いしていました シートの追加(Add)ではなくコピーでした たしかにそうなります （´・ω・`） 2023/04/27(木) 10:06:16
よし来た､↓下なら
Sub Sample()
Dim ws As Worksheet
Set ws = Sheets.Add(after:=Sheets(Sheets.Count))
ws.Name = "発注書"
Worksheets("Master").[A1:E10].Copy _
Destination:=Worksheets(ws.Name).[A1:E10]
MsgBox ws.Name & "へコピーしました"
End Sub
Worksheets("リスト").Visible = True
Worksheets("リスト").Visible = False
ws.Move after:=Sheets(Sheets.Count)
が省ける （あみな） 2023/04/27(木) 10:07:56
（´・ω・`）さん＞
コピーですね。
Addなら普通に最後尾に追加されるのがまた腹立たしい感が・・・
あみなさん＞
Addのあとに様式コピーだと印刷関係の設定はコピーされないんですよね。
なので私はAddよりコピーを推します。
（つまりもこな２さんのが一番良いかと）
今回のはもともとの様式が左端(Worksheets(1))に原紙シートがあったのでこのような質問になりましたが、
こういう原紙シートからコピーシートを作成していくようなマクロがある場合
原紙シートを最後尾にしてBefore:=原紙シートにした方がスマートなのかな、って思いました。
※今更ですが。
（知りたがり） 2023/04/27(木) 12:44:58
＞確かにActiveSheetだと直後ならいけますね。 ＞ただ出来るだけActive○○は使いたくないんですよね。
何か論理的とは思えないのだが・・
こんなにすったもんだする位なら ActiveSheetが最適じゃないの？
（半平太） 2023/04/27(木) 13:50:27
>Addのあとに様式コピーだと印刷関係の設定はコピーされないんですよね。
印刷関係の設定ってどんなのかしら？ う〜ん、ちょっと良くわからないです。
原紙一発コピーってことなのか？ 私には、されたいコピー内容までわかりかねます。
もこなさんのコードに、Set入れてイメージで 良いのでされたい事を、教えてください。
そしたら、こちらも勉強になりますので Ser後に、どんなマクロ内容の事をされるのか 興味があります。
（あみな） 2023/04/27(木) 14:31:08
半平太さん＞
あの時点ではSetの問題しか考えてなかったのであんな回答になりましたが、
もこな2さんの案で完全に最後尾にシートがコピーされるのでその案が1番かなと思いました。
だから個人的には解決？はしています。
Activesheetを使いたくないのはコード組み替えとかした時に
見た目どのシートか分かりづらいからですね。
あみなさん＞
印刷関係の設定と言うのは印刷範囲だったりヘッダー・フッターとかの設定ですね。
コピー後にコード上で設定してもいいですが、しなくていいならそちらのが楽ですから…
＞もこなさんのコードに、Set入れてイメージで
＞ 良いのでされたい事を、教えてください。
今自宅でPCにさわれないのでコードは明日になりますが、
やりたい事は別ブックのデータの月締めのときに
履歴ブックの書式や印刷設定してある原紙シートをコピー作成して
配列でデータ転記しシート名を変えるといったものです。
その後印刷してファイルに閉じてます。
こんな説明で分かりますかね？
実際のコードを見たらSet要らないやん！とか言われそうな気がしますが…
（知りたがり） 2023/04/27(木) 23:37:17
うーむ、今まで Worksheets(Worksheets.Count)の後ろにコピーすれば、その後は Worksheets(Worksheets.Count)はコピー後のシートになるので「凄く分かり易い」。 Activesheetを使うなんて、ド素人のコードだね。
皆、そう思っていたハズなんだが、 非表示のシートがあった場合は「逆じゃね？」って話ですよね。
堅牢であるべき実務コードでは、もこな２さんの様な慎重なコードを書いているんですかね？ まさか、非表示シートなんて想定しなくていいよ、なんて前提でいいとも思えないのだが。。 (実務家にお聞きしたいものです）
（半平太） 2023/04/28(金) 10:05:06
原紙シートが左端にあるほうが一般的なのかな？
確かに手動でやるならそっちのほうが便利かもしれないけど
（ufj） 2023/04/28(金) 11:05:56
元にするシート1枚だけのブックをどこかに保存しておいて
Dim ws As Worksheet Set ws = Sheets.Add(after:=Worksheets(Worksheets.Count), Type:="D:\Master.xlsx") とかするのが一番簡単な感じですね
type引数には、xltxじゃなくてxlsxでもいいみたいです （´・ω・`） 2023/04/28(金) 11:20:16
＞非表示のシートがあった場合は「逆じゃね？」って話ですよね。
After:=Worksheets(Worksheets.Count)なのにWorksheets.Countより
手前にコピーされてしまう不可思議使用によりActivesheetの価値が若干上がった感ありますね。
全てを知り尽くしたプログラマーであれば、どのようなコードかは分かりませんが
最後尾の表示・非表示関係なく全て同じ動作をするように組んでいるのでしょうね。
（´・ω・`）さん＞
同じブックに原紙は置かず、不可思議使用に引っかかりそうなコードは組まない・・・
が最善策ですかね。
あみなさん＞
取り合えず今実際に組んでいるコードはこんな感じです。
いろいろ突っ込みどころはあるでしょうがご容赦を。
Public Sub 更新()
'
Dim wb履歴 As Workbook
Dim ws原料 As Worksheet, ws履歴 As Worksheet, _
ws As Worksheet, ws最後尾 As Worksheet
Dim 読取範囲 As Range, 貼付範囲 As Range, _
消去範囲(2) As Range, 先月末在庫範囲 As Range, 対象月 As Range
Dim 転写 As Variant, 在庫 As Variant
Dim 範囲 As String, シート名 As String
Dim 最終行 As Long, 行 As Long, msg As Long, 最後尾 As Long
Dim 表示状態 As Boolean
'各範囲の設定
Set ws原料 = ThisWorkbook.Worksheets("原料管理")
With ws原料
最終行 = .Cells(.Rows.Count, "A").End(xlUp).Row
範囲 = "$A$1:" & .Cells(最終行, "BO").Address
Set 読取範囲 = .Range(範囲)
Set 消去範囲(1) = .Range("$D$1:$BM$1")
Set 消去範囲(2) = .Range("$D$4:$BM$" & 最終行)
Set 先月末在庫範囲 = .Range("$BN$4:$BN$" & 最終行)
Set 対象月 = .Range("A1")
End With
'必要なデータの格納
ReDim 在庫(最終行 - 4, 0)
転写 = 読取範囲.Value
For 行 = 4 To 最終行
在庫(行 - 4, 0) = 読取範囲(行, 2)
Next 行
シート名 = Format(対象月.Value, "yyyy年m月")
'
Set wb履歴 = GetBook '←別プロシージャでブック開いてます
With wb履歴
'作成しようとするシートと同月がないか検索、あれば質問
For Each ws In .Worksheets
If ws.Name = シート名 Then
msg = MsgBox("作成しようとする月のシートが既に存在します。" & vbLf & _
"このまま更新を進めますか？", vbYesNo + vbQuestion)
If msg = vbNo Then Exit Sub
シート名 = "重複シート (" & シート名 & ")"
Exit For
End If
Next ws
'最後尾に"Master"シートをコピー、シート名を当該月にする
Set ws最後尾 = .Worksheets(.Worksheets.Count)
表示状態 = ws最後尾.Visible
ws最後尾.Visible = xlSheetVisible
.Worksheets("Master").Copy After:=ws最後尾
Set ws履歴 = .Worksheets(.Worksheets.Count)
ws最後尾.Visible = 表示状態
ws履歴.Name = シート名
'データを貼り付け
Set 貼付範囲 = ws履歴.Range(範囲)
貼付範囲.Value = 転写
End With
'原料管理シートを初期化
対象月.Value = DateValue(Year(対象月.Value) & "/" & Month(対象月.Value) + 1 & "/01")
先月末在庫範囲.Value = 在庫
消去範囲(1).ClearContents
消去範囲(2).ClearContents
End Sub
（知りたがり） 2023/04/28(金) 11:29:49
（知りたがり）さん...ありがとうございます。(o_ _)o. ﾍﾟｺ
>取り合えず今実際に組んでいるコードはこんな感じです。 >いろいろ突っ込みどころはあるでしょうがご容赦を。
突っ込めるほど、知識がありませんが、研究材料に させていただきます。
（あみな） 2023/04/28(金) 11:45:22
Addなら最後尾来るなら、スケープゴート作ってやるのも手ですよね。
Sub test()
Dim ws As Worksheet
With Sheets.Add(after:=Sheets(Sheets.Count))
Sheets("Sheet1").Copy after:=Sheets(.Index)
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With
Set ws = Sheets(Sheets.Count)
Debug.Print ws.Name
End Sub
（稲葉） 2023/04/28(金) 16:53:25
皆さんありがとうございます。
（知りたがり） 2023/04/28(金) 18:13:42
少数のシートの複製ならば追加は後ろ…って思ってしまうので
原紙は先頭にあった方が見栄えが良さげな感がありますが、
多数のシートの複製であれば、複製した時に見える最後尾の方が
分かりやすいのかもしれませんね。
この様なブックの使い方をしている方々はどっちが多いのかな…と思いました。
（知りたがり） 2023/04/29(土) 23:06:53
踏まえて別案。興味があれば、ステップ実行等を行い研究のうえ、必要な部分をご自身のコードに組み込んでください。
※1 理解して頂きたいので丸パクリはNGとします。 ※2 コンパイルエラーにならないことしかチェックしてません。
Sub 別案()
Dim 最終行 As Long
Dim wb履歴 As Workbook
Dim tmp As Worksheet, dstSH As Worksheet
Dim シート名 As String
Dim 表示状態 As Boolean
With ThisWorkbook.Worksheets("原料管理")
最終行 = .Cells(.Rows.Count, "A").End(xlUp).Row
シート名 = Format(.Range("A1").Value, "yyyy年m月")
Set wb履歴 = Workbooks("hogehoge.xlsx")
'▼シート重複チェック
On Error Resume Next
Set tmp = wb履歴.Worksheets(シート名)
On Error GoTo 0
If Not tmp Is Nothing Then
If MsgBox("作成しようとする月のシートが既に存在します。" & vbLf & "このまま更新を進めますか？", vbYesNo + vbQuestion) = vbNo Then
Exit Sub
Else
シート名 = "重複シート (" & シート名 & ")"
End If
End If
'▼シート挿入処理
表示状態 = wb履歴.Worksheets(wb履歴.Worksheets.Count).Visible
wb履歴.Worksheets(wb履歴.Worksheets.Count).Visible = xlSheetVisible
wb履歴.Worksheets("Master").Copy After:=wb履歴.Worksheets(wb履歴.Worksheets.Count)
'▼転記処理
Set dstSH = wb履歴.Worksheets(wb履歴.Worksheets.Count)
dstSH.Previous.Visible = 表示状態
dstSH.Name = シート名
dstSH.Range("A1:BO" & 最終行).Value = .Range("A1:BO" & 最終行).Value
'▼"原料管理"シートのリセット（月次更新？）
.Range("A1").Value = DateAdd("m", 1, Range("A1").Value) 'A1セルを１か月後に更新
.Range("BN4:BN" & 最終行).Value = .Range("B4:B" & 最終行).Value
.Range("D1:BM1,D4:BM" & 最終行).ClearContents
End With
End Sub
（もこな２ ） 2023/05/01(月) 19:07:41
やはり上手な人が組むとすっきりしますね・・・
DateAddとか知りませんでしたし、勉強になります。
＞変数を盛りだくさんにしてかえって混乱しないですかね。
これは自分が今の職場から居なくなった時に
VBAをほとんど知らない方(自分が熟知しているわけではないですが)が見ても
変数を日本語にして入れておけば分かるかな？と思ってやってたんですが、
やっぱり逆に分かりづらいですかね。
マクロの中身を知らないで様式変えたりする人もいるんで・・・
＞一旦配列を変数に入れるのは冗長な気がします。
すみません、配列を覚えたてなので使いたくなってしまいました。
（知りたがり） 2023/05/02(火) 07:38:21
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.