【ExcelVBA】ワークシートのコピー

2019.06.12ExcelVBA,シートの操作

基本編

先頭にコピーした後、シートの名前を変更

Sub Sample()

	ThisWorkbook.Worksheets("Sheet1").Copy Before:=ThisWorkbook.Worksheets(1)

	ActiveSheet.Name = "在庫"

End Sub

※名前の変更が不要であれば、5行目は削除

末尾にコピーした後、シートの名前を変更

Sub Sample()

	ThisWorkbook.Worksheets("Sheet1").Copy After:=ThisWorkbook.Worksheets(Worksheets.Count)

	ActiveSheet.Name = "在庫"

End Sub

※名前の変更が不要であれば、5行目は削除

別ブックの先頭にコピーした後、シートの名前を変更

Sub Sample()

  Dim TargetWB As String
  
  TargetWB = "Book2.xlsx" '別ブック名
  
  ThisWorkbook.Worksheets("Sheet1").Copy Before:=Workbooks(TargetWB).Worksheets(1)
  
  ActiveSheet.Name = "在庫"

End Sub

※名前の変更が不要であれば、9行目は削除

別ブックの末尾にコピーした後、シートの名前を変更

Sub Sample()

  Dim TargetWB As String
  
  TargetWB = "Book2.xlsx" '別ブック名
  
  ThisWorkbook.Worksheets("Sheet1").Copy After:=Workbooks(TargetWB).Worksheets(Workbooks(TargetWB).Worksheets.Count)
  
  ActiveSheet.Name = "在庫"

End Sub

※名前の変更が不要であれば、9行目は削除

応用編

「数式」「名前の定義」「データの入力規則」を複写させたくない

ワークシートを丸ごとコピーした場合は「数式」「書式」「名前の定義」「データの入力規則」等々もコピー先へ複写されます。
また「数式」「名前の定義」「データの入力規則」は、複製元ブックの名前が参照先リンクに含まれてしまい、何かと都合が悪くなることがあります。

【１】シートを丸ごとコピーして、数式のみ除去する方法（数式以外の除去は割愛）

Sub Sample()

  Dim TargetWB As String
  Dim TargetWS As String
  
  TargetWB = "Book1.xlsx" '別ブック名
  TargetWS = "在庫"

  ThisWorkbook.Worksheets("Sheet1").Copy Before:=Workbooks(TargetWB).Worksheets(1)
  
  ActiveSheet.Name = "在庫"

  '自身を値貼り付けして数式を除去
  with Workbooks(TargetWB).Worksheets(TargetWS)
		.UsedRange.Value = .UsedRange.Value
	End With
		
End Sub

【２】新しいシートを作成して、そこに値と書式のみ貼り付けをする方法

Sub Sample()

  Dim TargetWB As String
  Dim TargetWS As String
  
  TargetWB = "Book1.xlsx" '別ブック名
  TargetWS = "在庫" '新規シート名

	'別ブックのワークシート末尾に名前付き新規シート追加
  Workbooks(TargetWB).Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = TargetWS

  ThisWorkbook.Worksheets("Sheet1").Cells.Copy

  With Workbooks(TargetWB).Worksheets(TargetWS).Cells(1, 1)
    .PasteSpecial Paste:=xlPasteValues  '値貼り付け
    .PasteSpecial Paste:=xlPasteFormats '書式貼り付け
  End With

End Sub