VBA script to copy data based on Cell criteria

  • In my attempt to learn from what you are sharing with me. I have created and attempted to modify the macro in the attached file. I will also be coping the forecast information into the attached using the last macro you sent. Thanks much.


    However, after modifying the first macro you sent, as per the attached file, I am getting a N/A in all sheets being copied, into the cells that I am not copying over information from; see attached. Please look at it and advice me how to rectify same; thanks again.


    In the interim, I will work on modifying the second macro you sent.

  • I corrected the error I made and it works well.


    I am now not sure the below works. It does not update the forecast, using the file I send earlier.


    Sub Forecastupdate()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim rMonth As Range, fnd As Range, WB As Workbook
    Set rMonth = Sheets("Jam").Range("J2")
    Set fnd = Sheets("Jam").Rows(4).Find(rMonth, LookIn:=xlValues, lookat:=xlWhole)
    With Sheets("Jam")
    For Each WB In Workbooks
    If WB.Name Like "Jam*" Then
    .Cells(6, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(5, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
    .Cells(16, fnd.Column + 1).Resize(16, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(16, fnd.Column + 1).Resize(16, 15 - fnd.Column).Value
    .Cells(42, fnd.Column + 1).Resize(1, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(1, fnd.Column + 1).Resize(1, 15 - fnd.Column).Value
    .Cells(44, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(2, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
    .Cells(61, fnd.Column + 1).Resize(17, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(17, fnd.Column + 1).Resize(17, 15 - fnd.Column).Value
    .Cells(82, fnd.Column + 1).Resize(20, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(20, fnd.Column + 1).Resize(20, 15 - fnd.Column).Value
    .Cells(103, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(3, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value
    .Cells(115, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(5, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
    .Cells(124, fnd.Column + 1).Resize(7, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(7, fnd.Column + 1).Resize(7, 15 - fnd.Column).Value
    .Cells(146, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(2, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
    .Cells(157, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(2, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
    .Cells(162, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(5, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
    .Cells(197, fnd.Column + 1).Resize(16, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(16, fnd.Column + 1).Resize(16, 15 - fnd.Column).Value
    .Cells(301, fnd.Column + 1).Resize(7, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(7, fnd.Column + 1).Resize(7, 15 - fnd.Column).Value
    .Cells(327, fnd.Column + 1).Resize(4, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(4, fnd.Column + 1).Resize(4, 15 - fnd.Column).Value
    .Cells(334, fnd.Column + 1).Resize(21, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(21, fnd.Column + 1).Resize(21, 15 - fnd.Column).Value
    .Cells(356, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(3, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value
    .Cells(361, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(5, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
    .Cells(370, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(2, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
    Exit For
    End If
    Next WB
    End With
    With Sheets("Barb")
    For Each WB In Workbooks
    If WB.Name Like "Barb*" Then
    .Cells(6, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(5, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
    .Cells(16, fnd.Column + 1).Resize(16, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(16, fnd.Column + 1).Resize(16, 15 - fnd.Column).Value
    .Cells(42, fnd.Column + 1).Resize(1, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(1, fnd.Column + 1).Resize(1, 15 - fnd.Column).Value
    .Cells(44, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(2, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
    .Cells(61, fnd.Column + 1).Resize(17, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(17, fnd.Column + 1).Resize(17, 15 - fnd.Column).Value
    .Cells(82, fnd.Column + 1).Resize(20, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(20, fnd.Column + 1).Resize(20, 15 - fnd.Column).Value
    .Cells(103, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(3, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value
    .Cells(115, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(5, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
    .Cells(124, fnd.Column + 1).Resize(7, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(7, fnd.Column + 1).Resize(7, 15 - fnd.Column).Value
    .Cells(146, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(2, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
    .Cells(157, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(2, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
    .Cells(162, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(5, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
    .Cells(197, fnd.Column + 1).Resize(16, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(16, fnd.Column + 1).Resize(16, 15 - fnd.Column).Value
    .Cells(301, fnd.Column + 1).Resize(7, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(7, fnd.Column + 1).Resize(7, 15 - fnd.Column).Value
    .Cells(327, fnd.Column + 1).Resize(4, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(4, fnd.Column + 1).Resize(4, 15 - fnd.Column).Value
    .Cells(334, fnd.Column + 1).Resize(21, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(21, fnd.Column + 1).Resize(21, 15 - fnd.Column).Value
    .Cells(356, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(3, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value
    .Cells(361, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(5, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
    .Cells(370, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(2, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
    Exit For
    End If
    Next WB
    End With
    With Sheets("Trin")
    For Each WB In Workbooks
    If WB.Name Like "Trin*" Then
    .Cells(6, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(5, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
    .Cells(16, fnd.Column + 1).Resize(16, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(16, fnd.Column + 1).Resize(16, 15 - fnd.Column).Value
    .Cells(42, fnd.Column + 1).Resize(1, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(1, fnd.Column + 1).Resize(1, 15 - fnd.Column).Value
    .Cells(44, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(2, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
    .Cells(61, fnd.Column + 1).Resize(17, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(17, fnd.Column + 1).Resize(17, 15 - fnd.Column).Value
    .Cells(82, fnd.Column + 1).Resize(20, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(20, fnd.Column + 1).Resize(20, 15 - fnd.Column).Value
    .Cells(103, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(3, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value
    .Cells(115, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(5, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
    .Cells(124, fnd.Column + 1).Resize(7, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(7, fnd.Column + 1).Resize(7, 15 - fnd.Column).Value
    .Cells(146, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(2, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
    .Cells(157, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(2, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
    .Cells(162, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(5, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
    .Cells(197, fnd.Column + 1).Resize(16, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(16, fnd.Column + 1).Resize(16, 15 - fnd.Column).Value
    .Cells(301, fnd.Column + 1).Resize(7, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(7, fnd.Column + 1).Resize(7, 15 - fnd.Column).Value
    .Cells(327, fnd.Column + 1).Resize(4, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(4, fnd.Column + 1).Resize(4, 15 - fnd.Column).Value
    .Cells(334, fnd.Column + 1).Resize(21, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(21, fnd.Column + 1).Resize(21, 15 - fnd.Column).Value
    .Cells(356, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(3, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value
    .Cells(361, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(5, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
    .Cells(370, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(2, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
    Exit For
    End If
    Next WB
    End With
    With Sheets("Bah")
    For Each WB In Workbooks
    If WB.Name Like "Bah*" Then
    .Cells(6, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(5, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
    .Cells(16, fnd.Column + 1).Resize(16, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(16, fnd.Column + 1).Resize(16, 15 - fnd.Column).Value
    .Cells(42, fnd.Column + 1).Resize(1, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(1, fnd.Column + 1).Resize(1, 15 - fnd.Column).Value
    .Cells(44, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(2, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
    .Cells(61, fnd.Column + 1).Resize(17, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(17, fnd.Column + 1).Resize(17, 15 - fnd.Column).Value
    .Cells(82, fnd.Column + 1).Resize(20, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(20, fnd.Column + 1).Resize(20, 15 - fnd.Column).Value
    .Cells(103, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(3, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value
    .Cells(115, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(5, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
    .Cells(124, fnd.Column + 1).Resize(7, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(7, fnd.Column + 1).Resize(7, 15 - fnd.Column).Value
    .Cells(146, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(2, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
    .Cells(157, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(2, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
    .Cells(162, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(5, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
    .Cells(197, fnd.Column + 1).Resize(16, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(16, fnd.Column + 1).Resize(16, 15 - fnd.Column).Value
    .Cells(301, fnd.Column + 1).Resize(7, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(7, fnd.Column + 1).Resize(7, 15 - fnd.Column).Value
    .Cells(327, fnd.Column + 1).Resize(4, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(4, fnd.Column + 1).Resize(4, 15 - fnd.Column).Value
    .Cells(334, fnd.Column + 1).Resize(21, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(21, fnd.Column + 1).Resize(21, 15 - fnd.Column).Value
    .Cells(356, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(3, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value
    .Cells(361, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(5, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
    .Cells(370, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(2, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
    Exit For
    End If
    Next WB
    End With
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    End Sub
    Sub CopyData()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim rMonth As Range, fnd As Range, srcWS As Worksheet
    Set rMonth = Sheets("Jam").Range("J2")
    Set srcWS = Sheets("CM-Act")
    Set fnd = Sheets("Jam").Rows(4).Find(rMonth, LookIn:=xlValues, lookat:=xlWhole)
    With Sheets("Jam")
    .Cells(6, fnd.Column).Resize(5).Value = srcWS.Range("B6:B10").Value
    .Cells(16, fnd.Column).Resize(16).Value = srcWS.Range("B16:B40").Value
    .Cells(42, fnd.Column).Resize(1).Value = srcWS.Range("B42").Value
    .Cells(44, fnd.Column).Resize(2).Value = srcWS.Range("B44:B45").Value
    .Cells(61, fnd.Column).Resize(17).Value = srcWS.Range("B61:B77").Value
    .Cells(82, fnd.Column).Resize(20).Value = srcWS.Range("B82:B101").Value
    .Cells(103, fnd.Column).Resize(3).Value = srcWS.Range("B103:B105").Value
    .Cells(115, fnd.Column).Resize(5).Value = srcWS.Range("B115:B119").Value
    .Cells(124, fnd.Column).Resize(7).Value = srcWS.Range("B124:B130").Value
    .Cells(146, fnd.Column).Resize(2).Value = srcWS.Range("B146:B147").Value
    .Cells(157, fnd.Column).Resize(2).Value = srcWS.Range("B157:B158").Value
    .Cells(162, fnd.Column).Resize(5).Value = srcWS.Range("B162:B166").Value
    .Cells(197, fnd.Column).Resize(16).Value = srcWS.Range("B197:B212").Value
    .Cells(301, fnd.Column).Resize(7).Value = srcWS.Range("B301:B307").Value
    .Cells(327, fnd.Column).Resize(4).Value = srcWS.Range("B327:B330").Value
    .Cells(334, fnd.Column).Resize(21).Value = srcWS.Range("B334:B354").Value
    .Cells(356, fnd.Column).Resize(3).Value = srcWS.Range("B356:B358").Value
    .Cells(361, fnd.Column).Resize(5).Value = srcWS.Range("B361:B365").Value
    .Cells(370, fnd.Column).Resize(2).Value = srcWS.Range("B370:B371").Value
    End With
    With Sheets("Barb")
    .Cells(6, fnd.Column).Resize(5).Value = srcWS.Range("B6:B10").Value
    .Cells(16, fnd.Column).Resize(16).Value = srcWS.Range("B16:B40").Value
    .Cells(42, fnd.Column).Resize(1).Value = srcWS.Range("B42").Value
    .Cells(44, fnd.Column).Resize(2).Value = srcWS.Range("B44:B45").Value
    .Cells(61, fnd.Column).Resize(17).Value = srcWS.Range("B61:B77").Value
    .Cells(82, fnd.Column).Resize(20).Value = srcWS.Range("B82:B101").Value
    .Cells(103, fnd.Column).Resize(3).Value = srcWS.Range("B103:B105").Value
    .Cells(115, fnd.Column).Resize(5).Value = srcWS.Range("B115:B119").Value
    .Cells(124, fnd.Column).Resize(7).Value = srcWS.Range("B124:B130").Value
    .Cells(146, fnd.Column).Resize(2).Value = srcWS.Range("B146:B147").Value
    .Cells(157, fnd.Column).Resize(2).Value = srcWS.Range("B157:B158").Value
    .Cells(162, fnd.Column).Resize(5).Value = srcWS.Range("B162:B166").Value
    .Cells(197, fnd.Column).Resize(16).Value = srcWS.Range("B197:B212").Value
    .Cells(301, fnd.Column).Resize(7).Value = srcWS.Range("B301:B307").Value
    .Cells(327, fnd.Column).Resize(4).Value = srcWS.Range("B327:B330").Value
    .Cells(334, fnd.Column).Resize(21).Value = srcWS.Range("B334:B354").Value
    .Cells(356, fnd.Column).Resize(3).Value = srcWS.Range("B356:B358").Value
    .Cells(361, fnd.Column).Resize(5).Value = srcWS.Range("B361:B365").Value
    .Cells(370, fnd.Column).Resize(2).Value = srcWS.Range("B370:B371").Value
    End With
    With Sheets("Trin")
    .Cells(6, fnd.Column).Resize(5).Value = srcWS.Range("B6:B10").Value
    .Cells(16, fnd.Column).Resize(16).Value = srcWS.Range("B16:B40").Value
    .Cells(42, fnd.Column).Resize(1).Value = srcWS.Range("B42").Value
    .Cells(44, fnd.Column).Resize(2).Value = srcWS.Range("B44:B45").Value
    .Cells(61, fnd.Column).Resize(17).Value = srcWS.Range("B61:B77").Value
    .Cells(82, fnd.Column).Resize(20).Value = srcWS.Range("B82:B101").Value
    .Cells(103, fnd.Column).Resize(3).Value = srcWS.Range("B103:B105").Value
    .Cells(115, fnd.Column).Resize(5).Value = srcWS.Range("B115:B119").Value
    .Cells(124, fnd.Column).Resize(7).Value = srcWS.Range("B124:B130").Value
    .Cells(146, fnd.Column).Resize(2).Value = srcWS.Range("B146:B147").Value
    .Cells(157, fnd.Column).Resize(2).Value = srcWS.Range("B157:B158").Value
    .Cells(162, fnd.Column).Resize(5).Value = srcWS.Range("B162:B166").Value
    .Cells(197, fnd.Column).Resize(16).Value = srcWS.Range("B197:B212").Value
    .Cells(301, fnd.Column).Resize(7).Value = srcWS.Range("B301:B307").Value
    .Cells(327, fnd.Column).Resize(4).Value = srcWS.Range("B327:B330").Value
    .Cells(334, fnd.Column).Resize(21).Value = srcWS.Range("B334:B354").Value
    .Cells(356, fnd.Column).Resize(3).Value = srcWS.Range("B356:B358").Value
    .Cells(361, fnd.Column).Resize(5).Value = srcWS.Range("B361:B365").Value
    .Cells(370, fnd.Column).Resize(2).Value = srcWS.Range("B370:B371").Value
    End With
    With Sheets("Bah")
    .Cells(6, fnd.Column).Resize(5).Value = srcWS.Range("B6:B10").Value
    .Cells(16, fnd.Column).Resize(16).Value = srcWS.Range("B16:B40").Value
    .Cells(42, fnd.Column).Resize(1).Value = srcWS.Range("B42").Value
    .Cells(44, fnd.Column).Resize(2).Value = srcWS.Range("B44:B45").Value
    .Cells(61, fnd.Column).Resize(17).Value = srcWS.Range("B61:B77").Value
    .Cells(82, fnd.Column).Resize(20).Value = srcWS.Range("B82:B101").Value
    .Cells(103, fnd.Column).Resize(3).Value = srcWS.Range("B103:B105").Value
    .Cells(115, fnd.Column).Resize(5).Value = srcWS.Range("B115:B119").Value
    .Cells(124, fnd.Column).Resize(7).Value = srcWS.Range("B124:B130").Value
    .Cells(146, fnd.Column).Resize(2).Value = srcWS.Range("B146:B147").Value
    .Cells(157, fnd.Column).Resize(2).Value = srcWS.Range("B157:B158").Value
    .Cells(162, fnd.Column).Resize(5).Value = srcWS.Range("B162:B166").Value
    .Cells(197, fnd.Column).Resize(16).Value = srcWS.Range("B197:B212").Value
    .Cells(301, fnd.Column).Resize(7).Value = srcWS.Range("B301:B307").Value
    .Cells(327, fnd.Column).Resize(4).Value = srcWS.Range("B327:B330").Value
    .Cells(334, fnd.Column).Resize(21).Value = srcWS.Range("B334:B354").Value
    .Cells(356, fnd.Column).Resize(3).Value = srcWS.Range("B356:B358").Value
    .Cells(361, fnd.Column).Resize(5).Value = srcWS.Range("B361:B365").Value
    .Cells(370, fnd.Column).Resize(2).Value = srcWS.Range("B370:B371").Value
    End With
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    End Sub

  • To eliminate all the repetition, I've modified the macro as below. Give it a try. Please note the changes in the "Resize" values. If you need to add more sheets or delete sheets, simply add or delete the sheet name in shArr. You have to make sure that the order in shArr is the same as the sheets appear in your workbook.


    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.


  • I am unable to get this one to work.

  • Try these:



    If you are still having problems, please attach updated copies of the Actual Forecast file and the 4 forecast files.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • The UpdateActualsOnly macro seems to be working properly. Try the revised one below:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.