変数を名前に使ったシートにデータをコピーする方法

質問

いつもお世話になります。

hisworkbookにあるVBAから新たに開いたmyFileにデータをコピーさせようとしています。
myFileである統合.xlsにはあらかじめ該当するシートが作成されています。
myBushoとmyGroupはそれぞれセルの値を参照しています。
それを元に対応するシート名にデータのコピーをしたいのです。

当初workbooks(myFile)をactiveworkbookにしていたのですが、うまくコピーされなかったので、
ファイルパスを記述しました。

sub test()

dim cnt as long
dim lcnt as long
dim myFile as string
dim myBusho as string
dim myGroup as string

cnt = ThisWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
lcnt = ActiveWorkbook.Sheets(myBusho & "_" & myGroup).Cells(Rows.Count, 1).End(xlUp).Row
myFile = "C:\統合.xls"
myBusho = range("A1").value
myGroup = range("A2").value

ThisWorkbook.Sheets("Sheet1").Range(Cells(9, 1), Cells(cnt, 21)).Copy _
Workbooks(myFile).Sheet(myBusho & "_" & myGroup).Cells(lcnt + 1, 1)

end sub

上記のコードではうまくコピーできませんでした。

よろしくアドバイスのほど、お願いします。

回答No.2

imogasi

多分、原因からすると質問表題（コピーの問題といっている）が、的外れではないかな。
先日もこのコーナーに質問があったが、Range（Cells(),Cells())の記述方法にあるのでは。
質問のコードをコピーし、一部削除（コメント化）したり、コードを追加して下記でやってみた。
' myBusho = Range("A1").Value
' myGroup = Range("A2").Value
が後に出てきている理由がわからない。名前をリテラル記述でなく、Ａ１，Ａ２セルに入れて、その都度実行できるようにする狙いがあり、質問の時に記述で混乱していないか。
・・・Cells(cnt, "U")).Selectの部分で無理に終わらせて、VBA実行で範囲指定が思う通りに行われているか確認できる。
その後Ｃｏｐｙ貼り付けをテストする。
１歩１歩確認の癖はぜひつけるべきだ。下記ではMsgboxでやッている。ＶＢＡでは正式な他の方法があるが。
それに初心者が、複雑な職場の複雑（多数ブック、多数シートにまたがる処理など）な処理を、ＶＢＡですぐ取り掛かるのは無理があると思う。
Range（Cells(),Cells())の件は、解説書にも大きくは取り上げてないようで、私も理由ははっきりわかっているとはいえない状態です。上位オブジェクト.中位オブジェクト.下位オブジェクトの明確化と、省略可能ケースについてはよく勉強してください。できれば指定する癖で行くのが安全。
私はＣｅｌｌｓ（１、１）よりＣｅｌｌｓ（１，”Ａ”）の書き方が好きで使う。
Sub test()
Dim ws1 As Worksheet '追加
Dim cnt As Long
Dim lcnt As Long
Dim myFile As String
Dim myBusho As String
Dim myGroup As String
MsgBox Rows.Count
cnt = ThisWorkbook.Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
MsgBox cnt
myBusho = "総務"
myGroup = "製品"
Sheets(3).Name = myBusho & "_" & myGroup
lcnt = ActiveWorkbook.Sheets(3).Cells(Rows.Count, _
"A").End(xlUp).Row
MsgBox lcnt
myFile = "C:\統合.xls"
' myBusho = Range("A1").Value
' myGroup = Range("A2").Value
'--
Dim book1 As Workbook
Workbooks(1).Activate
Set book1 = Application.ActiveWorkbook
MsgBox book1.Name
Set ws1 = book1.Worksheets(1)
Set ws2 = book1.Worksheets(2)
' thisWorkbook.Worksheets("Sheet1").Range(ws1.Cells(9, "A"), ws1.Cells(cnt, "U")).Select
book1.Activate
ws1.Select
ws1.Range(ws1.Cells(9, "A"), ws1.Cells(cnt, "U")).Select
ws1.Range(ws1.Cells(9, "A"), ws1.Cells(cnt, "U")).Copy ws2.Range("a1")
' ThisWorkbook.Sheets("Sheet1").Range(Cells(9, "A"), Cells(cnt, "U").Copy _
'Workbooks(myFile).Sheet(myBusho & "_" & myGroup).Cells(lcnt + 1, 1)
End Sub
ーー
データは
Sheet1に勝手なデータを１２行用意した。
実行して
Sheet2にA9:U12に張り付いた。
'Workbooks(myFile).Sheet(myBusho & "_" & myGroup).Cells(lcnt + 1, 1)
の部分はフォルダの作成やデータ作成が面倒で、上記はやってない（すみません手抜き。方向は間違っていなければ、質問者で修正してやってみてください）。

回答No.1

real beatin（@realbeatin）

こんにちは。

説明からもスクリプトからも、整理された要件が伝わって来ませんので、
ご自身の中で整理することを目標にしてみて下さい。
今、大事なことは、
「どのブックの」
「どのシートの」
「どのセル」
という風に対象オブジェクトひとつひとつを明示的にする、ということだと思います。

その前にまず、
> lcnt = ActiveWorkbook.Sheets(myBusho & "_" & myGroup).Cells(Rows.Count, 1).End(xlUp).Row
> myFile = "C:\統合.xls"
> myBusho = range("A1").value
> myGroup = range("A2").value
myBusho、myGroup、に値を格納する前に、
.Sheets(myBusho & "_" & myGroup)を呼び出しても、
エラーになる筈なのですが、この点に言及がないことが、
この質問に近寄り難い印象を与えてしまっています。
　myFile = "C:\統合.xls"
　myBusho = range("A1").value
　myGroup = range("A2").value
　lcnt = Workbooks(myFile).Sheets(myBusho & "_" & myGroup).Cells(Rows.Count, 1).End(xlUp).Row
という順番で書かれるべき意図で書かれたもの？ということだったとして、話を進めます。

> myBusho = range("A1").value
> myGroup = range("A2").value
「どのブックの」「どのシートの」rangeなのか、ということ、
親オブジェクトを意識して、
次に把握して、整理して、
正しく指定するようにしましょう。

１■【ThisWorkbook.Sheets("Sheet1")】が親オブジェクトであった場合
部分的には、
　myBusho = ThisWorkbook.Sheets("Sheet1").Range("A1").value
　myGroup = ThisWorkbook.Sheets("Sheet1").Range("A2").value
等のようにきちんと指定して書けば済むことです。
２■【Workbooks(myFile)】の例えば仮に【.Sheets("Sheet1")】が親オブジェクトであった場合
　myBusho = Workbooks(myFile).Sheets("Sheet1").Range("A1").value
　myGroup = Workbooks(myFile).Sheets("Sheet1").Range("A2").value
ですね。
こういう大事な要件が説明分からもスクリプトからも抜けているのは、
やはりご自身でも整理できていない、ということなのだと思います。
（> ThisWorkbook.Sheets("Sheet1").Range(Cells(9, 1), Cells(cnt, 21)).Copy _
　　の部分についても同様、Cells(9, 1)、Cells(cnt, 21)、について
　　「どのブックの」「どのシートの」指定が抜けています。）
２つの例を挙げましたが、解答する立場から見れば
これ以外の場合だって考えられますから、どうしていいのか解らないのです。
ここでは上述２例に絞って、整理することを目的とした書き方を挙げてみます。
端的にいうとRows.Countの部分以外は全部、rangeオブジェクトの扱いについて、
親オブジェクトの指定を確認してください。

'１■【ThisWorkbook.Sheets("Sheet1")】が親オブジェクトであった場合
' ' ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

Sub Re8928979_a1()
Dim 統合先シート As Worksheet
Dim cnt As Long
Dim lcnt As Long
Dim myFile As String
Dim myBusho As String
Dim myGroup As String

　　myFile = "C:\統合.xls"
　　With ThisWorkbook.Sheets("Sheet1")
　　　　cnt = .Cells(Rows.Count, 1).End(xlUp).Row
　　　　myBusho = .Range("A1").Value
　　　　myGroup = .Range("A2").Value
　　　　Set 統合先シート = Workbooks(myFile).Sheets(myBusho & "_" & myGroup)
　　　　lcnt = 統合先シート.Cells(Rows.Count, 1).End(xlUp).Row
'　　　　.Range(.Cells(9, 1), .Cells(cnt, 21)).Copy 統合先シート.Cells(lcnt + 1, 1)
　　　　.Range("A9:U" & cnt).Copy 統合先シート.Cells(lcnt + 1, 1)
　　End With
End Sub

' ' ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

'２■【Workbooks(myFile)】の例えば仮に【.Sheets("Sheet1")】が親オブジェクトであった場合、
' ' ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

Sub Re8928979_a2()
Dim 統合ブック As Workbook
Dim 統合先シート As Worksheet
Dim cnt As Long
Dim lcnt As Long
Dim myFile As String
Dim myBusho As String
Dim myGroup As String

　　myFile = "C:\統合.xls"
　　Set 統合ブック = Workbooks(myFile)
　　With 統合ブック.Sheets("Sheet1")　'　シート名　？
　　　　myBusho = .Range("A1").Value
　　　　myGroup = .Range("A2").Value
　　End With

　　Set 統合先シート = 統合ブック.Sheets(myBusho & "_" & myGroup)
　　lcnt = 統合先シート.Cells(Rows.Count, 1).End(xlUp).Row

　　With ThisWorkbook.Sheets("Sheet1")
　　　　cnt = .Cells(Rows.Count, 1).End(xlUp).Row
'　　　　.Range(.Cells(9, 1), .Cells(cnt, 21)).Copy 統合先シート.Cells(lcnt + 1, 1)
　　　　.Range("A9:U" & cnt).Copy 統合先シート.Cells(lcnt + 1, 1)
　　End With
End Sub

' ' ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

言わずもがな、ですが、
.Sheets(myBusho & "_" & myGroup)指定したシート名が見つからなければエラーになります。
Workbooks(myFile)ブックが開いていなければエラーになります。

ActiveWorkbookとか、ActiveSheetとか、ActiveCellとか、
マクロの記録では見ることもありますが、基本的には使いません。
理由は、オブジェクトが何者か、解り難くなるからです。
今後の話として、なるべく使わない方向で書くようにして
少し、オブジェクトの扱いに熟れるようにすれば、徐々に迷わなくなると思います。

取り敢えず、以上です。
こちらの理解が至っていないかも知れないので、
もし違っている場合は補足してみてください。
詳らかであれば、再度返答します。