マクロの記録

質問

マクロの記録について教えてください。

数字を入力する列と、先月の数字から今月の数字の差を出している列の2行があります。
やりたいことは、毎月その2列をコピーして新しい月の分を作成。
印刷範囲の指定や、非表示にしたりして、今月先月分の列だけを表示するようにすることです。

その通りに記録すると、その月の分は出来たのですが、翌月の分ができません。（記録した月から進まない。）

説明がわかりにくくて申し訳ございません。
どうしたらいいのでしょうか？

回答No.6

fujillin · ベストアンサー

No2、No4です。

まだ、内容がちゃんと把握できないけれど、とりあえず参考まで。
列を決める方法は、月から計算してもよいけれど誤操作で上書きする可能性もあるので、空白セルを探す方式にしてあります。

代表とする行の右端空白セルを探して、その列にコピーするという方法。
代表とする行はcheck_rowで指定してください。
（とりあえず1行目にしてある：その行には必ず値がある行を指定しておく）

Sub test()
Dim rng As Range, col As Long
Const check_row = 1 '// ← この行の空白列にコピーされます

'// check_row行の右端の空白列を取得
col = Cells(check_row, Columns.Count).End(xlToLeft).Column + 1
'// 基準とする範囲をrngにセット（2列の1行目）
Set rng = Cells(1, col).Resize(1, 2)

'// 空白列がF列未満の場合は何もしない（コピー対象がない）
If col < 6 Then Exit Sub
'// 直前の2列をコピー
rng.Offset(0, -2).EntireColumn.Copy (Cells(1, col))

'// 印刷範囲を設定
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(64, col + 1)).Address

'// 対象セルがJ列未満の場合は非表示処理を行わない
If col < 10 Then Exit Sub
'// 6列左側の2列を非表示にする
rng.Offset(0, -6).EntireColumn.Hidden = True

End Sub

お礼

ありがとうございます！
check_rowの数字を変えたら出来ました。
自分ではまだほとんど理解できませんが、頑張って勉強したいと思います。
本当にありがとうございました。

回答No.5

imogasi

マクロの記録をしても、そのコードはその操作をそっくり再現するプログラムです。それぐらい勉強してるでしょう。
普通はそれで繰り返して、よい場合も有るが、別の実行機会には変化させたい箇所がある場合がほとんどです。
変化させられるコードを作ることが出来る力をつけることがＶＢＡ利用の初歩の一歩です。（進むとマクロの記録は頼りにしません）
そこで出来ませんといわれても、勉強してもらわないと。
どこをどう変えるべきか（コード上ではなくシート上で、説明文で）書けてないので、回答しようが無い。
各月連続して行う作業の詳細が説明不足で質問文では判らない。
実例を挙げて(列、行、セル、シートの各々の名を示して）具体的にしたい（変化させたい箇所）ことを一歩一歩文章で説明すべきです。
それにマクロの記録を修正すれば目的がかなうかどうかは場合によります。

お礼

ありがとうございます。
記録を少し触れば同じことが繰り返せるのかと思っていました。
私にはちょっと難しいようなのでもう少し勉強してからチャレンジしてみます。

やりたいことは

ABCには部門等の名前が入っています。
そしてDが1月分の数字、Eが12月分の数字から1月分の数字をひいた物（使用量）、Fが2月分の数字、Gが2月分の数字から1月分の数字をひいた物・・・というようにセットで何年も続いています。

現在3月分の使用量Iまであるとして、4月分作成のためHIをGKにコピー。印刷範囲をGKまで広げる、DEを非表示、という作業を毎月やっています。

回答No.4

fujillin

No2です。

例では行を変数とする場合で説明しましたが、ご提示のマクロの内容ですと、（No2の最後にも書きましたが）列を可変（変数）にしないといけないようですね。
月によって操作が変わるのは、行ではなくて列ですよね？

また、ご提示の補足のコードでは
　rw=7
と固定の行番号（←本当は列番号にするべき）になったままなので、処理対象のセル位置は変化しません。
わざわざ変数に置き換えたのは、その値を変化させるためです。No2の<例5>、<例6>に該当する部分が抜けていますね。

月と、列の関係はどのようにして決まっているのでしょうか？
あるいは、○行目の最初の空白列を次のペースト対象と判断するとか、なんらかの規則性があるはずなのですが…

印刷範囲の縦方向（1～64行）は固定なのでしょうか？
"$A$1:$K$64" → "$A$1:$A&rw$1" ではうまくいきませんね。（意味不明となってしまいます）
対象範囲にあわせたRangeを作成して、そのRangeのAdressを代入するのが簡単かと思います。

お礼

ありがとうございます。

行と列は完全に勘違いしていました。変わるのはABCの方です。列ですね。
思っていたより色々難しそうで、記録だけをどうこういう私のレベルでは出来そうにないですね。

月と列の関係は、まずABCには部門等の名前が入っています。
そしてDが1月分の数字、Eが12月分の数字から1月分の数字をひいた物（使用量）、Fが2月分の数字、Gが2月分の数字から1月分の数字をひいた物・・・というように何年も続いています。

現在3月分の使用量Iまであるとして、HIをGKにコピー。印刷範囲をGKまで広げる、DEを非表示、という作業を毎月やっています。

回答No.3

xls88

提示された相対参照マクロを使ってみます。

★1の行を追加してみます。
≪例1≫
Sub tes相対Macro1()
Range("G1").Select　'★1
ActiveCell.Offset(0, 1).Columns("A:B").EntireColumn.Select
Selection.Copy
ActiveCell.Offset(0, 2).Columns("A:A").EntireColumn.Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.PageSetup.PrintArea = "$A$1:$K$64"
ActiveCell.Offset(0, -6).Columns("A:B").EntireColumn.Select
Selection.EntireColumn.Hidden = True
End Sub

1行目と2行目を★2と★3のように変えてみます。
≪例2≫
Sub tes相対Macro2()
' Range("G1").Select
Range("H1").Select　'★2
' ActiveCell.Offset(0, 1).Columns("A:B").EntireColumn.Select
ActiveCell.Columns("A:B").EntireColumn.Select　'★3
Selection.Copy
ActiveCell.Offset(0, 2).Columns("A:A").EntireColumn.Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.PageSetup.PrintArea = "$A$1:$K$64"
ActiveCell.Offset(0, -6).Columns("A:B").EntireColumn.Select
Selection.EntireColumn.Hidden = True
End Sub

どちらもステップモードでマクロを1行毎に実行し、Excel画面の動きを確認してください。
デバッグについて
http://members.jcom.home.ne.jp/rex-uchida/vba110.htm

≪例3≫
次はSelectを止めてみます。
Macro1、Macro2、はActiveCellが移動しています。
しかしMacro3では、ActiveCellがH1セルに固定されます。
移動しない分、Offsetする位置を注意しなければなりません。
H1を起点に、貼り付け先、非表示列へのOffset量を決定します。
Sub tes相対Macro3()
Range("H1").Select
ActiveCell.Columns("A:B").EntireColumn.Copy _
ActiveCell.Offset(0, 2).Columns("A:A").EntireColumn
ActiveSheet.PageSetup.PrintArea = "$A$1:$K$64"
ActiveCell.Offset(0, -3).Columns("A:B").EntireColumn.Hidden = True
End Sub

次に、1行目でセレクトしているセルを変更できるように工夫します。
印刷範囲はどうなりますか？

お礼

ありがとうございます。
ちょっと私には難しいようです。
もっとマクロを勉強してからチャレンジしてみます。

回答No.2

fujillin

具体例がないので、一般的な回答になってしまいますが…
「マクロの記録」で作成されたマクロは、セル位置の指定などが「A18」のように固定された指定方法になっています。
（それなので、何回実行しても、同じ月の分しかできないのです。）

例えば、以下はA18:B18を一行下へコピーするのを自動記録したものです。
<例1>
　Range("A18:B18").Select
　Selection.Copy
　Range("A19").Select
　ActiveSheet.Paste
これを何回実行しても、同じ行しかコピーしません。

行を可変にするために変数にしてみます。（rwという変数にする）
<例2>
　rw = 18
　Range("A" & rw & ":B" & rw).Select
　Selection.Copy
　Range("A" & (rw+1)).Select
　ActiveSheet.Paste

この状態で、rwに必要な行番号を入れておけば指定した内容に応じてコピーされる行が変わることになります。
その前に、Ｓｅｌｅｃｔなどの操作に係る部分を省略してしまいましょう。
<例3>
　rw = 18
　Range(Cells(rw, 1), Cells(rw, 2)).Copy (Cells(rw + 1, 1))

あるいは、以下のような形でもかまいません。
<例4>
　Set rng = Range("A1:B1")
　rw = 17
　rng.Offset(rw).Copy (rng.Offset(rw + 1))
*この例の場合は、オフセットする関係で、rwは実際の行番号より1小さい数になっています。

ここまでは、rwの数字を変えていませんので、どの例でも結果的には最初と同じ18行目に対して処理がされます。
実際には、月によって対象とする行が変わるにしても、なんらかの規則性があるはずなので、その規則からrwの値を算出してあげればよいことになります。

<例5>
例えば、A列の最終行の次（空白）の行に、最終行をコピーするなら、
　rw = Cells(Rows.Count, 1).End(xlUp).Row
　Range(Cells(rw, 1), Cells(rw, 2)).Copy (Cells(rw + 1, 1))
*こうすると、実行するごとに最下行にコピーが追加されてゆきます。

<例6>
月の数字から行番号が計算できるとして、例えば「月*3+2」であれば
　rw = Month(Date) * 3 + 1
　Range(Cells(rw, 1), Cells(rw, 2)).Copy (Cells(rw + 1, 1))

みたいな要領になります。
実際には、もっと複雑な処理をしていると思いますが、処理する対象セルの指定を変数にしておいて、その変数の値を（なんらかの規則によって）指定してあげるということになるでしょう。

↑では行を可変にする場合を例示しましたが、列を可変にする場合も同じ要領です。同時に両方やれば、行列とも位置が可変ということになります。

お礼

ありがとうございます。
考え方としては納得しました。
教えていただいた物を参考に

Sub 更新()

'
' Macro1 Macro
'

'
rw = 7
Columns("A" & rw & ":B" & rw).Select
Selection.Copy
Columns("A" & (rw + 2)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.PageSetup.PrintArea = "$A$1:$A&rw$1"
Columns("A" & rw - 4 & ":A" & rw - 3).Select
Selection.EntireColumn.Hidden = True
End Sub

としてみたのですが、ダメなようです。
間違いをご指摘願えないでしょうか。

回答No.1

xls88

記録して得られたコードを提示できないでしょうか。

お礼

ありがとうございます。

Sub Macro1()
'
' Macro1 Macro
'

'
Columns("H:I").Select
Selection.Copy
Columns("J:J").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.PageSetup.PrintArea = "$A$1:$K$64"
Columns("D:E").Select
Selection.EntireColumn.Hidden = True
End Sub

このようになりました。

相対参照にしてみたら

Sub Macro1()
'
' Macro1 Macro
'

'
ActiveCell.Offset(0, 1).Columns("A:B").EntireColumn.Select
Selection.Copy
ActiveCell.Offset(0, 2).Columns("A:A").EntireColumn.Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.PageSetup.PrintArea = "$A$1:$K$64"
ActiveCell.Offset(0, -6).Columns("A:B").EntireColumn.Select
Selection.EntireColumn.Hidden = True
End Sub

こちらでは、もともと行の頭にあった使用者名が非表示になったり・・