
VBA 初心者です。請求書作成のマクロを組んでいる最中なのですが調べては試してをし、現在に至るのですが出来ないことが2点あります。
※一つ前の質問の改訂版です。色々と試した結果一つだけ問題解決しました・・・
①1件目のデータ基を請求書に転記するマクロは組めました。しかし、2件目以降のデータを対象の列から最終行を取得して貼り付ける。ということを行いたく、Cells(Rows.Count, "").End(xlUp).Row&offsetを使うのは解るのですがデータ基の繰り返しForの値との組み合わせ?方がわかりません。
小計2のデータもあるため、ただ最終行取得では本来小計1に貼り付けたい内容が小計2に張り付けられてしまいました。
※また、案件コード・案件名は表題として扱いたいので項目ごとに記載ではなく、一行のみ記載したいです。(現在はセルの指定をしていますが、2件目のデータの際には案件コード等も変わるのでやはりoffset等での指示が必要ですよね・・・)
②データ基のブック名・シート名が一部変更になっても大丈夫なVBAを組もうと*を使って作ったのですがエラーが起きてしまいつくれませんでした。
ブック名・シート名共にD6までは固定で00101~どんどん変動していきます。
エラーの対応策をご教授頂けたらと思います。
※今は請求書作成をとりあえず先に。と1つの名前で作成しています。
周りにVBA組める人がいないのでここに頼ってしまい申し訳ありませんが宜しくお願い致します。
下記、現在作成したVBAとなります。
画像は追って添付します。
ーーーーーーーーーー
Sub 請求書作成()
'シートの指定
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim x As Long
Dim y As Long
Dim m As Long
Dim n As Long
Set wb1 = Workbooks("売上管理票(D600101).xlsx")
Set wb2 = Workbooks("請求書.xlsm")
Set sh1 = wb1.Worksheets("D600101")
Set sh2 = wb2.Worksheets("請求書")
x = 168: y = 17: m = 172: n = 39
sh2.Cells(6, 2) = sh1.Cells(3, 14) '取引先名
'小計1
For x = 168 To 171
With sh1.Cells(x, "AC")
If IsNumeric(.Value) And .Value > 0 Then
sh2.Cells(17, 2) = sh1.Cells(2, 8) '案件コード
sh2.Cells(17, 3) = sh1.Cells(2, 14) '案件名
sh2.Cells(y, 4) = sh1.Cells(x, 25) '項目
sh2.Cells(y, 5) = sh1.Cells(x, 26) '数量
sh2.Cells(y, 6) = sh1.Cells(x, 27) '単位
sh2.Cells(y, 7) = sh1.Cells(x, 28) '単価
sh2.Cells(y, 8) = sh1.Cells(x, 29) '金額
y = y + 1
End If
End With
Next x
'小計2
For m = 172 To 174
With sh1.Cells(m, "AC")
If IsNumeric(.Value) And .Value > 0 Then
sh2.Cells(39, 2) = sh1.Cells(2, 8) '案件コード
sh2.Cells(39, 3) = sh1.Cells(2, 14) '案件名
sh2.Cells(n, 4) = sh1.Cells(m, 25) '項目
sh2.Cells(n, 5) = sh1.Cells(m, 26) '数量
sh2.Cells(n, 6) = sh1.Cells(m, 27) '単位
sh2.Cells(n, 7) = sh1.Cells(m, 28) '単価
sh2.Cells(n, 8) = sh1.Cells(m, 29) '金額
n = n + 1
End If
End With
Next m
End Sub
No.4ベストアンサー
- 回答日時:
No.3です。
次のBook&Sheetを取り込む際に『案件』についての情報は同じ値で宜しいのでしたっけ?
もし違うと言うならそれぞれの固定行を変数: y , n に変更してください。
極力既に作成されているコードに手を付けずに弄りました。
不備がありましたらご連絡を。
----------
Sub 請求書作成()
'シートの指定
Dim wb As Workbook
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim ch As Integer
Dim x As Long
Dim y As Long
Dim m As Long
Dim n As Long
Set wb2 = ThisWorkbook 'Workbooks("請求書.xlsm")
Set sh2 = wb2.Worksheets("請求書")
For Each wb In Workbooks
If wb.Name <> wb2.Name Then Set wb1 = wb 'Workbooks("売上管理票(D600101).xlsx")
Next
wb1.Activate
Set sh1 = wb1.ActiveSheet 'wb1.Worksheets("D600101")
wb2.Activate
ch = MsgBox("開かれてる売上管理票のブック名は :" & wb1.Name & vbCrLf & _
"選択されたシートは :" & sh1.Name & vbCrLf & "これで宜しいですか?", vbYesNo)
If ch = vbNo Then MsgBox ("[いいえ] が選択されましたので中止。"): Exit Sub
x = 168: y = IIf(sh2.Cells(17, 4).Value = "", 17, sh2.Cells(16, 4).End(xlDown).Row + 1)
m = 172: n = IIf(sh2.Cells(39, 4).Value = "", 39, sh2.Cells(38, 4).End(xlDown).Row + 1)
sh2.Cells(6, 2).Value = sh1.Cells(3, 14).Value '取引先名
'小計1
For x = 168 To 171
With sh1.Cells(x, "AC")
If IsNumeric(.Value) And .Value > 0 Then
sh2.Cells(17, 2) = sh1.Cells(2, 8) '案件コード
sh2.Cells(17, 3) = sh1.Cells(2, 14) '案件名
sh2.Cells(y, 4) = sh1.Cells(x, 25) '項目
sh2.Cells(y, 5) = sh1.Cells(x, 26) '数量
sh2.Cells(y, 6) = sh1.Cells(x, 27) '単位
sh2.Cells(y, 7) = sh1.Cells(x, 28) '単価
sh2.Cells(y, 8) = sh1.Cells(x, 29) '金額
y = y + 1
End If
End With
Next x
'小計2
For m = 172 To 174
With sh1.Cells(m, "AC")
If IsNumeric(.Value) And .Value > 0 Then
sh2.Cells(39, 2) = sh1.Cells(2, 8) '案件コード
sh2.Cells(39, 3) = sh1.Cells(2, 14) '案件名
sh2.Cells(n, 4) = sh1.Cells(m, 25) '項目
sh2.Cells(n, 5) = sh1.Cells(m, 26) '数量
sh2.Cells(n, 6) = sh1.Cells(m, 27) '単位
sh2.Cells(n, 7) = sh1.Cells(m, 28) '単価
sh2.Cells(n, 8) = sh1.Cells(m, 29) '金額
n = n + 1
End If
End With
Next m
End Sub
------------
売上管理票のBookは開いておき且つ必要なSheetを広げている状態からとなっております。
お忙しいところ本当にありがとうございます!!早速動かしてみました。案件コード・案件名は変動するのでy・nにしました。動作内容は感動するレベルで完璧でした!!
一点、もし可能であれば案件コード・案件名は各項目ごとに記載されるのではなく、記載される項目1回目のみという動きをさせることは可能なのでしょうか。無理であればこのまま使用させて頂きます!!
上記の回答を頂け次第、こちらをベストアンサーにさせて頂き今回の質問を解決済にしたいと思います。
本当にありがとうございました。
No.5
- 回答日時:
No.4です。
Dim yy As Long
Dim nn As Long
と言う2つの変数を追加します。
x = 168: y = IIf(sh2.Cells(17, 4).Value = "", 17, sh2.Cells(16, 4).End(xlDown).Row + 1):yy = y
m = 172: n = IIf(sh2.Cells(39, 4).Value = "", 39, sh2.Cells(38, 4).End(xlDown).Row + 1):nn = n
と y,n の初期値を yy,nn に代入します。
あとは先ほどの y,n に変更した『案件』の行を示す変数と yy,nn を入れ替えます。
この2つはループ中に +1 をされないので初期値のままのはずです。
できました!!早急なご教授本当にありがとうございます。
まだ初めてだらけすぎてご迷惑をおかけして申し訳ありません。
感謝の気持ちでいっぱいです。
分かりやすく一つ前のNo.4をベストアンサーにさせて頂きます!!
本当に本当にありがとうございました!!
No.3
- 回答日時:
まず請求書の小計1と2の境界線はその書式で固定されていると考えて構わないのでしょうか?
それともどちらかの増減により変動せざるを得ない状況が発生する場合はありますか?
スタイルとしては『売上管理票~.xlsx』を『作業者が自分で選択して開いてシートをアクティブにする。(エクスプローラーで)』なのか、
何らかの手段で自動化させようと考えているのか。
その場合組み合わせがどうなっているのかが不明になりますね。
次に呼び出すBookをどう選ばせるのかですので。
まぁ、まだ簡単に書けると思えるのは『請求書Bookは開いたまま』『売上Bookを選んで開き、必要なシートをアクティブにしておく』と言う手作業を行い、
あとはマクロを実行。
請求書に値が書き込まれたら一旦マクロは終了。
次のブックを開きシートをアクティブしてマクロを実行。
ってのが回答する上では楽ですよね。
ただ小計2がない場合、小計1の対になっていたデータ行は飛ばして次のデータを取り込むのか否か・・・
小計1・2の項目行が固定であれば、最終行は下から調べる(xlUp)のではなく、上から調べる(xlDown)方法もありますよ。
お忙しい中ありがとうございます!
小計1、2の境界線の変動(フォーマットの変動)はありません!
また、ブックのアクティブについてでふが請求書、対象の売上管理表を作業者が都度開きます。記載して頂いたとおり、両方のブックを開き一度マクロ実行。次の売上管理表を開き再度マクロ実行。の、流れになります。
小計1、2の項目行ですが固定です。またデータ元の売上管理表の方も固定です。
No.2
- 回答日時:
No.1です。
>1件目のデータ基を請求書に転記するマクロは組めました。
『1件目のデータ基』って何を指しているのでしょう?
そして『2件目以降』とは?
次のシート?次のブック?の事かな。
コード的には『Workbooks("売上管理票(D600101).xlsx")』の『wb1.Worksheets("D600101")』のY列より右のデータを取得してますよね?
でも画像的に『2件目以降』と言えるようなものがどれなのか不明。
もしかして提示しているコードの該当する『列番号』はダミー?
度々ありがとうございます!下記にも書きましたが1件目、2件目とは異なるブックの事を示しております。
売上管理表がそれにあたります。
はい!右下の方にある印刷範囲外のところがデータ元となってます!!(見にくくて申し訳ありません)よって、ダミーではないです。
No.1
- 回答日時:
『請求書』の書式なので本来画像の状態が提出するフォーマットになるのでは?
そう考えると最終行取得云々よりも書き込み開始行(y , n )を使ったままで良いように思えるのですが、
例えば小計1で空白行が多いのが気に入らないから件数に合わせると言う改善を検討されているのでしょうか?
又は『小計3』や『小計4』など増える場合もあるのでそれらを調整したいとか?
あと
sh2.Cells(17, 2) = sh1.Cells(2, 8) '案件コード
sh2.Cells(17, 3) = sh1.Cells(2, 14) '案件名
この2行(ともう1組)は For ~ Next の前に実行しても良いのでは?
ループ中何度も同じ所に同じ値を入れてる訳でちょっときになりました。
コメントありがとうございます!!説明が色々と分かりにくく申し訳ありません。
請求書ですが、おなじ取引先様でも案件が複数発生しており、案件コードにて管理しております。
↑それぞれ別ブックにて管理です。
小計1、2ともに条件毎に複数の案件の請求内容が記載されるので、一件目の記載が終わったあと、別ブックを再度マクロの対象にして反映させたいと思ってます。
↑
なので、シート名が一部変更でもマクロが動くようにしたいのです。
案件コード、案件名は確かにForの前に実行しても問題はないです!ただ、小計2は必ずしも反映内容があるとは限らないのでForの中に入れておこうとおもってます。小計1も中に入れてるのはただ自分が見てわかりやすいからだけです。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) 2つ目のコンボボックスが動作しません。 3 2023/03/25 12:29
- Visual Basic(VBA) マクロで最終行を取得したい 4 2023/05/28 12:14
- Visual Basic(VBA) Excel VBA ユーザーフォーム1のコンボボックスに別ブックの値を反映させたいです。 6 2023/03/21 16:12
- Visual Basic(VBA) VBA 請求書自動作成 3 2022/04/24 01:58
- Visual Basic(VBA) VBA 別ブックからの転記の高速化について VBA 別ブックからの転記の高速化についてご教授下さい。 19 2022/07/26 13:07
- Visual Basic(VBA) vbaを早くしたい 5 2022/09/09 10:58
- Visual Basic(VBA) VBAで質問ですが、皆さんはどの様に導き出しているのでしょうか? 6 2022/05/03 21:53
- Excel(エクセル) VBAで組み合わせ算出やCOUNTIFSの処理を高速化したいです。 4 2022/04/07 02:38
- Excel(エクセル) VBAの指示の内容 昨日こちらでご教示頂いたのですが初心者な為、一つ一つの指示が何をやっているのかわ 2 2022/10/25 18:08
- Visual Basic(VBA) 【VBA】特定のワードが入っている行全体を塗りつぶしたい 4 2022/04/20 15:22
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・一番好きなみそ汁の具材は?
- ・泣きながら食べたご飯の思い出
- ・「これはヤバかったな」という遅刻エピソード
- ・初めて自分の家と他人の家が違う、と意識した時
- ・いちばん失敗した人決定戦
- ・思い出すきっかけは 音楽?におい?景色?
- ・あなたなりのストレス発散方法を教えてください!
- ・もし10億円当たったら何に使いますか?
- ・何回やってもうまくいかないことは?
- ・今年はじめたいことは?
- ・あなたの人生で一番ピンチに陥った瞬間は?
- ・初めて見た映画を教えてください!
- ・今の日本に期待することはなんですか?
- ・集中するためにやっていること
- ・テレビやラジオに出たことがある人、いますか?
- ・【お題】斜め上を行くスキー場にありがちなこと
- ・人生でいちばんスベッた瞬間
- ・コーピングについて教えてください
- ・あなたの「プチ贅沢」はなんですか?
- ・コンビニでおにぎりを買うときのスタメンはどの具?
- ・おすすめの美術館・博物館、教えてください!
- ・【お題】大変な警告
- ・洋服何着持ってますか?
- ・みんなの【マイ・ベスト積読2024】を教えてください。
- ・「これいらなくない?」という慣習、教えてください
- ・今から楽しみな予定はありますか?
- ・AIツールの活用方法を教えて
- ・最強の防寒、あったか術を教えてください!
- ・歳とったな〜〜と思ったことは?
- ・モテ期を経験した方いらっしゃいますか?
- ・好きな人を振り向かせるためにしたこと
- ・スマホに会話を聞かれているな!?と思ったことありますか?
- ・それもChatGPT!?と驚いた使用方法を教えてください
- ・見学に行くとしたら【天国】と【地獄】どっち?
- ・これまでで一番「情けなかったとき」はいつですか?
- ・この人頭いいなと思ったエピソード
- ・あなたの「必」の書き順を教えてください
- ・14歳の自分に衝撃の事実を告げてください
- ・人生最悪の忘れ物
- ・あなたの習慣について教えてください!!
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
excelの差込印刷で可視セルだけ...
-
エクセル:VBAで月変わりで、自...
-
vbaでコントロールブレイク
-
VBA 貼付先範囲(行)がいっぱ...
-
VBA:同じ文字列データの比...
-
Excel で行を指定回数だけコピ...
-
VBA 最終行取得からの繰り返し貼付
-
エクセルVBAで SendKeys "{TAB}"
-
Excel VBA 時刻でのD...
-
Excelマクロ データが上書きさ...
-
歯抜けの時間を埋めて行の挿入
-
VBA別シートの最終行の下行へ貼...
-
エクセル2007で、マクロで、結...
-
Excelでデータの抽出&別シート...
-
VBAで複数シート選択
-
Excelマクロで空白セルを詰めて...
-
EXCELマクロで全シート対...
-
Excel VBA インデックスの境...
-
スマホ機種変更で旧機種のGoogl...
-
機種変更時にデータは見られる?
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
excelの差込印刷で可視セルだけ...
-
Excel で行を指定回数だけコピ...
-
Excel VBA インデックスの境...
-
VBA別シートの最終行の下行へ貼...
-
エクセルVBAで 2種のリストを...
-
エクセル:VBAで月変わりで、自...
-
VBA:同じ文字列データの比...
-
VBA 貼付先範囲(行)がいっぱ...
-
Excel VBAでシート内全体に非表...
-
エクセルVBA 別シートの複数の...
-
歯抜けの時間を埋めて行の挿入
-
Excelマクロで空白セルを詰めて...
-
VBA 最終行取得からの繰り返し貼付
-
Excelマクロ データが上書きさ...
-
EXCELマクロで全シート対...
-
エクセル シート保護後コメン...
-
エクセルVBAで SendKeys "{TAB}"
-
Excelでデータの抽出&別シート...
-
VBAで複数シート選択
-
エクセル2007で、マクロで、結...
おすすめ情報
請求書の画面です。
D列を基準に求めたくおもっております。
データ元の画面です。