|
今晩は。やって見ました。
値は完全に一致させないと合わないのでご注意下さい。たとえば、
”4月”と”4月”は、合いません。詳細シートの項目を売上シートに
コピーすれば確実です。売上シートの製造番号は数値として扱っています。
これがもし文字列だったらうまく行きません。テストして見て下さい。
Option Explicit
Option Base 1
Const sb1 As String = "詳細11" 'ブックの名称をここに書く。
Const sb2 As String = "詳細22"
Const sb3 As String = "詳細33"
Const sb4 As String = "詳細44"
Const sb5 As String = "詳細55"
Dim ss(5) As String
Dim sinki As Object
Dim vv As Variant
Dim b As Boolean
Sub 詳細記入()
b = False
Call 配列記入(sb1)
Call 配列記入(sb2)
'Call 配列記入(sb3)
'Call 配列記入(sb4)
'Call 配列記入(sb5)
End Sub
Private Sub 配列記入(s1 As String)
Dim i As Integer, j As Integer
Workbooks.Open ThisWorkbook.Path & "\" & s1 & ".xls"
vv = Workbooks(s1 & ".xls").Worksheets(1).UsedRange.Value
vv(1, 1) = Val(Mid(vv(1, 1), 5, Len(vv(1, 1))))
For j = 2 To UBound(vv, 2)
For i = 3 To UBound(vv, 1)
vv(2, j) = vv(2, j) + vv(i, j)
Next
Next
用紙記入
End Sub
Private Sub 用紙記入()
Dim r As Range, rr1 As Range, rr2 As Range
Dim i As Integer, j As Integer, ir As Integer, ix As Integer
If b = False Then
ThisWorkbook.Worksheets("売上").Copy
Set sinki = ActiveWorkbook
b = True
End If
With sinki.Worksheets("売上")
For i = 2 To 6
If .Cells(i, 1).Value = vv(1, 1) Then
ir = i
Exit For
End If
Next
For i = 2 To UBound(vv, 2)
For j = 2 To 13
If .Cells(1, j).Value = vv(1, i) Then
.Cells(ir, j).Value = vv(2, i)
End If
Next
Next
End With
End Sub
|
|