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

Question

こんにちは

毎月更新するシートを追加する
コマンドボタンを設置したいと思います。

シートをコピーし、名前を付けた範囲「翌月繰越」を 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です。
よろしくお願いいたします。

Wendy02 · Accepted Answer

#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

onlyrom · Answer

最近この手の質問が多いような。。。(^^;;;

エラーの原因は、シート名を省略しているからです。
複数のシートを扱うときは、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:=

以上。

Wendy02 · Answer

#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

Wendy02 · Answer

こんにちは。

そのマクロは分かりにくいですね。
最初に、記録マクロで作られたものは、「標準モジュール」で動くようには作られても、シートモジュールでは動くようにはなっておりません。

まず、セルやRangeの位置関係が、曖昧なのです。

'コマンドボタンのあるシートから、

ActiveSheet.Copy Before:=ActiveSheet　'新しいシート
Application.Goto Reference:="翌月繰越"　'前のシート
Selection.Copy　'前のシートの翌月繰越
Range("F6").Select　'これはコマンドボタンのあるシートになくてはエラーが発生します。

それと、その「名前－登録」の使ったマクロは読めません。私が、以前から、ぼんやりとしていた問題のイメージが、このコードでやっとはっきりしました。

この場合は、「名前－登録」がどこにあるかにもよりますが、このName オブジェクトが、Applicationに対して一意ですから、コピー側に移ってしまうようです。そうすると、どうしようもないというか、そのマクロでは、偶然に誰かが場あり的に解答は出来ても、今の時点では、厳密には答えはありません。

「名前－登録」の処理をどのようにするかにもよります。
今の段階では、シートコピーをやめて、シートを新たに挿入するスタイルにして、元のシートのCellsコピーすれば、問題は減るとは思います。

>コピー元シートの右側に追加するにはどうしたら良いでしょうか。

ActiveSheet.Copy After:=ActiveSheet

ということでしょうか。

fifaile · Answer

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

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

#3の回答者です。

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

#2の補足です。

この回答への補足

こんにちは。

マクロはよくわからないので的外れかもしれませんけど、

この回答への補足

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

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