【Excel】 マクロをチェックして頂けないでしょうか。

質問

こんにちは

毎月更新するシートを追加する
コマンドボタンを設置したいと思います。

シートをコピーし、名前を付けた範囲「翌月繰越」を F6にコピペ。
名前を付けた範囲「クリア範囲」をクリアし、カーソルをG6に置く
という、マクロを記録しました。

コマンドボタンをクリックすると、記録したマクロを
実行するようにしましたが、

Range("F6").Select

の、ところで止まってしまいます。
どのように修正したら良いでしょうか。

Private Sub CommandButton2_Click()

ActiveSheet.Copy Before:=ActiveSheet
Application.Goto Reference:="翌月繰越"
Selection.Copy
Range("F6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="クリア範囲"
ActiveWindow.SmallScroll Down:=-48
Application.CutCopyMode = False
Selection.ClearContents
Range("G6").Select
End Sub

また、これを実行するとコピー元シートの左側にシートが追加されますが、
コピー元シートの右側に追加するにはどうしたら良いでしょうか。

Excelは2003です。
よろしくお願いいたします。

回答No.5

Wendy02 · ベストアンサー

#3の回答者です。

ちょっと後で、思いついたのですが、点線から下のように、マクロを分離させたほうがよいです。
同じマクロを使いますし、ブック全体の文字量の負担が減ります。

shName = Format$(CDate(shName) + 31, "yyyy.mm")
このようにすれば、必ず、新しいシートは、次の月になっていきますし、2008.12 の次は、2009.01になります。

>翌月繰越には式が入っているため、値のみを貼付けたいのですが、

>.Range("F6").Value = Application.Range("翌月繰越").Value　　

このように逆さにすればよいです。

もし、その名前登録した部分も、数式を値に換えるなら、

Application.Range("翌月繰越").Value　=Application.Range("翌月繰越").Value

とすれば、値になります。

'シートオブジェクト
'--------------------------------------------

'コマンドボタンのマクロ
Private Sub CommandButton2_Click()
　Call CopySheet
End Sub

'--------------------------------------------

'標準モジュール

Public Sub CopySheet()
Dim shName As String
Dim dum As Variant
'シート名は、yyyy.mm 型に限ります。
　　shName = StrConv(ActiveSheet.Name, vbNarrow)
　　shName = Replace(shName & "/01", ".", "/", , , 1)
　　shName = Format$(CDate(shName) + 31, "yyyy.mm")
　　On Error Resume Next
　　 dum = Worksheets(shName).Range("A1")
　　 If Err.Number = 0 Then
　　　 MsgBox "すでに、" & shName & " シートは存在しています。", 48
　　　 Exit Sub
　　 End If
　　On Error GoTo 0
　　ActiveSheet.Copy After:=ActiveSheet
　　With ActiveSheet
　　　　.Name = shName
　　　　.Range("F6").Value = Application.Range("翌月繰越").Value
　　　　Application.Range("クリア範囲").ClearContents
　　　　.Range("G6").Select
　　End With
End Sub

お礼

ご回答有難うございます。

完璧！といいますか、
自分が考えていた以上に上手くできてしまいました。

本当に助かりました。

有難うございました。

回答No.4

onlyrom

最近この手の質問が多いような。。。(^^;;;

エラーの原因は、シート名を省略しているからです。
複数のシートを扱うときは、Range、Cellsの前に必ずシート名を付加しませう。

もちろん、名前が特定できないときはActiveSheetとしてもいいし（今回の場合）、
コピーしてできたシートをオブジェクト変数に入れてそれを使うとか。

（１）ActiveSheetを使う場合
'------------------------------------------
Private Sub CommandButton2_Click()
　ActiveSheet.Copy Before:=ActiveSheet
　ActiveSheet.Range("F6").Value = ActiveSheet.Range("翌月繰越")
　ActiveSheet.Range("クリア範囲").ClearContents
　ActiveSheet.Range("G6").Select
End Sub
'-------------------------------------------

（２）コピーしたシートをオブジェクト変数に入れて使う場合。
'---------------------------------------------------
Private Sub CommandButton2_Click()
　Dim NewSheet As Worksheet
　ActiveSheet.Copy Before:=ActiveSheet
　Set NewSheet = ActiveSheet
　NewSheet.Range("F6").Value = NewSheet.Range("翌月繰越").Value
　NewSheet.Range("クリア範囲").ClearContents
　NewSheet.Range("G6").Select
End Sub
'----------------------------------------------

何れにしろ、上記（１）（２）とも、
ActiveSheet.Copy Before:=ActiveSheet
このActiveSheetは、まだコピー元のシートです。
このコードが実行されあと、コピーしてできたシートがActiveSheetになります。
それは（２）のコードを見れば何となくでも分かると思います。

それから、コピー元の右にコピーしたいときは、
　Before:=　＝＞　After:=

以上。

お礼

回答頂きありがとうございます。
大変分かりやすく、勉強になりました。

有難うございました。

回答No.3

Wendy02

#2の補足です。
あえて、シートコピーで、オブジェクトなどの移動を考えたものだとしたら、このようになります。
shName は、例えば、4月なら、5月が新しいシートとして変更されていきます。

Private Sub CommandButton2_Click()
Dim shName As String
Dim dum As Variant
　　shName = StrConv(ActiveSheet.Name, vbNarrow)
　　shName = Val(shName) Mod 12 + 1 & "月"
　　On Error Resume Next
　　 dum = Worksheets(shName).Range("A1")
　　 If Err.Number = 0 Then
　　　 MsgBox "すでに、" & shName & " シートは存在しています。", 48
　　　 Exit Sub
　　 End If
　　On Error GoTo 0
　　ActiveSheet.Copy After:=ActiveSheet
　　With ActiveSheet
　　　　.Name = shName
　　　　Application.Range("翌月繰越").Copy .Range("F6")
　　　　Application.Range("クリア範囲").ClearContents
　　　　.Range("G6").Select
　　End With
End Sub

補足

ご回答有難うございます。
上手く行きました。

シートを作成する際、翌月の「5月」と表示される点まで
ご配慮いただき有難うございます。

シート名「2008.04」を新しいシート名「2008.05」と
するには、どのようにしたらよいでしょうか。

また、翌月繰越の範囲を貼付ける際、
翌月繰越には式が入っているため、値のみを貼付けたいのですが、
どのようにしたらよいでしょうか。

回答No.2

Wendy02

こんにちは。

そのマクロは分かりにくいですね。
最初に、記録マクロで作られたものは、「標準モジュール」で動くようには作られても、シートモジュールでは動くようにはなっておりません。

まず、セルやRangeの位置関係が、曖昧なのです。

'コマンドボタンのあるシートから、

ActiveSheet.Copy Before:=ActiveSheet　'新しいシート
Application.Goto Reference:="翌月繰越"　'前のシート
Selection.Copy　'前のシートの翌月繰越
Range("F6").Select　'これはコマンドボタンのあるシートになくてはエラーが発生します。

それと、その「名前－登録」の使ったマクロは読めません。私が、以前から、ぼんやりとしていた問題のイメージが、このコードでやっとはっきりしました。

この場合は、「名前－登録」がどこにあるかにもよりますが、このName オブジェクトが、Applicationに対して一意ですから、コピー側に移ってしまうようです。そうすると、どうしようもないというか、そのマクロでは、偶然に誰かが場あり的に解答は出来ても、今の時点では、厳密には答えはありません。

「名前－登録」の処理をどのようにするかにもよります。
今の段階では、シートコピーをやめて、シートを新たに挿入するスタイルにして、元のシートのCellsコピーすれば、問題は減るとは思います。

>コピー元シートの右側に追加するにはどうしたら良いでしょうか。

ActiveSheet.Copy After:=ActiveSheet

ということでしょうか。

回答No.1

fifaile

マクロはよくわからないので的外れかもしれませんけど、
＞Range("F6").Select
これってコピー先のシートを参照していますか？
別シートの場合はシート名を入れないとまずいとおもうんですが。
マクロだとちがうのかな？

補足

お返事ありがとうございます。
はい。
F6はコピー先シートのF6です。
コピー先は「元シート名(1)」になりますが、
これを指定するにはどうしたら良いのか分かりません…。