VBA For Each 〜 複数条件について

EXCEL マクロ VBAについて質問をお願い致します。
フォルダ内の全てのブックを開いて、
sheet名が「商品A」だったら処理①を行う
sheet名が「商品B」だったら処理②を行う
というコードについて下記で試したところ上手く行きませんでした。

For Each ws In Worksheets

If ws.Name = "商品A" Then
ws.Range(Cells(3 , 2),Cells(20 , 2)).Copy wb.Worksheets (mysht).Cells(lastrow , 3) ’処理①
lastrow = lastrow + 18
End If

If ws.Name = "商品B" Then
ws.Range(Cells(3 , 2),Cells(20 , 2)).Copy wb.Worksheets (mysht).Cells(lastrow , 5) ’処理②
lastrow = lastrow + 18
End If

Next ws

どなたかご教授頂けますでしょうか。宜しくお願い致します。

No.3 ベストアンサー 回答者： Qchan1962 回答日時： 2022/10/21 11:30

こんにちは

初めてのご質問のようですのでお節介なアドバイスをします
（うざったいかも知れませんが・・・）

ご質問の内容でおそらく問題がある場所は、すでに回答にある通りと思います
2シート以上存在するブックに対してアクティブでないシートにTrue処理をするとエラー1004が返り止まります）

しかし、
上手く行きませんでした。を広く解釈して想像するとエラーの出ないバグ（期待不一致処理）の可能性も見受けられます

＞試したところ上手く行きませんでした。
この部分は、出来るだけ具体的にどのように上手くいかないのか
期待する処理結果との差異、エラーなどを記す様にしましょう


余談
バグの可能性
ご質問文をそのまま読むと・・

＞フォルダ内の全てのブックを開いて
すべてを手動、もしくはVBAで開いたのちに処理した場合、対象となるブックは最後にアクティブになったブックとなります
each bk in workbooks などで取得するにしても
In Worksheetsを使う場合ブックオブジェクトを明示する必要があります
もちろん　activateやselectで対応することは出来ますが・・
（1つずつ開いてそのまま処理していれば良いのですが）

他の部分でも期待と不一致処理を想像させる部分があります
lastrow = lastrow + 18
lastrowは変数名などから想像して最終行№を代入していると想像できますが
例えば、
lastrow = wb.Worksheets(mysht).Cells(Rows.Count, 3).End(xlUp).Row
又は　lastrow =　定数
前者はwb.Worksheets(mysht)C列の最終行（新規行）を取得していますが
処理後に処理行数を+ 18しています。仮定として次のシート商品Bがあった時に貼り付けスタート行は最終行（新規行）では無い可能性が高いですよね

これもブックごとに行を同じにしたい場合、またはNot　や　常に上から詰めて取得する場合、変数への値の代入方法（定数を代入）などで変わってきます・・

どうでしょう、良く解らない（求めていない）回答がされてしまいますよね
またそこで躓いたり、混乱したりするかもしれませんね

出来るだけ上手くいかない内容、事象を明示するようにするのが好ましいですね

この回答へのお礼

Qchan1962様
こんばんは。ご回答頂きありがとうございます。
凄いです・・・まさに仰る通りで、lastrowの部分が期待した処理にならない最大の原因でした。
最終行(新規行)に値を次々取得していきたかったのですが、１つ目のIf文と２つ目のIf文の両方にlastrow = lastrow + 18を書いていたために、コピー先のC列とE列にそれぞれ18行飛ばしで値がコピーされていました。
ご指摘頂いたCellsにwsを指定し、1つ目のIf 文のlastrow = lastrow + 18を削除したところ思い通りの処理が出来ました。
今後質問する時には、どういう結果にしたかったか・エラーの内容等を細かく記載したいと思いました。
お節介どころか一番の原因を突き止めて頂き、本当に助かりました。ありがとうございました！

お礼日時：2022/10/21 19:32

No.2 回答者： fujillin 回答日時： 2022/10/20 21:43

こんばんは

原因はNo1様の回答にある通りかと。

どちらも固定セル範囲なので、
＞ws.Range(Cells(3 , 2),Cells(20 , 2)).Copy ~~
　　　　↓　　↓
　ws.Range("B3:B20").Copy ~~
のような記述法にしておけば、あまり気にしなくてもすむと思います。

あるいは、
　ws.Cells(3, 2).Resize(18).Copy ~~
とかでもいけるでしょう。

この回答へのお礼

fujillin様
こんばんは。ご回答頂きありがとうございます。
なるほど、ws.Range("B3:B20").Copy ~~の書き方は可読性が高くとても分かりやすいですね。早速書き換えました。Resizeという方法もあるのですね。勉強になりました。ありがとうございました！

お礼日時：2022/10/21 19:30

No.1 回答者： めぐみん_ 回答日時： 2022/10/20 20:10

どちらもですが。

ws.Range(ws.Cells(3 , 2),ws.Cells(20 , 2)).Copy

Cellsもどのシートなのか明確な指定を。
withで纏めても良いですけどね。

この回答へのお礼

めぐみん様
こんばんは。ご回答頂きありがとうございます。
Cellsのワークシートを指定しなければならなかったのですね。withで纏めるとよりスッキリしたコードになり良いですね。早々にご回答頂き大変嬉しかったです。勉強になりました。ありがとうございました！

お礼日時：2022/10/21 19:29