| |
こんばんは。
シートマスターから各社ごとにオートフィルターを実施し、
新しいシートに貼り付けています。
合計も一応出していますが、
こういう意味でなかったらすみません。
なお、金額は表示形式のみで入力されているのは数値として考えてます。
Sub test()
Dim myR As Range
Dim mySH1 As Worksheet
Dim mySH As Worksheet
Dim sh As Worksheet
Dim myVal As Variant
Dim myCol As Long
Dim myRow As Long
Dim i As Integer, j As Integer
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If Not sh.Name = "マスター" Then
Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
End If
Next
Set mySH1 = Worksheets("マスター")
Set myR = mySH1.Range("A1").CurrentRegion
myVal = Array("会社A", "会社B", "会社C", "会社D", "会社E")
For i = 0 To UBound(myVal)
Set mySH = Worksheets.Add(after:=Sheets(Sheets.Count))
mySH.Name = myVal(i) & "のデータ"
With myR
.AutoFilter field:=2, Criteria1:=myVal(i)
.Copy mySH.Range("A1")
.AutoFilter
End With
With mySH.Range("A65536").End(xlUp)
myCol = Range("A1").End(xlToRight).Column
myRow = .Row
For j = 2 To myCol - 1
.Offset(2, 1).Value = "合計"
With .Offset(2, j)
.Value = WorksheetFunction.Sum(Range(Cells(2, j + 1), Cells(myRow, j + 1)))
.NumberFormatLocal = "\#,##0;\-#,##0"
End With
Next j
mySH.Range("A1").End(xlToRight).Offset(0, 2).Value = "合計"
For j = 1 To myRow - 1
With Range("A1").Offset(j, myCol + 1)
.Value = WorksheetFunction.Sum(.Offset(0, -myCol).Resize(1, myCol - 2))
.NumberFormatLocal = "\#,##0;\-#,##0"
End With
Next j
End With
Next i
Application.ScreenUpdating = True
End Sub
|
|
![[未読]](./data/excel/image/read_icon.gif){kind=icon}
