在指定工作表中重复复制/粘贴步骤-我如何简化我的VBA代码?

Question

我是VBA初学者，我想请更有经验的人来简化我的VBA代码。

代码工作起来非常简单。宏遍历源.xlsx文件的下钻列表，每次通过循环(工作表"CZDataSource")设置下钻引用时刷新表作为PowerQuery结果，并在同一工作表的每三个工作表(工作表"预报-月"， "预报-月+1"， "预报-月+2")中重复执行相同的复制/粘贴步骤。

Sub SpitValues()
Dim dvCell As Range
Dim inputRange As Range
Dim c As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
'Drop-down list source
Set dvCell = Worksheets("CZDataSource").Range("C3")
Set inputRange = Evaluate(dvCell.Validation.Formula1)
For Each c In inputRange
dvCell = c.Value

If (Right(Range("C2"), 3) = "1st") And Range("C3") = "RIG Forecast_2021_act.xlsx" Then
Sheets("CZDataSource").ListObjects("RIG_Forecast_output").QueryTable.Refresh BackgroundQuery:=False
'CZ data input/output
Sheets("CZDataSource").Range("C7:C12").Copy
Sheets("Forecast - Month").Range("AZ34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month").Range("AZ34:AZ39").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("D7:D12").Copy
Sheets("Forecast - Month").Range("BA34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month").Range("BA34:BA39").NumberFormat = "#,##0,"
Sheets("Forecast - Month").Range("BB34:BB39").Value = "0"
Sheets("CZDataSource").Range("F7:F12").Copy
Sheets("Forecast - Month").Range("BI34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month").Range("BI34:BI39").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("G7:G12").Copy
Sheets("Forecast - Month").Range("BJ34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month").Range("BJ34:BJ39").NumberFormat = "#,##0,"
Sheets("Forecast - Month").Range("BK34:BK39").Value = "0"
Sheets("CZDataSource").Range("E7:E12").Copy
Sheets("Forecast - Month").Range("BO34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month").Range("BO34:BO39").NumberFormat = "#,##0,"
Sheets("Forecast - Month").Range("BP34:BP39").Value = "0"
Sheets("CZDataSource").Range("H7:H12").Copy
Sheets("Forecast - Month").Range("BS34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month").Range("BS34:BS39").NumberFormat = "#,##0,"
Sheets("Forecast - Month").Range("BT34:BT39").Value = "0"
'SK data input/output
Sheets("CZDataSource").Range("C13:C18").Copy
Sheets("Forecast - Month").Range("AZ55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month").Range("AZ55:AZ60").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("D13:D18").Copy
Sheets("Forecast - Month").Range("BA55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month").Range("BA55:BA60").NumberFormat = "#,##0,"
Sheets("Forecast - Month").Range("BB55:BB60").Value = "0"
Sheets("CZDataSource").Range("F13:F18").Copy
Sheets("Forecast - Month").Range("BI55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month").Range("BI55:BI60").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("G13:G18").Copy
Sheets("Forecast - Month").Range("BJ55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month").Range("BJ55:BJ60").NumberFormat = "#,##0,"
Sheets("Forecast - Month").Range("BK55:BK60").Value = "0"
Sheets("CZDataSource").Range("E13:E18").Copy
Sheets("Forecast - Month").Range("BO55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month").Range("BO55:BO60").NumberFormat = "#,##0,"
Sheets("Forecast - Month").Range("BP55:BP60").Value = "0"
Sheets("CZDataSource").Range("H13:H18").Copy
Sheets("Forecast - Month").Range("BS55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month").Range("BS55:BS60").NumberFormat = "#,##0,"
Sheets("Forecast - Month").Range("BT55:BT60").Value = "0"

ElseIf (Right(Range("C2"), 3) = "1st") And Range("C3") = "RIG Forecast_2021_m+1.xlsx" Then
Sheets("CZDataSource").ListObjects("RIG_Forecast_output").QueryTable.Refresh BackgroundQuery:=False
'CZ data input/output
Sheets("CZDataSource").Range("C7:C12").Copy
Sheets("Forecast - Month +1").Range("AZ34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +1").Range("AZ34:AZ39").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("D7:D12").Copy
Sheets("Forecast - Month +1").Range("BA34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +1").Range("BA34:BA39").NumberFormat = "#,##0,"
Sheets("Forecast - Month +1").Range("BB34:BB39").Value = "0"
Sheets("CZDataSource").Range("F7:F12").Copy
Sheets("Forecast - Month +1").Range("BI34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +1").Range("BI34:BI39").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("G7:G12").Copy
Sheets("Forecast - Month +1").Range("BJ34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +1").Range("BJ34:BJ39").NumberFormat = "#,##0,"
Sheets("Forecast - Month +1").Range("BK34:BK39").Value = "0"
Sheets("CZDataSource").Range("E7:E12").Copy
Sheets("Forecast - Month +1").Range("BO34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +1").Range("BO34:BO39").NumberFormat = "#,##0,"
Sheets("Forecast - Month +1").Range("BP34:BP39").Value = "0"
Sheets("CZDataSource").Range("H7:H12").Copy
Sheets("Forecast - Month +1").Range("BS34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +1").Range("BS34:BS39").NumberFormat = "#,##0,"
Sheets("Forecast - Month +1").Range("BT34:BT39").Value = "0"
'SK data input/output
Sheets("CZDataSource").Range("C13:C18").Copy
Sheets("Forecast - Month +1").Range("AZ55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +1").Range("AZ55:AZ60").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("D13:D18").Copy
Sheets("Forecast - Month +1").Range("BA55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +1").Range("BA55:BA60").NumberFormat = "#,##0,"
Sheets("Forecast - Month +1").Range("BB55:BB60").Value = "0"
Sheets("CZDataSource").Range("F13:F18").Copy
Sheets("Forecast - Month +1").Range("BI55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +1").Range("BI55:BI60").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("G13:G18").Copy
Sheets("Forecast - Month +1").Range("BJ55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +1").Range("BJ55:BJ60").NumberFormat = "#,##0,"
Sheets("Forecast - Month +1").Range("BK55:BK60").Value = "0"
Sheets("CZDataSource").Range("E13:E18").Copy
Sheets("Forecast - Month +1").Range("BO55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +1").Range("BO55:BO60").NumberFormat = "#,##0,"
Sheets("Forecast - Month +1").Range("BP55:BP60").Value = "0"
Sheets("CZDataSource").Range("H13:H18").Copy
Sheets("Forecast - Month +1").Range("BS55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +1").Range("BS55:BS60").NumberFormat = "#,##0,"
Sheets("Forecast - Month +1").Range("BT55:BT60").Value = "0"

ElseIf (Right(Range("C2"), 3) = "1st") And Range("C3") = "RIG Forecast_2021_m+2.xlsx" Then
Sheets("CZDataSource").ListObjects("RIG_Forecast_output").QueryTable.Refresh BackgroundQuery:=False
'CZ data input/output
Sheets("CZDataSource").Range("C7:C12").Copy
Sheets("Forecast - Month +2").Range("AZ34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +2").Range("AZ34:AZ39").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("D7:D12").Copy
Sheets("Forecast - Month +2").Range("BA34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +2").Range("BA34:BA39").NumberFormat = "#,##0,"
Sheets("Forecast - Month +2").Range("BB34:BB39").Value = "0"
Sheets("CZDataSource").Range("F7:F12").Copy
Sheets("Forecast - Month +2").Range("BI34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +2").Range("BI34:BI39").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("G7:G12").Copy
Sheets("Forecast - Month +2").Range("BJ34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +2").Range("BJ34:BJ39").NumberFormat = "#,##0,"
Sheets("Forecast - Month +2").Range("BK34:BK39").Value = "0"
Sheets("CZDataSource").Range("E7:E12").Copy
Sheets("Forecast - Month +2").Range("BO34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +2").Range("BO34:BO39").NumberFormat = "#,##0,"
Sheets("Forecast - Month +2").Range("BP34:BP39").Value = "0"
Sheets("CZDataSource").Range("H7:H12").Copy
Sheets("Forecast - Month +2").Range("BS34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +2").Range("BS34:BS39").NumberFormat = "#,##0,"
Sheets("Forecast - Month +2").Range("BT34:BT39").Value = "0"
'SK data input/output
Sheets("CZDataSource").Range("C13:C18").Copy
Sheets("Forecast - Month +2").Range("AZ55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +2").Range("AZ55:AZ60").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("D13:D18").Copy
Sheets("Forecast - Month +2").Range("BA55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +2").Range("BA55:BA60").NumberFormat = "#,##0,"
Sheets("Forecast - Month +2").Range("BB55:BB60").Value = "0"
Sheets("CZDataSource").Range("F13:F18").Copy
Sheets("Forecast - Month +2").Range("BI55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +2").Range("BI55:BI60").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("G13:G18").Copy
Sheets("Forecast - Month +2").Range("BJ55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +2").Range("BJ55:BJ60").NumberFormat = "#,##0,"
Sheets("Forecast - Month +2").Range("BK55:BK60").Value = "0"
Sheets("CZDataSource").Range("E13:E18").Copy
Sheets("Forecast - Month +2").Range("BO55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +2").Range("BO55:BO60").NumberFormat = "#,##0,"
Sheets("Forecast - Month +2").Range("BP55:BP60").Value = "0"
Sheets("CZDataSource").Range("H13:H18").Copy
Sheets("Forecast - Month +2").Range("BS55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +2").Range("BS55:BS60").NumberFormat = "#,##0,"
Sheets("Forecast - Month +2").Range("BT55:BT60").Value = "0"

ElseIf (Right(Range("C2"), 3) = "2nd") And Range("C3") = "RIG Forecast_2021_act.xlsx" Then
Sheets("CZDataSource").ListObjects("RIG_Forecast_output").QueryTable.Refresh BackgroundQuery:=False
'CZ data input/output
Sheets("CZDataSource").Range("C7:C12").Copy
Sheets("Forecast - Month").Range("AZ34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month").Range("AZ34:AZ39").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("D7:D12").Copy
Sheets("Forecast - Month").Range("BB34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month").Range("BB34:BB39").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("F7:F12").Copy
Sheets("Forecast - Month").Range("BI34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month").Range("BI34:BI39").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("G7:G12").Copy
Sheets("Forecast - Month").Range("BK34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month").Range("BK34:BK39").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("E7:E12").Copy
Sheets("Forecast - Month").Range("BP34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month").Range("BP34:BP39").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("H7:H12").Copy
Sheets("Forecast - Month").Range("BT34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month").Range("BT34:BT39").NumberFormat = "#,##0,"
'SK data input/output
Sheets("CZDataSource").Range("C13:C18").Copy
Sheets("Forecast - Month").Range("AZ55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month").Range("AZ55:AZ60").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("D13:D18").Copy
Sheets("Forecast - Month").Range("BB55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month").Range("BB55:BB60").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("F13:F18").Copy
Sheets("Forecast - Month").Range("BI55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month").Range("BI55:BI60").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("G13:G18").Copy
Sheets("Forecast - Month").Range("BK55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month").Range("BK55:BK60").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("E13:E18").Copy
Sheets("Forecast - Month").Range("BP55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month").Range("BP55:BP60").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("H13:H18").Copy
Sheets("Forecast - Month").Range("BT55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month").Range("BT55:BT60").NumberFormat = "#,##0,"

ElseIf (Right(Range("C2"), 3) = "2nd") And Range("C3") = "RIG Forecast_2021_m+1.xlsx" Then
Sheets("CZDataSource").ListObjects("RIG_Forecast_output").QueryTable.Refresh BackgroundQuery:=False
'CZ data input/output
Sheets("CZDataSource").Range("C7:C12").Copy
Sheets("Forecast - Month +1").Range("AZ34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +1").Range("AZ34:AZ39").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("D7:D12").Copy
Sheets("Forecast - Month +1").Range("BB34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +1").Range("BB34:BB39").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("F7:F12").Copy
Sheets("Forecast - Month +1").Range("BI34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +1").Range("BI34:BI39").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("G7:G12").Copy
Sheets("Forecast - Month +1").Range("BK34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +1").Range("BK34:BK39").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("E7:E12").Copy
Sheets("Forecast - Month +1").Range("BP34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +1").Range("BP34:BP39").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("H7:H12").Copy
Sheets("Forecast - Month +1").Range("BT34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +1").Range("BT34:BT39").NumberFormat = "#,##0,"
'SK data input/output
Sheets("CZDataSource").Range("C13:C18").Copy
Sheets("Forecast - Month +1").Range("AZ55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +1").Range("AZ55:AZ60").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("D13:D18").Copy
Sheets("Forecast - Month +1").Range("BB55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +1").Range("BB55:BB60").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("F13:F18").Copy
Sheets("Forecast - Month +1").Range("BI55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +1").Range("BI55:BI60").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("G13:G18").Copy
Sheets("Forecast - Month +1").Range("BK55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +1").Range("BK55:BK60").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("E13:E18").Copy
Sheets("Forecast - Month +1").Range("BP55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +1").Range("BP55:BP60").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("H13:H18").Copy
Sheets("Forecast - Month +1").Range("BT55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +1").Range("BT55:BT60").NumberFormat = "#,##0,"
ElseIf (Right(Range("C2"), 3) = "2nd") And Range("C3") = "RIG Forecast_2021_m+2.xlsx" Then
Sheets("CZDataSource").ListObjects("RIG_Forecast_output").QueryTable.Refresh BackgroundQuery:=False
'CZ data input/output
Sheets("CZDataSource").Range("C7:C12").Copy
Sheets("Forecast - Month +2").Range("AZ34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +2").Range("AZ34:AZ39").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("D7:D12").Copy
Sheets("Forecast - Month +2").Range("BB34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +2").Range("BB34:BB39").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("F7:F12").Copy
Sheets("Forecast - Month +2").Range("BI34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +2").Range("BI34:BI39").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("G7:G12").Copy
Sheets("Forecast - Month +2").Range("BK34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +2").Range("BK34:BK39").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("E7:E12").Copy
Sheets("Forecast - Month +2").Range("BP34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +2").Range("BP34:BP39").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("H7:H12").Copy
Sheets("Forecast - Month +2").Range("BT34").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +2").Range("BT34:BT39").NumberFormat = "#,##0,"
'SK data input/output
Sheets("CZDataSource").Range("C13:C18").Copy
Sheets("Forecast - Month +2").Range("AZ55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +2").Range("AZ55:AZ60").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("D13:D18").Copy
Sheets("Forecast - Month +2").Range("BB55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +2").Range("BB55:BB60").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("F13:F18").Copy
Sheets("Forecast - Month +2").Range("BI55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +2").Range("BI55:BI60").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("G13:G18").Copy
Sheets("Forecast - Month +2").Range("BK55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +2").Range("BK55:BK60").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("E13:E18").Copy
Sheets("Forecast - Month +2").Range("BP55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +2").Range("BP55:BP60").NumberFormat = "#,##0,"
Sheets("CZDataSource").Range("H13:H18").Copy
Sheets("Forecast - Month +2").Range("BT55").PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationNone
Sheets("Forecast - Month +2").Range("BT55:BT60").NumberFormat = "#,##0,"

Else

MsgBox ("there is something wrong")

End If

Next c
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.CutCopyMode = False
End Sub

基本上，我想摆脱这么多重复的步骤在特定的表，缩短和简化代码。

任何帮助都非常感谢。

Answer 1

将范围放入数组中，然后可以重复使用相同的代码行遍历它们。

Option Explicit
Sub SpitValues()
Const PREFIX = "RIG Forecast_2021_"
Dim dvCell As Range, inputRange As Range, c As Range
Dim wsSrc As Worksheet, wsTarget As Worksheet
Dim tbl As ListObject

'Drop-down list source
Set wsSrc = Sheets("CZDataSource")
Set dvCell = wsSrc.Range("C3")
Set inputRange = Evaluate(dvCell.Validation.Formula1)
Set tbl = wsSrc.ListObjects("RIG_Forecast_output")

' put source ranges into array
Dim arSrc, arTgt, arZeros, i As Integer, bError As Boolean, s As String
arSrc = Split("C,D,E,F,G,H", ",")

' put target and zero ranges for 1st/2nd into arrays
s = Right(wsSrc.Range("C2"), 3)
If s = "1st" Then
arTgt = Split("AZ,BA,BO,BI,BJ,BS", ",")
arZeros = Split("BB,BP,BK,BT", ",")
ElseIf s = "2nd" Then
arTgt = Split("AZ,BB,BP,BI,BK,BT", ",")
arZeros = Array()
Else
MsgBox "'" & s & "' is not 1st/2nd in C2", vbCritical
Exit Sub
End If

' copy source to target
Application.ScreenUpdating = False
For Each c In inputRange
dvCell.Value = c.Value
' select target sheet
If c.Value = PREFIX & "act.xlsx" Then
Set wsTarget = Sheets("Forecast - Month")
ElseIf c.Value = PREFIX & "m+1.xlsx" Then
Set wsTarget = Sheets("Forecast - Month +1")
ElseIf c.Value = PREFIX & "m+2.xlsx" Then
Set wsTarget = Sheets("Forecast - Month +2")
Else
bError = True
End If
' execute
If bError Then
MsgBox c.Value & " not recognized", vbExclamation, "Row " & c.Row
Else
tbl.QueryTable.Refresh BackgroundQuery:=False
With wsTarget
' loop through the src/tgt ranges
For i = 0 To UBound(arSrc)
'CZ data input/output
.Range(arTgt(i) & "34").Resize(6).Value2 = wsSrc.Range(arSrc(i) & "7").Resize(6).Value2
.Range(arTgt(i) & "34").Resize(6).NumberFormat = "#,##0,"
'SK data input/output
.Range(arTgt(i) & "55").Resize(6).Value2 = wsSrc.Range(arSrc(i) & "13").Resize(6).Value2
.Range(arTgt(i) & "55").Resize(6).NumberFormat = "#,##0,"
Next
' zero values
For i = 0 To UBound(arZeros)
.Range(arZeros(i) & "34").Resize(6).Value2 = 0
.Range(arZeros(i) & "55").Resize(6).Value2 = 0
Next
End With
s = s & vbLf & wsTarget.Name ' for msgbox
End If
Next
Application.ScreenUpdating = True
MsgBox "Sheets updated for " & s, vbInformation
End Sub

Answer 2

将做一个我在评论中描述的简短模型:

sub stuff()
dim sourceSheet as worksheet
set sourceSheet = Sheets("CZDataSource")
dim destSheet as worksheet
Select Case True
Case sourceSheet.Cells(2,3).Value =  "RIG Forecast_2021_act.xlsx"
set destSheet = Sheets("Forecast - Month")
Case sourceSheet.Cells(2,3).Value =  "RIG Forecast_2021_m+1.xlsx"
set destSheet = Sheets("Forecast - Month +1")
Case sourceSheet.Cells(2,3).Value =  "RIG Forecast_2021_m+2.xlsx"
set destSheet = Sheets("Forecast - Month +2")
End Select
Dim sourceRangeArray as variant
sourceRangeArray = sourceSheet.Range("C7:C12") ', Keep filling these
Dim destRangeArray as variant
destRangeArray = destSheet.Range("AZ34:AZ39") ', Keep filling these
Dim i as Long
For i = lbound(sourceRangeArray) to ubound(sourceRangeArray) 
DoTheThing sourceRangeArray(i),destRangeArray(i)
Next i
End Sub
Private Sub DoTheThing(sourceRange as Range, destRange as Range)
destRange.Value = sourceRange.Value
destRange.Numberformat = "#,##0,"
End Sub

无论目标表是什么，您都可以持续使用相同的范围，因此您可以在两个数组中以相同的顺序1:1地使用它们。

更改为.value = .value将节省您的复制和粘贴，并删除pastespecial的需要;如果有必要，您仍然会保留格式化，尽管我感觉您可以在每个操作结束时在更大的范围内进行格式化……有了这么多的范围，这样做在时间上应该不会太糟糕。

为你的目标表使用Select删除了在你的范围复制中发生的最大块，因此单独使用应该节省大量的时间/重复。

仔细看一下范围，"1st"one_answers"2 nd"似乎并不重要，因为您只在C3中查看工作簿的名称。这将进一步降低Select。