[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『可変行を選択して別のシートにコピー、可変行の下にある別の行をコピーして別のシートにコピーしたい』（ＭＩＥちゃん）
VBA全くの初心者です。
Excelの複数の表に入力したデータをそれぞれデータのみにしてTextデータに変換したいと考えています。以下のとおりの要件です。よろしくお願いします。
A ヘッダーレコード : 1行
B データレコード ： 可変
C トレーラーレコード ：1行
D エンドレコード ：1行
（1）Sheet_Dateに４つの種類のレコードをそれぞれ項目名称の行をつけた
表に入力します。表は縦に並べています。
（2）項目名称の行は必要ないので、それぞれ入力したデータレコードを コピーして同じBookの別のSheet_Textの１行めからA〜Dの順で貼り
付けます。
（3）Sheet_TextをTextファイルで名前を付けて保存します。
・データレコードはのみその時々で行数が異なります。
・表と表の間に１行ずつ空欄の行を作っています。
・CとDのレコードは、常にCのレコードの最終行から決まった行だけ下
（具体的にはCは６行下、Dは１２行下の行に存在します。Cの最終行は
データ件数によって一定ではありません。
Aは常に決まった位置にあるので別のシートにコピー＆貼付け、Bは最初の行が決まった位置（７行目）にあるのでその行からBの最終行まで選択してSheet_Textにコピー＆貼付け、ここまで自動マクロで記録することができました。これから先、CとDのデータをSheet_Textにマクロを書いてコピー＆貼付け、さらにBの作業を実行したいのですが、CとDのコピー＆貼付けが自動マクロではできません。どのようにVBAを書いたらよいかご指南いただきたくお願いします。
＜ 使用 Excel：Excel2010、使用 OS：Windows8 ＞
文章だけだと、そう書くんでしょうが、今一つはっきりしません。
具体例があるともっと分かり易くなります。
下図を参考にして、実状図(サンプル）をアップしてください。
<Sheet1 データ> <別シート 希望図> 行 _____A_____ ___B___ 行 ___A___ 1 ヘッダー Header 1 Header 2 レコード RCD1 2 RCD1 3 レコード RCD2 3 RCD2 4 レコード RCD3 4 RCD3 5 レコードNN RCDNN 5 RCDNN 6 トレーラー Tral 6 Tral 7 エンド End 7 End 8 (空白) 8 Header 9 ヘッダー Header 9 RCD1 10 レコード RCD1 10 RCD2 11 レコード RCD2 11 RCD3 12 レコード RCD3 12 RCDNN 13 レコード RCD4 13 Tral 14 レコードNN RCDNN 14 End 15 トレーラー Tral 16 エンド End
（半平太） 2017/05/18(木) 17:28
Sheet_Date
行 A B C D ・・・・
1 データ区分 名称 管理番号 ・・・
2 1 あああああ 1234567
3
4 データ区分 契約番号 年月日 ・・・・
5 2 99999999 20170515 ・・・・
6 2 88888888 20170510 ・・・・
7 2 77777777 20161225 ・・・・
8 2 66666666 20170401 ・・・・
9 2 55555555 20150612 ・・・・
10
11 データ区分 合計件数 合計金額 ・・・
12 3 5 500000 ・・・
13
14 データ区分 ダミー
15 9
説明：データ区分2は毎回可変で行数がかわります。したがって、データ区分3とデータ区分9は
その都度何行目になるかわかりません。
この例では、２行目、5〜9行目、12行目、15行目をコピーしてSheet_Textに貼付けしたい
と考えています。
貼り付けた結果のイメージは以下のとおりです。
Sheet_Text
行 A B C D ・・・・
1 1 あああああ 1234567
2 2 99999999 20170515 ・・・・
3 2 88888888 20170510 ・・・・
4 2 77777777 20161225 ・・・・
5 2 66666666 20170401 ・・・・
6 2 55555555 20150612 ・・・・
7 3 5 500000 ・・
8 9
データ区分1は、Sheet_Dateの２行目に必ずあるのでこの行を指定してSheet_Textに貼付けできます。
データ区分2は、Sheet_Dateの５行目から必ず始まるのでこからデータ区分2の最終行までの選択して
Sheet_Textに貼付けできます。ここまでは自動マクロで記録しました。以下のとおりです。
Sub M_ヘッダー()
'
' M_ヘッダー Macro
'
'
Rows("2:2").Select
Selection.Copy
Sheets("Text").Select
Rows("1:1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("入力Sheet").Select
Range("A1").Select
Application.CutCopyMode = False
End Sub
Sub M_データ()
'
' M_データ Macro
'
'
Rows("5:5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Text").Select
Rows("2:2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("入力Sheet").Select
Application.CutCopyMode = False
End Sub
ここから先、データ区分3は、Sheet_Dataでいつも同じ行にあるわけではなく、貼付け先の
Sheet_Textでもデータ２を貼り付けた次の行に貼付けるために最終行を取得しなければならず
行き詰ってしまいました。
知識が浅く説明も拙くてすみません。よろしくお願いします。
（ＭＩＥちゃん） 2017/05/18(木) 18:28
分からない部分があったら聞いてください。
Sub DataShift()
Dim WsDate As Worksheet
Dim WsText As Worksheet
Dim rngTop As Range
Dim lastRowDate As Long
Dim lastRowText As Long
Dim rngToProc As Range
Set WsDate = Sheets("Date")
Set WsText = Sheets("Text")
Application.ScreenUpdating = False
WsText.Cells.ClearContents '白紙化
'ヘッダー処理
WsDate.Rows(2).Copy
WsText.Rows(1).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'データ処理
Set rngTop = WsDate.Range("A4") '項目行(4行目)のA列をセットする
If Not IsEmpty(rngTop.Offset(1).Value) Then 'もし一つ下にデータがあれば
Set rngToProc = rngTop.CurrentRegion 'データ範囲を取得する(4行目もあり）
rngToProc.Offset(1).Copy '一行下からをコピー
WsText.Range("A2").PasteSpecial Paste:=xlPasteValues
End If
'トレーラー処理
If rngToProc Is Nothing Then 'データが一件も無かったら
lastRowDate = 4
Else
lastRowDate = Split(rngToProc.Address, "$")(4)
End If
WsDate.Rows(lastRowDate + 3).Copy 'データ最終行の3行下をコピー
lastRowText = WsText.Cells(Rows.Count, "A").End(xlUp).Row 'Textシートの最終行を取得
WsText.Rows(lastRowText + 1).PasteSpecial Paste:=xlPasteValues '転記
'エンド処理
WsText.Cells(lastRowText + 2, "A").Value = WsDate.Cells(lastRowDate + 6, "A").Value
'画面をTextシートに戻す
WsText.Activate
WsText.Range("A1").Select
Application.ScreenUpdating = True
End Sub
（半平太） 2017/05/18(木) 20:28
ありがとうございました。大変助かります。これで毎回の作業が
各段に楽になります。
ただ、ひとつわからないことがあります。
トレーラー処理でデータが一件もなかったらと記載がありますが、
If rngToProc Is Nothing Then 'データが一件も無かったら
lastRowDate = 4
Else
lastRowDate = Split(rngToProc.Address, "$")(4)
End If
ここの lastRowDate = 4 は、４行目のことで、
lastRowDate = Split(rngToProc.Address, "$")(4)の（4）も
４行目のことなのでしょうか。
実は、本番ではデータの項目行が10行目にあります。
If rngToProc Is Nothing Then 'データが一件も無かったら
lastRowDate = 10
Else
lastRowDate = Split(rngToProc.Address, "$")(10)
End If
としたところ lastRowDate = Split(rngToProc.Address, "$")(10)で
デバックしてしまいました。
どう修正したらよいかわからず困っています。
（ＭＩＥちゃん） 2017/05/19(金) 10:48
＞ここの lastRowDate = 4 は、４行目のことで、 ＞lastRowDate = Split(rngToProc.Address, "$")(4)の（4）も ＞４行目のことなのでしょうか。
(4)は、4行目じゃないです。(0から数えるので「5番目」の意味です）
データが存在していて、その範囲が「$A$4:$Z$9」だとした場合、 LastRowDateには「9」が入ることになります。
※範囲の文字列「$A$4:$Z$9」を「$」マークの区切り文字で分解(Split)すると、 配列に格納される文字は、0,1,2,3,4 番目に「"","A","4","Z","9"」が入るため。
・・なので、そこはいつも(4)でないと意味を為さないです。
（半平太） 2017/05/19(金) 11:17
ありがとうございました。
理解できました。
質問です。もし、項目行の下にバイト数やデータ型を表示するために
データ以外の行を追加し、その行はTextにコピーしないとしたら
どのようになるのでしょうか。
以下のような項目行で4〜6は、コピーしないです。ヘダーにもトレーラーにもデータにも同じようにデータ型とバイト数の行を入れます。
4 データ区分 契約番号 年月日 ・・・・
5 数値 数値 Date ・・・・
6 16 16 8 ・・・・
7 2 77777777 20161225 ・・・・
8 2 66666666 20170401 ・・・・
データ処理のところの
rngToProc.Offset(1).Copy '一行下からをコピー
を rngToProc.Offset(3).Copy としたところ、データの３行したまで
コピーされてしまいました。
（ＭＩＥちゃん） 2017/05/19(金) 12:21
＞rngToProc.Offset(1).Copy '一行下からをコピー
それは、最初の説明に従って、データ部分をコピーしているところですが、 rngToProcは項目行からなので、その初行を無視するため、一つ下にずらしたものをコピーしています。
当然、本来の最下行より一つ下も余分にコピーすることになります。
・・けど、そこは「空白行が必ずある」と言う説明だったので、無害との判断です(でした）。
ところが、今回追加された話は、初行だけじゃなく、3行無視することになったので、 下方にある余分な行も3行になり、空白じゃない行２つも含むことになった訳です。
こう云う話は初めに言って頂かないと再度検討し直さなければなら無くなります。
私は、二度手間な話は余り好みませんので、他の回答者のレスをお待ち頂くようお願いします。m(__)m
（半平太） 2017/05/19(金) 13:04
いろいろありがとうございました。
とても勉強になりました。
（ＭＩＥちゃん） 2017/05/19(金) 16:06
食指が動いた人は居なかったですかぁ・・
本番のデータ状況をご説明ください。 それさえ分かれば、誰でも回答してくれると思います。
推測では、下図かなと思うのですが、当初の説明には合わないから違っているのでしょうね。 ↓ ＞・表と表の間に１行ずつ空欄の行を作っています。 ＞・CとDのレコードは、常にCのレコードの最終行から決まった行だけ下 ＞（具体的にはCは６行下、Dは１２行下の行に存在します。)
<Dateシート>推測 行 _____A_____ _____B_____ ____C____ ____D____ 1 データ区分 名称 管理番号 ・・・ (注) 2 数値 数値 Date 3 16 16 8 4 1 あああああ 1234567 転記対象 5 6 データ区分 契約番号 年月日 ・・・・ 7 数値 数値 Date 8 16 16 8 9 2 99999999 20170515 ・・・・ 転記対象 10 2 88888888 20170510 ・・・・ 転記対象 11 2 77777777 20161225 ・・・・ 転記対象 12 2 66666666 20170401 ・・・・ 転記対象 13 2 55555555 20150612 ・・・・ 転記対象 14 15 データ区分 合計件数 合計金額 ・・・ 16 数値 数値 Date 17 16 16 8 18 3 5 500000 ・・・ 転記対象 19 20 データ区分 ダミー 21 9 転記対象
（半平太） 2017/05/20(土) 22:34
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.