抽出マクロが上手くいきません教えてください！

質問

数値と文字が混在しているＢ列に、
入力されている数値データの最後のみをを違う合計シートに抽出したいのですが、
上手くいきません。。

下記のマクロなのですが。。
分かる方教えてください

お願いします

ThisWOrkbook1

Private SubWorkbook_Open()
Dim NO As Double
Dim シート名 As String
For l = 70 To 130
シート名 = Worksheet("goukei").Range("B" & l).Value
Debug.Print シート名
Worksheets(シート名).Select
Range("B30").End(xlUp).Offset(0).Select
NO = Range("B30").End(xlUp).Offset(0).Value
Debug.Print NO
Worksheets("goukei").Select
Range("C" & l) = NO
Next
End Sub

回答No.2

Wendy02

>Private Sub Workbook_Open()
起動時に、Workbook オープンイベントで、オープン中に、マクロを実行させるのは、万が一に再計算中などにどうなるかは分かりませんか、あまりお勧めできないと思います。ブックがメモリを確保してから、実行したほうがよいかと思います。

私なら、こういうように書きます。（ひと通りのエラー処理はしています）

'標準モジュール
Sub TestMacro1()
　Dim iNo　As Variant
　Dim i As Long
　Dim sh As Worksheet
　Dim iSh As Worksheet
　Dim shN As String
　Set sh = Worksheets("goukei")
　sh.Range("B70").Select
　Application.ScreenUpdating = False
　For i = 70 To 130
　　shN = sh.Cells(i, 2).Value
　　If shN <> "" Then
　　　On Error Resume Next 'シートがない場合
　　　 With Worksheets(shN)
　　　　　iNo = Empty
　　　　　iNo = .Range("B30").End(xlUp).Value
　　　　End With
　　　On Error GoTo 0
　　　If IsNumeric(iNo) Then　'値を取得した時のみ
　　　　sh.Cells(i, 3).Value = iNo
　　　Else
　　　　sh.Cells(i, 3).ClearContents
　　　End If
　　End If
　Next i
　Application.ScreenUpdating = True
End Sub

でも、関数で出来るのではないでしょうか？

SUMで合計を出しているなら、このようにして、オートフィルで、数式をコピーすればよいと思います。そうすれば、再計算で待ってくれるはずです。

>合計シート
SUMを使っている場合

C70～
=SUM(INDIRECT(B70&"!B1:B30"))/2

　*エラー処理はしていません。

回答No.1

keithin

＞数値データの最後のみ…抽出したい

Private Sub Workbook_Open()
　dim l as long
　worksheets("goukei").range("C70:C130").clearcontents
　For l = 70 To 130
　　with worksheets(worksheets("goukei").cells(l, "B"))
　　if application.count(.range("B1:B30")) > 0 then
　　　worksheets("goukei").cells(l, "C") = application.lookup(1e10, .range("B1:B30"))
　　end if
　　end with
　next l
End Sub

＃各シートのB30より上に「一番下がある」という今のマクロが実際には正しくない状況の場合は，マクロの中のrange("B1:B30")を漏れなくrange("B:B")など適切に修正する

＃実際にはわざわざマクロを使わなくても，
goukeiシートのC70セルに
=LOOKUP(1E10,INDIRECT(B70&"!B1:B30"))
以下C130までコピー
などのような数式を記入しておけば，出来ます。
同様に範囲（「"!B1:B30"」の部分が不適正だった場合は修正すること。