ＶＢＡ--- エクセル：シートを作成し、シート名を設定し、表を作成
令和元年分の消費税の手引が、国税庁ホームページに掲載されていたので、勉強がてらエクセルで計算してみようと思い、まず表を作成するＶＢＡを作成（説明は省略）
Sub hyo1_sakussei()
vpt = 0.52
vvt = 0.5
ThisWorkbook.Worksheets.Add
ActiveSheet.Name = "表イ_1"
With Worksheets("表イ_1")
.Columns("A").ColumnWidth = 5 * vpt
.Columns("B").ColumnWidth = 24 * vpt
.Columns("C").ColumnWidth = 5 * vpt
.Columns("D:I").ColumnWidth = 21 * vpt
.Rows(1).RowHeight = 80 * vvt
.Rows(2).RowHeight = 40 * vvt
.Rows(3).RowHeight = 20 * vvt
.Rows(4).RowHeight = 30 * vvt
.Rows(5).RowHeight = 70 * vvt
.Rows(6).RowHeight = 30 * vvt
.Rows(7).RowHeight = 120 * vvt
.Rows("8:40").RowHeight = 50 * vvt
.Range("A3:I40").Borders.LineStyle = xlContinuous
.Range("A3:I40").Borders.Weight = xlHairline
.Range("A3:I6").BorderAround Weight:=xlThin
.Range("A7:I7").BorderAround Weight:=xlThin
.Range("A8:I12").BorderAround Weight:=xlThin
.Range("A13:I13").BorderAround Weight:=xlThin
.Range("A14:I40").BorderAround Weight:=xlThin
.Range("A39:I39").BorderAround Weight:=xlThin
.Range("A40:I40").BorderAround Weight:=xlThin
.Range("E3:E40").Borders(xlEdgeRight).Weight = xlThin
.Range("F7:I7").Borders.Weight = xlMedium
.Range("F40:I40").Borders.Weight = xlMedium
.Range("D3:D40").Borders(xlEdgeRight).Weight = xlMedium
.Range("F9:I9").BorderAround LineStyle:=xlDouble
.Range("F38:I38").BorderAround LineStyle:=xlDouble
.Range("D6:I6").Borders(xlEdgeTop).LineStyle = xlNone
.Range("E8:I8").Borders(xlDiagonalUp).LineStyle = xlContinuous
.Range("E10:I13").Borders(xlDiagonalUp).LineStyle = xlContinuous
.Range("E16").Borders(xlDiagonalUp).LineStyle = xlContinuous
.Range("F21:I21").Borders(xlDiagonalUp).LineStyle = xlContinuous
.Range("E22:E23").Borders(xlDiagonalUp).LineStyle = xlContinuous
.Range("E24:I24").Borders(xlDiagonalUp).LineStyle = xlContinuous
.Range("E28:I28").Borders(xlDiagonalUp).LineStyle = xlContinuous
.Range("E30:I30").Borders(xlDiagonalUp).LineStyle = xlContinuous
.Range("G3").Borders(xlEdgeLeft).LineStyle = xlNone
.Range("A1:I1").Merge
.Range("F2:I2").Merge
.Range("A3:C6").Merge
.Range("D3:D5").Merge
.Range("E3:E5").Merge
.Range("F3:F5").Merge
.Range("G3:I3").Merge
.Range("H4:I4").Merge
.Range("A7:B7").Merge
.Range("A8:A12").Merge
.Range("A13:B13").Merge
.Range("A14:A38").Merge
.Range("A39:B39").Merge
.Range("A40:B40").Merge
.Range("A1") = "課税取引金額計算表"
.Range("A2") = "（令和元年分）"
.Range("H2") = "（事業所得用）"
.Range("A3") = "科 目"
.Range("D3") = "決 算 額"
.Range("E3") = "Ａのうち課税" & vbCrLf & "取引にからな" & vbCrLf & "いもの（※）"
.Range("F3") = "課税取引金額" & vbCrLf & "（Ａ－Ｂ）"
.Range("H4") = "R1.10.1以上（※2）"
.Range("G4") = "R1.9.30以前（※2）"
.Range("G5") = "のうち旧税率" & vbCrLf & "6.3％適用分"
.Range("H5") = "のうち軽減税率" & vbCrLf & "6.24適用分"
.Range("I5") = "のうち軽減税率" & vbCrLf & "7.8適用分"
.Range("D6") = "Ａ"
.Range("E6") = "Ｂ"
.Range("F6") = "Ｃ"
.Range("G6") = "Ｄ"
.Range("H6") = "Ｅ"
.Range("I6") = "Ｆ"
.Range("A7") = "売上（収入）金額" & vbCrLf & "（雑収入を含む）"
.Range("C7") = "1"
.Range("A8") = "売上原価"
.Range("B8") = "期首商品棚卸高"
.Range("C8") = "2"
.Range("B9") = "仕入金額"
.Range("C9") = "3"
.Range("C10") = "4"
.Range("B10") = "小 計"
.Range("B11") = "期末商品棚卸高"
.Range("C11") = "5"
.Range("B12") = "差引原価"
.Range("C12") = "6"
.Range("A13") = "差引金額"
.Range("C13") = "7"
.Range("A14") = "経費"
.Range("B14") = "租税公課"
.Range("C14") = "8"
.Range("B15") = "荷造運賃"
.Range("C15") = "9"
.Range("B16") = "水道光熱費"
.Range("C16") = "10"
.Range("B17") = "旅費交通費"
.Range("C17") = "11"
.Range("B18") = "通信費"
.Range("C18") = "12"
.Range("B19") = "広告宣伝費"
.Range("C19") = "13"
.Range("B20") = "接待交際費"
.Range("C20") = "14"
.Range("B21") = "損害保険料"
.Range("C21") = "15"
.Range("B22") = "修繕費"
.Range("C22") = "16"
.Range("B23") = "消耗品費"
.Range("C23") = "17"
.Range("B24") = "減価償却費"
.Range("C24") = "18"
.Range("B25") = "福利厚生費"
.Range("C25") = "19"
.Range("B26") = "給料賃金"
.Range("C26") = "20"
.Range("B27") = "外注工賃"
.Range("C27") = "21"
.Range("B28") = "利子割引料"
.Range("C28") = "22"
.Range("B29") = "地代家賃"
.Range("C29") = "23"
.Range("B30") = "貸倒金"
.Range("C30") = "24"
.Range("B31") = ""
.Range("C31") = "25"
.Range("B32") = ""
.Range("C32") = "26"
.Range("B33") = ""
.Range("C33") = "27"
.Range("B34") = ""
.Range("C34") = "28"
.Range("B35") = ""
.Range("C35") = "29"
.Range("B36") = ""
.Range("C36") = "30"
.Range("B37") = "雑費"
.Range("C37") = "31"
.Range("B38") = "計"
.Range("C38") = "32"
.Range("A39") = "差引金額"
.Range("C39") = "33"
.Range("C40") = "34"
.Range("A40") = " 3 ＋ 32"
.Range("A1").Font.Size = 12
.Range("A2:I40").Font.Size = 9
.Range("D6:I6").Font.Size = 8
.Range("G4").Font.Size = 7
.Range("G5").Font.Size = 8
.Range("H4").Font.Size = 7
.Range("H5").Font.Size = 8
.Range("I5").Font.Size = 8
.Range("A1:I1").HorizontalAlignment = xlCenter
.Range("A3:C3").HorizontalAlignment = xlCenter
.Range("D3:D5").HorizontalAlignment = xlCenter
.Range("E3:E5").HorizontalAlignment = xlCenter
.Range("F3:F5").HorizontalAlignment = xlCenter
.Range("G4").HorizontalAlignment = xlCenter
.Range("H4:I4").HorizontalAlignment = xlCenter
.Range("G5").HorizontalAlignment = xlLeft
.Range("H5:I5").HorizontalAlignment = xlCenter
.Range("D6:I6").HorizontalAlignment = xlCenter
.Range("A7:B7").HorizontalAlignment = xlCenter
.Range("C7:C40").HorizontalAlignment = xlCenter
.Range("A8:A12").HorizontalAlignment = xlCenter
.Range("B10").HorizontalAlignment = xlCenter
.Range("A13:B13").HorizontalAlignment = xlLeft
.Range("A14:A38").HorizontalAlignment = xlCenter
.Range("A3:C3").VerticalAlignment = xlCenter
.Range("D3:D5").VerticalAlignment = xlCenter
.Range("E3:E5").VerticalAlignment = xlCenter
.Range("F3:F5").VerticalAlignment = xlCenter
.Range("G4").VerticalAlignment = xlCenter
.Range("H4:I4").VerticalAlignment = xlCenter
.Range("G5").VerticalAlignment = xlCenter
.Range("H5:I5").VerticalAlignment = xlCenter
.Range("D6:I6").VerticalAlignment = xlCenter
.Range("A7:B7").VerticalAlignment = xlCenter
.Range("C7:C40").VerticalAlignment = xlCenter
.Range("D7:I40").VerticalAlignment = xlCenter
.Range("A8:A12").VerticalAlignment = xlCenter
.Range("B8:B12").VerticalAlignment = xlCenter
.Range("A13").VerticalAlignment = xlCenter
.Range("B14:B38").VerticalAlignment = xlCenter
.Range("A14:A38").VerticalAlignment = xlCenter
.Range("A39:B39").VerticalAlignment = xlCenter
.Range("A40:B40").VerticalAlignment = xlCenter
.Range("A8:A12").Orientation = xlVertical
.Range("A8:A12").Orientation = -90
.Range("A14:A38").Orientation = xlVertical
.Range("A14:A38").Orientation = -90
.Shapes.AddShape(msoShapeOval, 100, 156, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 199, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 223, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 248, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 272, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 298, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 322, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 347, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 371, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 396, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 421, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 446, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 471, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 495, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 520, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 545, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 569, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 594, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 619, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 644, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 668, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 693, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 718, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 743, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 767, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 792, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 817, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 842, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 867, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 892, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 917, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 941, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 965, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 100, 989, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 2, 990, 16, 16).Fill.Visible = msoFalse
.Shapes.AddShape(msoShapeOval, 35, 990, 16, 16).Fill.Visible = msoFalse
.Range("A7:I40").NumberFormat = "#,###"
End With
End Sub
