マクロで考えているのですが、

質問

教えてください！
シート名が、１回目、２回目、３回目、４回目、その隣からＡ会社、田中会社、小山田会社・・・・・このよう漢字のシート名で書かれたシートが続くのですが、
やりたい事は、シート１回目のセルＡ7：Ｌ210の中に各会社のシートのＡ7：Ｌ210の文字が書かれてる部分だけコピーして１回目にＡ7：Ｌ210張り付けたいのですが、
会社のシートのデータの数は、会社でまちまちなので、文字が書かれている、文字だけコピーして
張り付けるときは、Ａ7から詰めて張り付けさせたいのです。
文字がある部分だけコピーしたいのですが、各会社のデーターは、別のブックから参照させてるので、
セルに式が書かれているのですが、可能でしょうか、
同じ操作を２回目、３回目、４回目、のシートで行いたいのですが、

他で書いてあるマクロを参考に作ろうとしましたが、うまく動きませんでした。
知識不足でスミマセンが、よろしくお願いします。

回答No.9

merlionXX · ベストアンサー

ANo4-5-7 merlionXXです。

>　シートの順番ですが、一回目、２回目、３回目、４回目、その他の振込、福岡、配工、熊本、三友、名古屋、の順番です、ほしい会社データーは、福岡～名古屋までです。

一回目、２回目？漢数字と数字が混在しているのですか？
ま、どうでもいいですが、これがほんとに正しいなら、左から6番目～最後のシートまでの　7～210行のデータを見ればいいのですね？
わたしが前提として書いた、「それぞれのシートのA7からA210まで見て、そこが空白（数式で=""も空白）でなかったら、その行のL列までコピー」について何もコメントがありません。
他の方への補足で空白とは、数式で０が出るものを表示だけ空白にしているとのことなのでそこは直しますが、A列だけで判断していいのか悪いのか、についてはお答えがないので、それでいいものとします。
あと、コードを提示いただけなかったので、エラーの原因がわかりません。
とりあえずはシート名は使わず、シートの左からの順番で判断しています。（あくまで非表示シートがないことが前提ですが、ないですね？）

Sub test02()
　　Dim n As Long, i As Long, j As Long
　　n = 7
　　Application.ScreenUpdating = False
　　For j = 6 To Worksheets.Count '左から6番目～最後のシートまで
　　　　For i = 7 To 210 'iに7～210を代入
　　　　　　With Worksheets(j) 'J番目のシートで
　　　　　　　　If .Cells(i, "A") <> 0 Then 'A列i行目が0でないなら
　　　　　　　　　　.Cells(i, "A").Resize(, 12).Copy 'そこからL列までコピー
　　　　　　　　　　Sheets(1).Cells(n, "A").PasteSpecial Paste:=xlPasteValues　'1枚目シートA列ｎ行目に値貼り付け
　　　　　　　　　　Sheets(1).Cells(n, "A").PasteSpecial Paste:=xlFormats '書式貼り付け
　　　　　　　　　　n = n + 1 'カウント
　　　　　　　　　　Application.CutCopyMode = False
　　　　　　　　End If
　　　　　　End With
　　　　Next i
　　Next j
　　Application.ScreenUpdating = True
End Sub

お礼

大変遅くなりました。何度もあるが当ございます。
うまくゆきませんです。
なんだか申し訳なく落ち込みそうです。

補足

エラーNO1004　メゾットの失敗と出ました、あと実は、
貼り付け先が、A列でなくB列でした（非表示になってました）
これはSheets(1).Cells(n, "A").PasteSpecial
を　　　Sheets(1).Cells(n, "B").PasteSpecial 　にすればOKですよね！
コピーしたものを張るつけるときは、形式を選択して貼り付けで
値と数値の書式（A)で張り付けています。関係がありますか、
重ね重ねすみません！

回答No.10

merlionXX

ANo4-5-7-9 merlionXXです。

> エラーNO1004　メゾットの失敗と出ました、

あなたが自分の書いたコードを開示しない以上、こちらではわかりません。
すくなくとも私の書いたコードをそのままコピペしただけならエラーにはならないと思いますので。

> 貼り付け先が、A列でなくB列でした（非表示になってました）
> これはSheets(1).Cells(n, "A").PasteSpecial
> を　　　Sheets(1).Cells(n, "B").PasteSpecial 　にすればOKですよね！

そうです。

> コピーしたものを張るつけるときは、形式を選択して貼り付けで
> 値と数値の書式（A)で張り付けています。関係がありますか、

ANo3への補足で、「貼り付けは、文字や罫線などもはりつけたいのです。」とお書きでしたので、値と書式を貼り付けるようにしたつもりですが、わたしの書いたコードでそう張り付いたのですか？
ならば、エラーはどこででたのですか？

とにかくこちらが知りたい情報が満足にでてこないとわたしはあなたのエクセルをみられないのですからどうしようもありません。

回答No.8

keithin

問題の解決の糸口（平たく言えば「あなたは教わった事のどこが気に入らないんですか？いったいどうなれば満足するんですか？」ということです）が一向に不明のまま，何度も回答するのは大変苦痛なのですが

＞私の質問がおかしいので、現状では、コピー仕方が、うまくゆきませんでした

結局のところ「どうなって欲しい」のですか？ちゃんと質問して下さい。具体的に。正しく詳細に。目に見えるように。「うまくいきません」だけじゃ，何が気に入らないのか一体どうしたいのか，さっぱり内容がありません。

回答のマクロだとどういう結果が出るのかは，回答者は当然理解しています。
「下に向けて詰めたい」なら，上から順に詰めるマクロを回答しています。

そうじゃなく「実際のブックはこういう並びでこういう名前のシートがあります」「実際のデータはこのセルにこういう内容です」のときに「こういう具合に並んだ結果が欲しいです」がぜんぜん具体的な様子で示されていないので，いつまでたっても「欲しい結果が教われない」のだという事を理解して下さい。

＃３度目の正直になって欲しいです。（上述したようどうしたいのか不明のままなので，これでイイ根拠は何もありませんが）

sub macro3()
　dim i as long
　dim n as long
　dim h as range

’会社シートの巡回
　for i = 5 to worksheets.count
　’各会社シート内の巡回
　　for each h in worksheets(i).range("A7:L210")
　　'数式がゼロや数字を計算していなければ転記する
　　if h <> "" and not isnumeric(h) then
　　　worksheets("１回目").cells(7 + n mod 204, 1 + n \ 204).value = h.value
　　　n = n + 1
　　end if
　　next
　next i
end sub

＃シート名を間違えないようにキチンと正しいマクロに直してから実行すること
＃シートの並びは前回回答した注意をよく読んでその通りにして実行すること

＞シート名が漢字時ならば

いったい何を言ってるのですか？全く無関係です。

お礼

重ね重ねありがとうございます。
指摘の事を守り頑張ってみます。
ありがとうございました。

回答No.7

merlionXX

ANo4-5 merlionXXです。

> Range　pattcspccial　メゾットが失敗とでたのですが
> Sheets("一回目").Cells(n, "A").PasteSpecial (xlPasteValues)
> この部分のどこが、問題なのでしょうか、

シート名が違っているようですね。
お書きになったコードと正しいシート名を左から順番に教えてください。

お礼

重ねてありがとうございます。
シート名が違うとの指摘があり自分なりに試したのですが、
うまくゆきません　しばらくお付き合い下さいすみません！

補足

シートの順番ですが、一回目、２回目、３回目、４回目、その他の振込、福岡、配工、熊本、三友、名古屋、の順番です、ほしい会社データーは、福岡～名古屋までです。遅くなりました。

回答No.6

keithin

ANo.2の改訂です。
●１回目分のマクロ
●「ゼロを表示していない」に対応
●ゼロはスルーして，ご質問であなたの言ったことにより文字だけを転記
●これもあなたの説明通り「シート名が、１回目、２回目、３回目、４回目、その隣からＡ会社、田中会社、小山田会社・・・・・このよう漢字のシート名で書かれたシートが続く」というシートの並びが前提

sub macro2()
　dim i as long
　dim n as long
　dim h as range

’会社シートの巡回
　for i = 5 to worksheets.count
　’各会社シート内の巡回
　　for each h in worksheets(i).range("A7:L210")
　　'数式がゼロや数字を計算していなければ転記する
　　if h <> "" and not isnumeric(h) then
　　　worksheets("１回目").cells(7 + n \ 12, 1 + n mod 12).value = h.value
　　　n = n + 1
　　end if
　　next
　next i
end sub

「ここが不足してますよ」の指摘にも返答がないのは，自分から説明するつもりは無いという事ですから，そういうことならこれ以上ちょっとフォローもできかねますね。

お礼

御礼と捕捉が遅くなり済みません、
ありがとうございます。
NO2に書かせていただきました。
回答いただいたマクロでテストさせていただきました。
シート名が漢字時ならば、シートの指定をせずに済むので、たいへんたすかります。私の質問がおかしいので、現状では、コピー仕方が、うまくゆきませんでしたが、マクロは、動きました、自分なりにこれを元に考えてますが、
捕捉を元に教えていただければありがたいです。
失礼いたします。

回答No.5

merlionXX

ANo4　merlionXXです。
ANo1への補足を見ました。

> 参照元のデーターない部分は、空白で表示されるので、
> （単に条件付き書式で、0表示しないにしてるだけです）

最初からそう書いてくださいな。
ならば
If .Cells(i, "A") <> "" Then　を
If .Cells(i, "A") <> 0 Then　に変えてください。

お礼

少し仕事でバタバタしまして、順に皆様にお礼など
書いていたのですが思わぬ回答いただきありがとうございます。
ほんとうにありがとうございます。

補足

ありがとうございます。
今テストさせていただいてますが、
Range　pattcspccial　メゾットが失敗とでたのですが
Sheets("一回目").Cells(n, "A").PasteSpecial (xlPasteValues)
この部分のどこが、問題なのでしょうか、
私の力では、わからないので
おねがいします。

回答No.4

merlionXX

ご質問の内容がよくわかりませんでしたので以下の前提で書きました。
"Ａ会社", "田中会社", "小山田会社"のそれぞれのシートのA7からA210まで見て、そこが空白（数式で=""も空白）でなかったら、その行のL列までコピー
（Sheets(Array("Ａ会社", "田中会社", "小山田会社"))とシート名を明示しましたが、仮に左から５シート目以降～最後までとかきまっているのならもっと簡単に書けます。）
"１回目"シートの7行目から下へ順に値を貼り付ける。
２回目以降のシートにはどうしたいのか不明なので無視。

Sub test01()
　　Dim n As Long, i As Long
　　Dim ws As Worksheet
　　n = 7
　　Application.ScreenUpdating = False
　　For Each ws In Sheets(Array("Ａ会社", "田中会社", "小山田会社"))
　　　　For i = 7 To 210
　　　　　　With ws
　　　　　　　　If .Cells(i, "A") <> "" Then
　　　　　　　　　　.Cells(i, "A").Resize(, 12).Copy
　　　　　　　　　　Sheets("１回目").Cells(n, "A").PasteSpecial (xlPasteValues)
　　　　　　　　　　n = n + 1
　　　　　　　　　　Application.CutCopyMode = False
　　　　　　　　End If
　　　　　　End With
　　　　Next i
　　Next ws
　　Application.ScreenUpdating = True
End Sub

お礼

ありがとうございます

回答No.3

imogasi

こんなニーズはそうあるものでなく、読者が推測を働かせて、内容を掴むことも出来ない。
質問者がシートデータを見て、鉛筆で新たな紙に向かって質問者のやりたいこと（結果）を書きだす（実現する）ステップを１歩づつ、箇条書きで説明するること。
エクセルVBAも案外それをプログラム化しただけのことが多い。
ーー
＞各会社のデーターは、別のブックから参照させてるので、セルに式が書かれているのですが
こんなことを悩むようでは、エクセルのことが判ってない証拠。ほとんどの場合は、セルの値を問題にするので、そのセルに人間がデータを打ち込んでも、数式でそういう結果になっても、他のセルで＝B１０なんて参照する場合には両者の差は無い。
＞、１回目、２回目、３回目、４回目
の内容はなに？
本質問に関しては存在は関係ないのでは？
＞文字が書かれている、文字だけコピーして
数字ばかりのセルや、空白セルはどうする？１つのセルで文字＋数字のセルは無いのか？
＞各会社のシートのＡ7：Ｌ210の
＞コピーして１回目にＡ7：Ｌ210張り付けたいのですが
会社の数は複数あるのに、貼り付ける先のセル範囲は１つしかないのはどういうこと。
ーー
もっとしっかり文章表現が出来るようになって質問のこと。他の回答者も読んでも意味がわからないようだ。
忙しい中を回答してもらうのだから、しっかりして。
ーー
提案として
２会社に絞って、シートの何処（セル範囲）を見て、どのシートの何処（どのセル範囲）に値を代入するのか。
これに絞って表現のこと。変に一般化するようなことを初めから目指さ無いほうが良い。
写すのは「セルの値」だけで良いのでしょう。

お礼

御礼が遅くなりました。
初心者で、質問の仕方もわかりずらく迷惑をかけます。
ご指摘のとうりです。

補足

うまく説明できるか心配ですが、
やりたいことは、１回目のシートのA7~から下方向にA社のデーターをコピーしたものを値と数ちの書式で貼り付けA社のデーターの終わりから下方向へ、次の会社のデーターを順に貼り付けて行く作業を手作業してたので、マクロでできないかとかんがえていたのです。貼り付けは、文字や罫線などもはりつけたいのです。

回答No.2

keithin

「なにがどうしてどなった」といった，筋道だった説明がナッシングです。
もう一度ご自分の書いた説明を，よく読み返してみる必要がありますね。

●特に説明が足りない部分
１）Ａ７から，いったいどういう風に詰めて貼り付けたいのか。縦に詰める？横に詰める？それとも？
２）まず会社１枚目の「Ａ社」を処理した後，２枚目「田中会社」以降をどういう具合に続けたいのか。
２’何社も続けて処理する途中に「１回目のA7:L210」を埋め尽くしたらどうしたらいいのか。
３）「１回目」「２回目」「３回目」とは一体全体どういうことか。

以下のマクロを手がかりにまず勉強して，出来ない所は改めて「ヤリタイ事を今度はちゃんと補足して」ご相談を出し直してみて下さい。

sub macro1()
　dim i as long
　dim n as long
　dim h as range

’会社シートの巡回
　for i = 5 to worksheets.count
　’各会社シート内の巡回
　　for each h in worksheets(i).range("A7:L210")
　　'数式が""を計算していなければ転記する
　　if h <> "" then
　　　worksheets("１回目").cells(7 + n \ 12, 1 + n mod 12).value = h.value
　　　n = n + 1
　　end if
　　next
　next i
end sub

＃補足
＞「セルに式が書かれている」
これも全く説明不足の一つです。
転記したいのは「文字が計算された」のセルだという事ですが，転記したくないセルは具体的に一体どんな結果が現れる式になっているのか，まったく不明です。
上述マクロは丁寧に「表示すべき文字がなければ""にする」という数式が書かれている，という数式を前提にしています。
でももしかすると，「文字のないセルはゼロが計算されているが，表示によってゼロが見えないように細工している」だけかもしれません。
また「実は文字と数値と空白をそれぞれ計算していて，その中から特に文字の結果だけが欲しかったんだ」なんていう具合に，具体的なデータの内容によっても判断しなければイケナイ状況なのかも？しれません。
そういう説明の手抜きによって，折角寄せられた回答も「できないできないー」と投げ捨ててしまうご相談はとても多くて，回答者の皆さんを困らせています。

お礼

スミマセンお礼がおそくなりまして、
急に仕事が、バタバタしまして・・・こちらの都合で申し訳ありません。

ご丁寧な長文回答感謝してます。

補足

指摘の部分です。
A7から順に下につめたいのです。
一回目にシートA7からA社をコピー貼り付けして、下方向に田中会社終わりましたら次の会社・・・このような感じです。
A7からL210以上の数になったことがないので、貼り付け範囲は、これで問題ないと考えてます。
一回目、２回目、３回目、４回目、とも同じ処理なので、意味はなかったですね、私の勘違いで、マクロの式を書くときに、シートの指定などが、必要かなとおもいまして、混乱させて住むません、

セルの式は、参照のための関数が書かれていて、条件付き書式で、
0表示させないとしてます。

質問の仕方の不備で見なさにご迷惑かけます。

回答No.1

mt2008

> やりたい事は、シート１回目のセルＡ7：Ｌ210の中に各会社のシートのＡ7：Ｌ210の文字が書かれてる部分だけコピーして１回目にＡ7：Ｌ210張り付けたいのですが、

やりたい事が良く判りません。「各会社のシートのＡ7：Ｌ210の文字」って？
具体例をあげてください。

お礼

ありがとうございます。
ご指摘のように質問内容がへんですよね。
スミマセンでした。

補足

各会社のシートのＡ7：Ｌ210の文字と言うのは、
A7:L210のセルに他のブックから参照させてるのですが、
参照元のデーターない部分は、空白で表示されるので、
（単に条件付き書式で、0表示しないにしてるだけです）
内容は、数字・漢字など混じってます
一回目のシートのA7からコピーしたものを張り付けたいのです。
捕捉になってますか・・・すみません