勤怠・明細書のデーターが入ったが、１名に付１シートあります。各シート同

質問

勤怠・明細書のデーターが入ったが、１名に付１シートあります。各シート同じ書式です。
全部で、今の所ｓｈｅｅｔ１～ｓｈｅｅｔ５４まで計５４枚あるのですが、ある条件を満たしたSheetの
データを他のシートに　一覧表示させたいのですが、可能でしょうか、

表示させたいシート名は、（仮に週払一覧）　　

必要なデータのセルは、　AB３：AK8　で、　文字や数字関数もあります。

一覧に表示させたい条件は、AJ７に金額が出ますので、ＡＪ７が　￥０より多き場合
５４枚から条件に合うシートのデータだけ週払い一覧に表示させたいのです。
シート名は１～５４までの順番どうりです。

関数では無理でしょうか、　簡単なマクロしかできませんが・・・

良いアイデアおねがいします。

回答No.7

nattocurry · ベストアンサー

＞数式や参照でーたーのせるが、　＃Ｎ／Ａや＃ＲＥＦなどのエラー表示がでるのですが、
＞これは、私が作数式や関数が悪いのでしょうか、

たったこれだけの情報で、悪いのかどうか解るわけがありません。
悪いのかどうか聞きたかったら、あなたが入力した数式を提示すべきです。

＞値だけを文字列で表示させる事は、できますか

文字列で、というわけではありませんが、

Sh.Range("AB3:AK8").Copy .Cells(.Rows.Count, 1).End(xlUp).Offset(1)

の代わりに、

With .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
Sh.Range("AB3:AK8").Copy .Cells(1)
.Resize(6, 10).Value = Sh.Range("AB3:AK8").Value
End With

と書いてください。

お礼

回答ありがとうございます。
言われるとうりです。　
質問の仕方がわるいですね。
スミマセン　教えていただいた　とうりに書きましたら
上手く動きました。
ありがとうございました。

回答No.6

imogasi

>関数では無理でしょうか、
無理です。
＞簡単なマクロしかできませんが・
この課題が簡単かどうかは、判っている人にしかいえません。
正面からVBAで出来る人は中級の上ぐらいではないかな。
ーーー
最小限にマクロ利用であえてやって見ます。ユーザー関数の考えを使うので、（未経験なら）難しいといえば難しいかも。たった３行なのでと思って。
本格的なマクロで説明するより易しいかなと思うが、受け取り方は人により様々か。
ーー
シートは４つ。質問者がこのように質問を簡略化できれば、力のある証拠。（５４でも３シートでも類推できることが条件だが。このことを判っている質問者は少ない）
Sheet1 A1 大村　B2　12
Sheet2　A1　木村　B2 9
Sheet3　A1　田中　B2 13
氏名、計数のつもり。最小限簡略化。
ーー
シートタブの順番でシート名を取るユーザー関数
ッ標準のジュールに
Function sheetn(n)
sheetn = Sheets(n).Name
End Function
ーーー
Sheet4の第１行目には数字を入れないこと。漢字見出しはOK。
Sheet4のA2に
=INDIRECT(sheetn(ROW()-1)&"!$a$1")
と入れて下方向に式を複写
B2に
=INDIRECT(sheetn(ROW()-1)&"!$B$2")
と入れて下方向に式を複写。
ーーー
結果　Sheet4
大村 12
木村 9
田中 13
これを応用すると、１人１行の表が１シートに出来るのはわかるね。
ーーー
以下は条件に合った行に連番を振って、抜き出すための細工をする。
（Googleで「imogasi方式」で照会すれば、似た抜き出し問題に使っている例が多数が出る）
C2に=IF(B2>10,MAX($C$1:C1)+1,"")　と入れて下方向に式を複写。
結果
Sheet4
大村 12 1
木村 9
田中 13 2
ーー
これをSheet5に、条件合致分を抜き出す。
A2に=INDEX(Sheet4!$A$2:$C$4,MATCH(ROW()-1,Sheet4!$C$2:$C$4,0),COLUMN())
B2に式複写
A2:B2の式をA4:B4まで式複写。
ーーー
結果　Sheet5
大村 12
田中 13
#N/A #N/A

該当数と複写行数によっては#N/Aがでる。これを出さないようにする方法は、上記imogasi方式の回答を見ること。ここでは略。
ーー
また質問者の場合シート名を大村、木村、田中などと変更・設定しているかもしれないが、上記式はそのまま通用する。
Sheet4の順序は、シートタブの並び順になる。
シート順序を変えても被害は無い。

お礼

返事が遅くなりすみませんでした。
皆さまのおかげで　良い物が出来ました。
ありがとうございました。

回答No.5

imogasi

>関数では無理でしょうか、
無理です。
＞簡単なマクロしかできませんが・
この課題が簡単かどうかは、判っている人にしかいえません。
正面からVBAで出来る人は中級の上ぐらいではないかな。
ーーー
最小限にマクロ利用であえてやって見ます。ユーザー関数の考えを使うので、（未経験なら）難しいといえば難しいかも。たった３行なのでと思って。
本格的なマクロで説明するより易しいかなと思うが、受け取り方は人により様々か。
ーー
シートは４つ。質問者がこのように質問を簡略化できれば、力のある証拠。（５４でも３シートでも類推できることが条件だが。このことを判っている質問者は少ない）
Sheet1 A1 大村　B2　12
Sheet2　A1　木村　B2 9
Sheet3　A1　田中　B2 13
氏名、計数のつもり。最小限簡略化。
ーー
シートタブの順番でシート名を取るユーザー関数
ッ標準のジュールに
Function sheetn(n)
sheetn = Sheets(n).Name
End Function
ーーー
Sheet4の第１行目には数字を入れないこと。漢字見出しはOK。
Sheet4のA2に
=INDIRECT(sheetn(ROW()-1)&"!$a$1")
と入れて下方向に式を複写
B2に
=INDIRECT(sheetn(ROW()-1)&"!$B$2")
と入れて下方向に式を複写。
ーーー
結果　Sheet4
大村 12
木村 9
田中 13
これを応用すると、１人１行の表が１シートに出来るのはわかるね。
ーーー
以下は条件に合った行に連番を振って、抜き出すための細工をする。
（Googleで「imogasi方式」で照会すれば、似た抜き出し問題に使っている例が多数が出る）
C2に=IF(B2>10,MAX($C$1:C1)+1,"")　と入れて下方向に式を複写。
結果
Sheet4
大村 12 1
木村 9
田中 13 2
ーー
これをSheet5に、条件合致分を抜き出す。
A2に=INDEX(Sheet4!$A$2:$C$4,MATCH(ROW()-1,Sheet4!$C$2:$C$4,0),COLUMN())
B2に式複写
A2:B2の式をA4:B4まで式複写。
ーーー
結果　Sheet5
大村 12
田中 13
#N/A #N/A

該当数と複写行数によっては#N/Aがでる。これを出さないようにする方法は、上記imogasi方式の回答を見ること。ここでは略。
ーー
また質問者の場合シート名を大村、木村、田中などと変更・設定しているかもしれないが、上記式はそのまま通用する。
Sheet4の順序は、シートタブの並び順になる。
シート順序を変えても被害は無い。

回答No.4

keithin

例えば。
sub macro1()
　dim i
　dim j
　for i = 1 to 54
　　if worksheets("Sheet" & i).range("AJ7") > 0 then
　　　j = j + 1
　　　worksheets("仮に週払一覧").range("A1:J1").offset(j, 0).value = _
　　　　worksheets("Sheet" & i).range("AB3:AK3").value
　　end if
　next i
end sub

＃ところで
>全部で、今の所ｓｈｅｅｔ１～ｓｈｅｅｔ５４まで計５４枚あるのですが

あなたはご相談で「全角の英数字」でｓｈｅｅｔ１などと書いていますが，それが事実なのでしょうか。
教わったマクロを「コピーして走らせるしかできない」と，そういった細かい間違いでお互いの手間暇が無駄になるばかりなので，情報提供は正確に。

＞関数では無理でしょうか

たとえばまとめのB列に
Sheet1
と書いて下向けに54までオートフィルドラッグしておき
C列に
=INDIRECT(B2&"!AJ7")
と書いて下向けにコピー
D列に
=INDIRECT(B2&"!R3C[24]",FALSE)
と書いて右に計１０個，下に54個コピーしておき，
C列にオートフィルタを付けてゼロより大きいで絞ってやれば，欲しい一覧の体裁です。

＃あとは趣味で。
A列に更に関数を付けてC列がゼロより大きい行だけに連番を付けておき，その番号を手がかりにO列より右とかに連番の行だけVLOOKUPとかで取り出し直せば，上下の詰まった表を計算だけで並べさせる事も出来ます。
A2:
=IF(C2>0,COUNTIF($C$2:C2,">0"),"")
とかなんとか。

お礼

返事が遅くなりました。
いろいろ　ありがとうございます。
皆さまのおかげで、　納得のゆくものができました。
少しずつですが、　勉強してゆきます。
ありがとうございました。

回答No.3

layy

前のは解釈誤りでした。すみません。

値として貼り付けたい？。適当なセルでマクロ作って、形式を選択して貼り付け、をやってみましたか。それを真似して反映できませんか。

補足

回答ありがとうございます。
私の返答の言葉が適切でないようですね！
＃２さんの　マクロを実行したらチャンと条件に合った
個数だけ　週払い一覧に表示出来たのですが、

データの中に、計算式や他のセルから参照してる部分だけが　エラー表示されるのですが、
これは、関数の書き方に問題があるのか！
それとも参照やVLOOKUP等の関数を使っているとだめなのかが、分からないのです。
何か　方法は、有りますか。

お願いします。

回答No.2

nattocurry

Sub test()
Dim Sh As Worksheet
For Each Sh In Worksheets
If Sh.Name <> "週払一覧" Then
With Worksheets("週払一覧")
If Sh.Range("AJ7").Value > 0 Then
Sh.Range("AB3:AK8").Copy .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
End If
End With
End If
Next Sh
Set Sh = Nothing
End Sub

各シートのAB8に必ず何かが記入されていることが前提です。

お礼

失礼いました　こちらの手違いで　エラーがでたようです。
スミマセンでした。無事にデーターは、出てきたのですが、
数式や参照でーたーのせるが、　＃Ｎ／Ａや＃ＲＥＦなどのエラー表示がでるのですが、
これは、私が作数式や関数が悪いのでしょうか、
値だけを文字列で表示させる事は、できますか

すみませんが、おねがいします。

補足

回答　ありがとうございます。
インデックスが有効範囲にありません　と　エラーメッセージが出るのですが、
私の張り付け型に間違いがあるのでしょうか、

御手数かけます。

回答No.1

layy

マクロでやろうとしたら

あるシートを選択
１シート目のＡＪ７コピー
あるシートのＡ１に貼り付け
２シート目のＡＪ７コピー
あるシートのＡ２１に貼り付け
（５４回分繰り返し）
これくらいまででしょう。
これに追記して
貼り付け時は０より大きい時のみの判定、ですが、対応はＶＢＡになると思います。