新しいワークブックのシートが指定できません

質問

エクセルでマクロを作成中です。

マクロの動作の中で新しいワークブック作成して、
その中のワークシートを指定してデータを入力したいのですが、
全て1つ目のシートに入力されてしまいます。

どのようにしたら2つ目以降のシートを指定できるのでしょうか？

ソース
Public Csvfile
Public Csv_bk As Workbook
Public Xls_bk As Workbook
Public ch As Integer
Public i As Integer
Public LineCSV As Variant
Public LineXLS As Variant

Sub CSV_XLS()
Set Csv_bk = Workbooks.Open(Csvfile)
Set Xls_bk = Workbooks.Add

ch = FreeFile
Open Csvfile For Input As #ch
i = 1

Do Until EOF(ch)
Line Input #ch, LineCSV
LineXLS = Split(LineCSV, ",")
Xls_bk.Worksheets(1).Range(Cells(i, 1), Cells(i, UBound(LineXLS) + 1)) = LineXLS
i = i + 1
Loop
End Sub

DO文の中のWorksheets(1)をWorksheets(2)にしても全て
1つ目のシートに入力されてしまいます。

上のソースでは全てのデータを同じシートに入力していますが、
実際にはWorksheets(n)として
nを使ってデータをシート毎に振り分けたいと考えています。

Csvfileには別のフォームからファイル名を取得しています。

回答No.2

Wendy02 · ベストアンサー

回答は出ていますが、

Xls_bk.Worksheets(1).Range(Cells(i, 1), Cells(i, UBound(LineXLS) + 1)) = LineXLS
Range と Cells は、親オブジェクトを明示しないと共有しないのです。
いくら、Xls_bk.Worksheets(1).Range　としても、Cells 側は、ActiveSheet を指しています。
なぜ、こんな間違いやすい仕様になっているのかは、はっきりしないのですが、おそらく、成立した年代が違うからだと思います。だから、私は、そのような、Cells プロパティをRange でくくるようなことはしません。

私は、Resizeを使って、こんな書き方をしています。
'-------------------------------------------
　 Open Csvfile For Input As #ch
　 i = 1
　　Do Until EOF(ch)
　　　　Line Input #ch, LineCSV
　　　　LineXLS = Split(LineCSV, ",")
　　　　With Xls_bk.Worksheets(1)
　　　　　.Cells(i, 1).Resize(, UBound(LineXLS) + 1) = LineXLS
　　　　　i = i + 1
　　　　End With
　　Loop
　　Close #ch　　'←ご質問のコードには抜けています。

'-------------------------------------------
なお、基本的に、標準モジュールにコードを置くと、誤動作は減ります。

それと、変数は、グローバル変数ではなくて、ほとんど、その都度変わるローカル変数ですから、Sub の中に入れたほうがよいですね。
Const Csvfile ="Test1.CSV"
Sub CSV_XLS()
Dim ch　　　　As Integer
Dim i　　　　 As Long　'行を表す変数はLong型
Dim LineCSV 　 As String　'ループの中で使うものは、外には出さないほうがよいです。
Dim LineXLS　 As Varinat

お礼

ご回答ありがとうございます。

お手本になるコード、大変勉強になります。
テキスト片手に読み解いていこうと思います。

回答No.1

watabe007

>Xls_bk.Worksheets(1).Range(Cells(i, 1), Cells(i, UBound(LineXLS) + 1)) = LineXLS
With Xls_bk.Worksheets(1)
　　.Range(.Cells(i, 1), .Cells(i, UBound(LineXLS) + 1)) = LineXLS
End With

お礼

ご回答ありがとうございます。

勉強不足で何が間違っているのかすらわからないままですが、
おかげさまで、思うような動作を実現することができました。