[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『項目の並びが変動するデータを整備された表に反映させたい！』（ちとし）
基となるエクセルリストを用いて、
整備されたフォーマットへリスト反映をしたいのですが、
（関数ではない方法で）良案を教えてください。
条件としては
・基リストの項目は並びがバラバラ
ただし、項目名は フォーマットの項目名と同じもの
※フォーマットに無い項目もあるが、その項目は不要
・基リストの項目は1行だが、フォーマットは2行で構成
・レコード数は可変
・基リストのレコード開始位置はA2
フォーマットのレコード開始位置はA9
初心者のため、明記に過不足あるかもしれませんので、
必要な場合は ご質問、ご確認ください。
よろしくお願いします！！
＜ 使用 Excel：Excel2013、使用 OS：Windows7 ＞
>>基リストの項目は1行だが、フォーマットは2行で構成
具体的には、どういうことですか？
２行が結合セルになっている？ データ１行＋空白行１行？
それと、フォーマット側、整備された という表現ですが、罫線や結合セルなんかがある？ また、抽出する項目以外にも、いろんな文字列なんかがある？（摘要や備考等々）
（β） 2016/02/26(金) 13:45
具体的には、お客様からもらう「商品リスト」です。
それを、エンドユーザー向けのフォーマットに整備して渡す・・・ためのリストを作成
するためにTOOL化したいと考えています。
もらうリストは、項目が1行になっていますが
EU向けのリストは（名称の関係上、わかりやすくするために）
1行目と2行目を結合している項目と、
2段に分かれている項目（この場合は下段が貰ったリストの項目と同じ）になっています。
結合セルは項目としては存在しますが、レコードとしてはありません。
また、書式はそれぞれで、
JANコードや価格などの「数値」も持ってもいますが
値引き率などの「％」も持っています。
が、最悪そこは データ（内容）を持ってきた後に
書式変更を組み込めば良いかな と思っています。
※そこはマクロでも組めそうなので。
いかがでしょう？
（ちとし） 2016/02/26(金) 14:10
必要列のピックアップとその順番の組み換えは、フィルターオプションの得意技ですので、操作でも簡単ですし それをマクロ化しておくのもいいと思います。
ただし、具体的なレイアウトを提示いただかないと、できるかできないか、できるとしても、さらっとできるのか ひねくり回さなければできないのかがわかりません。
元リストは、１行目に項目名があって、２行目から淡々としたデータということはわかるのですが 「整備されたフォーマット」のシートを具体的に教えてください。
>>1行目と2行目を結合している項目と、 >>2段に分かれている項目（この場合は下段が貰ったリストの項目と同じ）になっています。 >>結合セルは項目としては存在しますが、レコードとしてはありません
読解力が乏しく、まったくわかりません。
[[20110209184943]] 『[談]シートレイアウトの投稿どうしてますか？』（ｍｏｍｏ）
↑で提供されているユーティリティを使って具体的なシートイメージをアップされてはいかが？
（β） 2016/02/26(金) 14:23
↑ レイアウトアップは 元シートと「整備されたフォーマット」シートの両方が必要でしょうね。
（β） 2016/02/26(金) 14:25
基シート
A B C D E 1 2 3 4 5
フォーマット シート
A B C D E 1 2 3 4 5
こんな感じで例示してみては？ （GobGob） 2016/02/26(金) 14:49
↓基のリスト
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L] |[M] |[N] |[O] |[P] |[Q] |[R] |[S] |[T] |[U] |[V] |[W] |[X] |[Y] |[Z]|[AA] |[AB] |[AC] |[AD] |[AE] |[AF] |[AG] |[AH]
[1]|ID |不要1|イメージ画|不要2|◇カテゴリーCD|◇ソートCD|カテゴリー|登録日|商品コード|ブランド名|不要3 |商品名 |規格 |入数|不要4|JANコード |引当|売価|通常|特別|原価引期間|不要5|不要6|不要7|不要8|酒 |エリア1|エリア2|エリア3|エリア4|不要9|不要10|不要11|備考
[2]| 25| 1| 25| | 1| 1|ＹＹＹ |既存 | 300003|Ａ社 |ＹＹＹ |ＹＹＹ |20.7g| 10|標準 | 40000000 | 1| 204| | | | | | | | | 1| 1| 1|− | | | |
[3]| 26| 2| 26| | 1| 1|ＤＤＤ |既存 | 0000119|Ｙ社 |ＤＤＤ-1|ＤＤＤ1|20.8g| 10|標準 | 49000001 | 1| 204| | | | | | | | | 1| 1| 1|− | | | |
[4]|5003| 3| 2| | 1| 1|ＫＫＫ |1/11 | 3600001|Ｅ社 |ＤＤＤ-2|ＤＤＤ2|90g | 5|標準 |4000000000001 | 1| 149| | | | | | | | |− |− | 1|− | | | |
※項目位置（列）が移動する事がありますが、項目名は変わりません
↓フォーマット
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L] |[M] |[N] |[O] |[P] |[Q]|[R] |[S] |[T] |[U] |[V]
[1] |タイトル | | | | | | | | | | | ?@色説明 | | | | | | | | | |
[2] |サブタイトル| | | | | | | | | | | ?A色説明 | | | | | | | | | |
[3] |表説明（1） | | | | | | | | | | | ?B色説明 | | | | | | | | | |
[4] |表説明（2） | | | | | | | | | | | ※注意事項| | | | | | | | | |
[5] |表説明（3） | | | | | | | | | | | | | | | | | | | | |
[6] |※補足 | | | | | | | | | | | | | | | | | | | | |
[7] |カテゴリー |登録日|商品コード|ブランド名|商品名|規格 |売価|値入率| |原価引期間 | 入数 | 引当 |JANコード（画）|JANコード |発注日|イメージ画|酒 |エリア1|エリア2|エリア3|エリア4|ID
[8] | | | | | | | |通常 |特別| | | | | | | | | | | | |
[9] |ＡＡＡ |既存 | 3000003|Ａ社 |商品Ａ|20.1g|208 |32.1% |1.1%|4/1〜5/15 | 10 | 1 | | 40000000 | | | | 1| 1| 1|− | 1
[10]|ＡＡＡ |既存 | 0000009|Ｂ社 |商品Ｂ|10.9g|150 |32.1% |1.1%|4/5〜5/5 | 20 | 1 | | 400000091 | | | | 1| 1| 1|− | 19
[11]|ＢＢＢ |1/10 | 3600000|Ｃ社 |商品Ｃ|90g |111 |32.1% |1.1%|4/1〜5/20 | 5 | 1 | |4900000000001 | | | |− |− | 1|− | 36
[12]|ＣＣＣ |2/1 | 5000001|Ａ社 |商品Ｄ|350ml|223 |32.1% |1.1%|4/1〜5/15 | 12 | 2 | |4901000000008 | | | 1| 1| 1| 1| 1|5001
※並び（列）・項目は（名前も含め）変動しません。
こんなカンジですが、イメージ沸きますか？
（ちとし） 2016/02/26(金) 15:19
よろしくお願いします！
（ちとし） 2016/02/26(金) 15:46
確認させてください。
１．フォーマットのタイトルで、以下のものが元表にはありません。その理解でいいですか？
JANコード（画）、発注日、イメージ画、酒
２．フォーマットの7行目、8行目の各列のセルはＨ列，Ｉ列を除き結合されているという理解でいいですか？
３．目的は、元表から、このフォーマットの ９行目以降に、抽出するということですね。
４．毎回、送られてきた元表から、そこにあるデータだけから、あらたにフォーマットを作成するということでいいですね？
４．イメージしている構成を聞かせてください。
１）マクロ実行時に、客先から送られた元表のブックが開かれている。 ２）フォーマットシートはマクロブックに存在する。 ３）そのフォーマットを新規ブックにコピーして、フォーマットシートのみのブックを作り上げる。
こんな流れでいいですか？
（β） 2016/02/27(土) 16:53
↑ 酒 は元表にありましたね。
（β） 2016/02/27(土) 16:58
とりあえず雛形として。 おそらく、ここはこうしてほしいとか、実はここはこうなっているなんてことがてんこ盛りでレスされると思いますが。
先にコメントした前提で書いています。 新規ブックとして作り上げるところまで。★印のところは実際のブック名、シート名に直してください。 (フィルターオプションで取り込むための作業シートを自動生成し、そのシート経由でデータ反映しています。）
Sub Sample()
Dim shW As Worksheet
Dim shO As Worksheet
Dim shF As Worksheet
Application.ScreenUpdating = False
Set shO = Workbooks("客先ブック.xlsx").Sheets("Sheet1") '★客先から送付されたブックの元表シート
Set shW = ThisWorkbook.Worksheets.Add(before:=ThisWorkbook.Worksheets(1)) '作業用シートの生成
ThisWorkbook.Sheets("フォーマット").Copy '★マクロ内フォーマットシートから新規ブック生成
Set shF = ActiveSheet
'作業用シートに客先シートの取り込み対象項目の項目名をセット
shW.Range("A1:S1").Value = Array( _
shO.Range("G1").Value, _
shO.Range("H1").Value, _
shO.Range("I1").Value, _
shO.Range("J1").Value, _
shO.Range("L1").Value, _
shO.Range("M1").Value, _
shO.Range("R1").Value, _
shO.Range("S1").Value, _
shO.Range("T1").Value, _
shO.Range("U1").Value, _
shO.Range("N1").Value, _
shO.Range("Q1").Value, _
shO.Range("P1").Value, _
shO.Range("Z1").Value, _
shO.Range("AA1").Value, _
shO.Range("AB1").Value, _
shO.Range("AC1").Value, _
shO.Range("AD1").Value, _
shO.Range("A1").Value)
'客先シートから作業シートにフィルターオプションでデータ取り込み
shO.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=shW.Range("A1").CurrentRegion
'作業用シートからフォーマット新規ブックにコピー
With shW.Range("A1").CurrentRegion.Offset(1)
.Columns("A:L").Copy shF.Range("A9")
.Columns("M").Copy shF.Range("N9")
.Columns("N:S").Copy shF.Range("Q9")
End With
'作業用シートの削除
Application.DisplayAlerts = False
shW.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "取り込みました"
End Sub
（β） 2016/02/27(土) 17:44
ご対応、ありがとうございます。
少し説明不足だったことを反省します。
レスが遅くなってしまいましたが・・
1.「フォーマット」の項目で「基のリスト」に無いものは？
→使用しない項目なので、スルーしたいです。
2.フォーマットの7行目、8行目の各列のセルはＨ列，Ｉ列を除き結合されているという理解でいいですか？
→そうです。H:I列のみ、7行目に「主タイトル（？）」が入ってしまっているため、
実使用する項目名としては8行目にある状態です。
3.目的は、元表から、このフォーマットの ９行目以降に、抽出するということですね。
→その通りです。
4.毎回、送られてきた元表から、そこにあるデータだけから、あらたにフォーマットを作成するということでいいですね？
→少し違いますか・・・
新しいフォーマットを作るんではなく、
「フォーマット」の9行目にデータを入れ込んでいく・・かんじです。
あくまでも「フォーマット」なので、固定のブック・シートとして考えています。
なぜなら、フォーマットシートの項目部分（7：8行目）には
すでに前任者の作成したマクロが設定されており、できればそこはそのまま使いたい機能だからです。
新規で作成でないと 難しいでしょうか？
イメージしているのは・・・
お客様からのファイルの「基リスト」シートの1行目の項目で
作業ファイルの「フォーマット」シートの7行目（H:I列のみ8行目）と同じ項目を判別し、
2行目以降のデータ内容を「フォーマット」シートの9行目の同じ項目名列に挿入
※「基リスト」の項目名は変更しないが、位置（列）はズレる可能性アリ。
※「フォーマット」シートは（パスも含めブックごと）固定のもの。
※「フォーマット」シートの項目名（7:8行目）には、別マクロが設定されている。
こういった感じなんですが・・・可能でしょうか？
よろしくお願いします。
（ちとし） 2016/02/29(月) 09:39
>>こういった感じなんですが・・・可能でしょうか？
はい、可能でしょうね。 ただし、そちらのイメージと、こちらのイメージ（理解）がマッチすればですけど。
フォーマットシートというのは、マクロブックとは別の ●●●.xlsm というブックなんですね。 それとも、その ●●●.xlsm に、今回の処理を行うマクロを追加するということでしょうか？ （いずれでもできますので）
>>※「フォーマット」シートの項目名（7:8行目）には、別マクロが設定されている。
７：８行目にマクロが設定されているという意味が？？？？です。
>> 新しいフォーマットを作るんではなく、 >> 「フォーマット」の9行目にデータを入れ込んでいく・・かんじです。
はい、それは認識が同じです。 確認したかったのは、たとえば、前回の処理で 9行目から20行目にデータ取り込みが行われていた。 で、今回処理は、前回のものを残し、21行目から追加するのか、今までのものは消して、9行目から、あらためて データを取り込むのか いずれですかということでした。 後者ですね。
いずれにしても、（まだまだ想像しながらですけど）改訂版を書いてみます。
（β） 2016/02/29(月) 09:50
●●●.xlsm に、今回の処理を行うマクロを追加 の方です。
７：８行目にマクロが設定されている
→右クリックでリストが表示され、そのリストを選択すると処理が実行される
マクロ？モジュール？（初心者のため、うまく説明できなくてすみません！）が組み込まれているんです。
そこはそのまま残したいのですが、項目が変更してしまったため
データがうまく持ってこれなくなってしまった・・・というわけです。
お手数おかけします。
（ちとし） 2016/02/29(月) 10:54
以下の流れです。 ★印のところは実際のフォルダパス、ブック名、シート名にしてください ・客先ブックを開き（客先ブックの対象シート、シート名を特定せず、一番左側のシートにしてあります） ・マクロブック内で、作業用シートを生成し ・作業用シートに必要列を取り込んで ・フォーマットシートにコピーし ・作業用シートを削除 ・客先ブックを保存なしで閉じる。
Sub Sample()
Dim shW As Worksheet
Dim shO As Worksheet
Dim shF As Worksheet
Dim pathO As String
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
pathO = CreateObject("WScript.SHell").SpecialFolders("DeskTop") & "\Test\" '★客先ブックが保存されているフォルダパス
Set shO = Workbooks.Open(pathO & "客先ブック.xlsx").Sheets(1) '★客先から送付されたブックの元表シート
Set shF = ThisWorkbook.Sheets("フォーマット") '★マクロブック内のフォーマットシート
Set shW = ThisWorkbook.Worksheets.Add(before:=ThisWorkbook.Worksheets(1)) '作業用シートの生成
'作業用シートに客先シートの取り込み対象項目の項目名をセット
shW.Range("A1:S1").Value = Array( _
shO.Range("G1").Value, _
shO.Range("H1").Value, _
shO.Range("I1").Value, _
shO.Range("J1").Value, _
shO.Range("L1").Value, _
shO.Range("M1").Value, _
shO.Range("R1").Value, _
shO.Range("S1").Value, _
shO.Range("T1").Value, _
shO.Range("U1").Value, _
shO.Range("N1").Value, _
shO.Range("Q1").Value, _
shO.Range("P1").Value, _
shO.Range("Z1").Value, _
shO.Range("AA1").Value, _
shO.Range("AB1").Value, _
shO.Range("AC1").Value, _
shO.Range("AD1").Value, _
shO.Range("A1").Value)
'客先シートから作業シートにフィルターオプションでデータ取り込み
shO.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=shW.Range("A1").CurrentRegion
'作業用シートからフォーマットシートにコピー
shF.Range("A1", shF.UsedRange).Offset(8).ClearContents '取り込み領域のクリア
With shW.Range("A1").CurrentRegion.Offset(1)
.Columns("A:L").Copy shF.Range("A9")
.Columns("M").Copy shF.Range("N9")
.Columns("N:S").Copy shF.Range("Q9")
End With
'作業用シートの削除
Application.DisplayAlerts = False
shW.Delete
Application.DisplayAlerts = True
shO.Parent.Close False '客先ブックを保存なしで閉じる
Application.Goto shF.Range("A1") 'フォーマットシートを表示
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
MsgBox "取り込みました"
End Sub
（β） 2016/02/29(月) 11:39
すみません、肝心な部分をお伝えできていませんでした。
（というか、こんな仕様になっていると認識できていなかったので・・・）
最終的にデータを反映させる「フォーマット」シートは
”通常非表示”の指定になっており、
表示させても 他のシート作業でシートを移動した時点で”非表示”になってしまうマクロ（？）が
組み込まれていました。
※たぶん、作業者が誤って削除してしまわないために設定されているんだと思われます。
この仕様のせいで（？）
＞Application.Goto shF.Range("A1") 'フォーマットシートを表示
で、デバックになってしまいます。
何度も追記が多く、申し訳ありません！！
（ちとし） 2016/02/29(月) 13:04
で、どうしましょうか？ できあがりのフォーマットシートを表示しなくていいなら
Application.Goto shF.Range("A1") 'フォーマットシートを表示
このコードをなくしてください。
もし、確認も含めて表示するなら
このコードの前に
shF.Visible = True を入れてください。
どちらでも、お好きなように。
（β） 2016/02/29(月) 13:23
上記 非表示の件は、なんとかなりそうです。
ご教示、ありがとうございます。
問題点がまた発見されまして・・・
「フォーマット」というだけあって、
「セルの書式」や「行高」なども「START」シート（各実行マクロが組まれたボタンがあるシート）で
指定できるようになっています。
フォントはT3、フォントサイズはT4、行高はT5で。
また、文字の中揃えや表示形式については、「フォーマットシート」で設定済み
のものを使用して、フォーマットにデータを反映させることは可能ですしょうか？
お手数おかけして すみません。
（たぶん、これで 抜け・漏れは無いと思います。）
（ちとし） 2016/02/29(月) 13:30
With shW.Range("A1").CurrentRegion.Offset(1)
.Columns("A:L").Copy shF.Range("A9")
.Columns("M").Copy shF.Range("N9")
.Columns("N:S").Copy shF.Range("Q9")
End With
これを
With shW.Range("A1").CurrentRegion.Offset(1)
shF.Range("A9:N9").Resize(.Rows.Count).Value = .Columns("A:L").Value
shF.Range("N9").Resize(.Rows.Count).Value = .Columns("M").Value
shF.Range("Q9:V9").Resize(.Rows.Count).Value = .Columns("N:S").Value
End With
こうかえて試してみてください。
（β） 2016/02/29(月) 14:41
STARTシートの「T3・T4・T5」セルに指定された（それぞれ フォント・フォントサイズ・行高）を持って
値貼付・・・は、難しいですか？
指定が多く、申し訳ありません。
（ちとし） 2016/02/29(月) 15:01
いやぁ・・・・よくわからないのですけど・・・・
なぜSTARTシートで指定するんでしょうか？ フォーマットシートそのものにフォント、フォントサイズ、行高を必要なものに設定しておけばいいのでは？ そのほかの書式は設定してあるんですよね。
フォーマットシートは、フォント、フォントサイズ、行高設定なし。 どうしても、STARTシートで指定したいということなら やりますけど・・・
やるとしても、9行目以降ですね。
（β） 2016/02/29(月) 15:29
フォントとフォントサイズと行高を「START」シートの指定セルから
「フォーマット」シートの9行目に書式設定し、
そこから最終行まで（他の書式も含め）を・・（さっきβさんが教えてくださったコード）
で
いけますかね？
（よっぴー） 2016/02/29(月) 15:36
調べたいことがあると、
後輩にPCを貸したら、ここに別名で掲載していたようで
私の名前が「よっぴー」となってしまっています・・・
別人ですので！！
（ちとし） 2016/02/29(月) 15:42
もしくは、
「フォーマット」シートに値貼付は 可能ですか？
（ちとし） 2016/02/29(月) 15:58
横から失礼します。 ＞基のリスト（客先ブック） ＞※項目位置（列）が移動する事がありますが、項目名は変わりません 書式を整える以前に、現時点のプログラムですが、 客先ブックの項目の列がかわったものに、対応してませんよね。 フォーマットシートからPickupした項目名のうち、客先シートの項目名にあるものを 作業シートの項目名にすべきですよね。
また、客先ブックのC列のイメージ画は、フォーマットで項目(P列)があるのに反映されてませんね。 （マリオ） 2016/02/29(月) 17:26
は！確かに・・・
これでは、列の移動があった場合、項目ズレを起こしてしまいますね・・・。
ちなみに、
「フォーマット」シートの項目になるセルには
「セルの名前」が付けてあり、そこをKeyに読み込む・・とい方法も可能のようです。
（前任者が作りかけで退職してしまったので、きちんと組まれていませんでしたが。）
この場合、どうでしょうか？
※例えばA7をセル名：A・・・として組めるのであれば・・・・
（ちとし） 2016/02/29(月) 17:44
「フォーマット」シートに値貼付は 可能ですか？
どうも、そちらのレスに違和感を感じます。 実行した結果、こうなった、そうではなく、このようにできないかというのならわかりますが。
書式のことを指摘されたので、あぁそうだねということで （β） 2016/02/29(月) 14:41 で それまでのコピペから値転記にしています。
（β） 2016/02/29(月) 14:41 の改定を反映しても、なおかつ値貼り付けにはなっていなかったということですか？？
★ところで、下記マリオさんからの指摘による不具合、解消する方策は、最後に、落ち着いたら提言します。 それまでは、今のレイアウトが固定だということで検証願います。
To マリオさん
>>※項目位置（列）が移動する事がありますが、項目名は変わりません
ご指摘のポイント、列がへんかする可能性がある部分を見過ごしていました。 客先から送られたデータをフィルターオプションで抽出するケースで落ちる泥沼、うけがわで設定した項目名と 客先データの項目名がマッチしない、スペースがついていたり、大文字、小文字があったり 等々 を避けるため 項目名は客先データから持ってきたんですが、列が変わると、それはできなくなりますね。 しょうがないので、項目名は絶対に同じものをセットするという前提で構成を少しかえます。 フォーマットシートには客先シートにない項目名があるので、作業用シートをクッションにしますけど。
（β） 2016/02/29(月) 17:46
こちらの対応が悪く、気分を害してしまったようでしたら、大変申し訳ありません。
早くレスをしなくては！というのと、教えていただいたものを反映する作業を
平行して行ってしまっていたため、確認しない内にコメントを出してしまっておりました。
失礼いたしました。
With shW.Range("A1").CurrentRegion.Offset(1)
shF.Range("A9:N9").Resize(.Rows.Count).Value = .Columns("A:L").Value
shF.Range("N9").Resize(.Rows.Count).Value = .Columns("M").Value
shF.Range("Q9:V9").Resize(.Rows.Count).Value = .Columns("N:S").Value
End With
で、値貼付けになりました。
また、双方 現状の項目名・並び ですと、うまく反映されました。
※少し追加になった項目がありましたが、
そこはβさんにご教示いただいたコードから作ることは容易でした。
ありがとうございます！
基本的な理解と処理スピードが低く、本当に申し訳ありませんでした。
ちなみに、マリオさんのご指摘とβさんの疑念点ですが・・・
「列の変動（配列変更？）」と「項目名の変動」については、
前者は発生する可能性が高く、後者は発生しない
（サンプルにあげた項目名では、多少違いがあったかもしれませんが、急ぎ「フォーマット」シートを「基リスト」の項目名に合わせています。）
方向で、ご認識いただければと思います。
せっかく教えていただいているにも関わらず、
不快感や不信感を抱かせるような やりとりをしてしまい
大変申し訳ありませんでした。
今後は「スピード感」よりも、「きちんとした確認」を優先で考えてゆきますので
どうか継続してご指導いただけますと幸いです。
よろしくお願い致します。
（ちとし） 2016/02/29(月) 18:14
いやいや、不快というのではなく、Ｑ／Ａは、その時点のお互いのポジションをしっかりと把握しながら 進めていかなければ、大きな回り道になるおそれがあるので、ちょっときつめにコメントしてしまいました。 ゴールは、すぐそこなので、焦らずにいきましょう。
さて、これまでの構造は
１．客先シートから必要列項目タイトルを作業用シートに取り込み ２．フィルターオプションでデータを抜出し ３．それをフォーマットシートの転記
こういうものでした。しかしながら、客先シートの項目の列が変更になる可能性があるわけですから １．では具合悪いですね。したがって
フォーマットシートから、抽出必要列の列タイトルを作業シートに抜出して、そのタイトルを頼りに 客先しーとからフィルターオプションでデータを抽出して、フォーマットシートに転記。
このように変えます。 ですから、フォーマットシート上で、客先シートから抜き出す項目の項目名は、客先シートのものに 【完全に】あわせておいてください。（スペースがくっついているとか、いないとか、大文字、小文字とか 等々）
それと、マリオさんから指摘がありましたが、フォーマットシート、客先シート ともに存在する イメージ画 については 処理の対象に（今のところ）含めていません。ここには 図 が貼り付けられているのでしょうか？ 図 はフィルターオプションでの抽出ができません。やるなら、ここだけ、まったく別の取り込み方法になります。 いずれ、やるとしたら、客先シートのセルに貼り付けられた図を、フォーマットシートの当該列のセルのどこに、どのように 貼り付けるのかを決めておいてください。行の高さ等、ことなっているはずですので。
今度アップするコードには STARTシートの「T3・T4・T5」セルの フォント・フォントサイズ・行高 をフォーマットシートの9行目以下に適用する部分も含めます。 もう、これが不要になっているなら、カットしてください。
また、そちらでは、列の増減等、やっておられると思いますが、コードは、これまで説明のあったレイアウトのままにしますので、適宜調整願います。
では、コードを書き始めます。しばらくお待ち下さい。
（β） 2016/02/29(月) 21:03
コードです。 なお、STARTシートのフォント(T3)ですが、フォント名にしてあります。太字等のフォント名以外の属性なら、そこは適当に変えてください。 くれぐれも、フォーマットシートの項目名と客先シートの項目名、完全に合わせてテストしてくださいね。
Sub Sample2()
Dim shW As Worksheet
Dim shO As Worksheet
Dim shF As Worksheet
Dim shS As Worksheet
Dim pathO As String
Dim myRows As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
pathO = CreateObject("WScript.SHell").SpecialFolders("DeskTop") & "\Test\" '★客先ブックが保存されているフォルダパス
Set shO = Workbooks.Open(pathO & "客先ブック.xlsx").Sheets(1) '★客先から送付されたブックの元表シート
Set shF = ThisWorkbook.Sheets("フォーマット") '★マクロブック内のフォーマットシート
Set shS = ThisWorkbook.Sheets("START") '★マクロブック内のSTARTトシート
Set shW = ThisWorkbook.Worksheets.Add(before:=ThisWorkbook.Worksheets(1)) '作業用シートの生成
'作業用シートに客先シートの取り込み対象項目の項目名をセット(項目名はフォーマットシートより転記）
shW.Range("A1:G1").Value = shF.Range("A7:G7").Value
shW.Range("H1:I1").Value = shF.Range("H8:I8").Value
shW.Range("J1:L1").Value = shF.Range("J7:L7").Value
shW.Range("M1").Value = shF.Range("N7").Value
shW.Range("N1:S1").Value = shF.Range("Q7:V7").Value
'客先シートから作業シートにフィルターオプションでデータ取り込み
shO.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=shW.Range("A1").CurrentRegion
'作業用シートからフォーマットシートにコピー
shF.Range("A1", shF.UsedRange).Offset(8).ClearContents '取り込み領域のクリア
With shW.Range("A1").CurrentRegion.Offset(1)
myRows = .Rows.Count - 1
shF.Range("A9:N9").Resize(myRows).Value = .Columns("A:L").Value
shF.Range("N9").Resize(myRows).Value = .Columns("M").Value
shF.Range("Q9:V9").Resize(myRows).Value = .Columns("N:S").Value
End With
'STARTシートに設定されたフォント、フォントサイズ、行高の適用
With shF.Range("A9:V9").Resize(myRows)
.Font.Name = shS.Range("T3").Value
.Font.Size = shS.Range("T4").Value
.EntireRow.RowHeight = shS.Range("T5").Value
End With
'作業用シートの削除
Application.DisplayAlerts = False
shW.Delete
Application.DisplayAlerts = True
shO.Parent.Close False '客先ブックを保存なしで閉じる
Application.Goto shF.Range("A1") 'フォーマットシートを表示
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
MsgBox "取り込みました"
End Sub
（β） 2016/02/29(月) 21:46
To βさん ＞【完全に】あわせておいてください。（スペースがくっついているとか フォーマットシート及び客先シートの項目をTRIM関数でスペース除去しましょうよ(^^♪
To ちとし さん フォーマットシートの9〜20000行目に、書式、★表示形式、行高をあらかじめ設定する別マクロです。
'新規シート作成（シート名：書式）してください。 'フォーマットシートのA9: V9に設定したい書式（及び表示形式）を、書式シートのA1:V9にて、あらかじめ設定 '書式シートのA1:V9をA9:V9にコピー（ただし書式のみ）、その後、A9:V9を20000行までコピー
Option Explicit
Sub 書式と表示形式のコピー()
Const mxr As Long = 20000 '★9〜20000行まで、設定（書式、表示形式、行高）
Dim shF As Worksheet, shA As Worksheet
Set shF = ThisWorkbook.Sheets("フォーマット")
Set shA = ThisWorkbook.Sheets("書式")
Set shS = ThisWorkbook.Sheets("START") '★
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
shF.Range("A1", shF.UsedRange).Offset(8).ClearContents '値のみ消去
shA.Range("A1:V1").Copy
shF.Range("A9:V9").PasteSpecial Paste:=xlPasteFormats '書式（及び表示形式）のみ貼り付け
Application.CutCopyMode = False
'20000行前までコピー
'（※あまり気にする必要はないが、UsedRangeを考慮、Offset(8)している為）
shF.Range("A9:V9").AutoFill Destination:=shF.Range("A9:V" & mxr), Type:=xlFillDefault
shF.Range("A9:V" & mxr).EntireRow.RowHeight = shS.Range("T5").Value '★行高
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
MsgBox "設定終了（書式、表示形式、行高）" & vbCrLf & _
shF.Name & " シートの9〜" & mxr & "行"
End Sub
（マリオ） 2016/03/01(火) 01:19
お二人とも、遅くまでご対応ありがとうございます！
これからご教示いただいたコードを反映させ 動作確認しますが、
取り急ぎ お礼を先に、と。
何かありましたら、ご相談させてください。
※少し時間はかかると思いますが・・・。
よろしくお願いします。
（ちとし） 2016/03/01(火) 09:19
当初から項目が追加になったりしているため、
ここからは自身での微調整になりますが
お二人からご教示いただいたコードのおかげで
思っていたデータ反映ができそうです。
何度も何度もやりとりを重ね、お時間を割いて改修をしていただき、
本当にありがとうございました！
この後（既に組まれているもの）との連動で
問題が生じるようでしたら、
改めて質問を立てさせていただくかもしれませんが・・・
取り急ぎ、この質問に関しては終了としていただいて大丈夫そうです。
ありがとうございました！
（ちとし） 2016/03/01(火) 10:09
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.