Home > Mobile >  Repeating copy/paste steps in specify sheets - how can I simplify my VBA code?
Repeating copy/paste steps in specify sheets - how can I simplify my VBA code?

Time：10-17

I am beginner in VBA and I would like to ask someone of more experienced folks to simplify my VBA code.

The code works really simple. Macro goes through drill-down list of sourcing .xlsx files, refresh table as PowerQuery result every time drill-down reference is set by loop (sheet "CZDataSource") and do same repeating copy/paste steps in each three sheets of the same workbook (sheets "Forecast - Month", "Forecast - Month 1", "Forecast - Month 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

Basically, I would like to get rid of so many repeating steps in particular sheets and cut short and simplify the code.

Any help greatly appreciated.

CodePudding user response：

Will do a short mockup of what I described in my comment:

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

You continually use the same ranges, regardless of destination sheet, so you can have them 1:1 in the same order amongst the two arrays.

Changing to .value = .value will save you the copying and pasting, and removes the need for pastespecial; you would still keep the formatting, if it's necessary, though I have a feeling that you could format at the end over a larger range than inside of each operation... with the amount of ranges, it shouldn't be too terrible timewise to do as is.

The use of Select for your destination sheet removes the biggest chunk of what was occurring in your duplication of ranges, so that alone should save a huge chunk of time/repetition.

In looking at the ranges a little closer, the use of "1st" and "2nd" doesn't really seem to matter, since you're only looking at the name of a workbook in C3. That would cut the Select down further.

CodePudding user response：

Put your ranges into arrays and then you can loop through them re-using the same code lines.

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

Page link：https//www.codepudding.com/Mobile/155189.html

Prev:Trigger events with the insertion and deletion of rows in VBA Excel
Next:How to get access to property inside ActionFilter?
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding