VBA script to copy data based on Cell criteria

Kthom019 · Jun 11th 2019

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.

Kthom019 · Jun 11th 2019

I am seeing an error I made with the resize values. I am adjusting them. Will keep you posted; thanks.:0ops:

Kthom019 · Jun 11th 2019

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

Mumps · Jun 12th 2019

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.

Code

Sub UpdateActualsOnly()
    Application.ScreenUpdating = False
    Dim month As Range, fnd As Range, srcWS As Worksheet, shArr As Variant, i As Long, colLetter As String, col As Long: col = 2
    Set month = Sheets("JAM").Range("J2")
    Set srcWS = Sheets("CM-Act")
    Set fnd = Sheets("JAM").Rows(4).Find(month, LookIn:=xlValues, lookat:=xlWhole)
    shArr = Array("JAM", "Barb", "TRIN", "BAH")
    For i = LBound(shArr) To UBound(shArr)
        With Sheets(shArr(i))
            colLetter = Replace(Cells(1, col).Address(False, False), "1", "")
            .Cells(6, fnd.Column).Resize(5).Value = srcWS.Range(colLetter & "6:" & colLetter & "10").Value
            .Cells(16, fnd.Column).Resize(25).Value = srcWS.Range(colLetter & "16:" & colLetter & "40").Value
            .Cells(42, fnd.Column).Value = srcWS.Range(colLetter & "42").Value
            .Cells(44, fnd.Column).Resize(2).Value = srcWS.Range(colLetter & "44:" & colLetter & "45").Value
            .Cells(61, fnd.Column).Resize(17).Value = srcWS.Range(colLetter & "61:" & colLetter & "67").Value
            .Cells(82, fnd.Column).Resize(20).Value = srcWS.Range(colLetter & "82:" & colLetter & "101").Value
            .Cells(103, fnd.Column).Resize(3).Value = srcWS.Range(colLetter & "103:" & colLetter & "105").Value
            .Cells(115, fnd.Column).Resize(5).Value = srcWS.Range(colLetter & "115:" & colLetter & "119").Value
            .Cells(124, fnd.Column).Resize(7).Value = srcWS.Range(colLetter & "124:" & colLetter & "130").Value
            .Cells(146, fnd.Column).Resize(2).Value = srcWS.Range(colLetter & "146:" & colLetter & "147").Value
            .Cells(149, fnd.Column).Resize(6).Value = srcWS.Range(colLetter & "149:" & colLetter & "154").Value
            .Cells(156, fnd.Column).Resize(5).Value = srcWS.Range(colLetter & "156:" & colLetter & "160").Value
            .Cells(162, fnd.Column).Resize(5).Value = srcWS.Range(colLetter & "162:" & colLetter & "166").Value
            .Cells(171, fnd.Column).Resize(6).Value = srcWS.Range(colLetter & "171:" & colLetter & "176").Value
            .Cells(178, fnd.Column).Resize(6).Value = srcWS.Range(colLetter & "178:" & colLetter & "183").Value
            .Cells(186, fnd.Column).Resize(3).Value = srcWS.Range(colLetter & "186:" & colLetter & "188").Value
            .Cells(197, fnd.Column).Resize(6).Value = srcWS.Range(colLetter & "197:" & colLetter & "212").Value
            .Cells(301, fnd.Column).Resize(7).Value = srcWS.Range(colLetter & "301:" & colLetter & "307").Value
            .Cells(327, fnd.Column).Resize(4).Value = srcWS.Range(colLetter & "327:" & colLetter & "330").Value
            .Cells(334, fnd.Column).Resize(21).Value = srcWS.Range(colLetter & "334:" & colLetter & "354").Value
            .Cells(356, fnd.Column).Resize(3).Value = srcWS.Range(colLetter & "356:" & colLetter & "358").Value
            .Cells(361, fnd.Column).Resize(5).Value = srcWS.Range(colLetter & "361:" & colLetter & "365").Value
            .Cells(370, fnd.Column).Resize(2).Value = srcWS.Range(colLetter & "370:" & colLetter & "371").Value
        End With
        col = col + 1
    Next i
    Application.ScreenUpdating = True
End Sub

Display More

Kthom019 · Jun 16th 2019

Awe Mumps, thank you so much.
Can this modified macro be used to copy over the information from the forecast files?

Kthom019 · Jun 16th 2019

Quote from Kthom019

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

Display More

I am unable to get this one to work.

Mumps · Jun 16th 2019

Try these:

Code

Sub UpdateActualsOnly()
    Application.ScreenUpdating = False
    Dim month As Range, fnd As Range, srcWS As Worksheet, shArr As Variant, i As Long, colLetter As String, col As Long: col = 2
    Set month = Sheets("JAM").Range("J2")
    Set srcWS = Sheets("CM-Act")
    Set fnd = Sheets("JAM").Rows(4).Find(month, LookIn:=xlValues, lookat:=xlWhole)
    shArr = Array("JAM", "Barb", "TRIN", "BAH")
    For i = LBound(shArr) To UBound(shArr)
        With Sheets(shArr(i))
            colLetter = Replace(Cells(1, col).Address(False, False), "1", "")
            .Cells(6, fnd.Column).Resize(5).Value = srcWS.Range(colLetter & "6:" & colLetter & "10").Value
            .Cells(16, fnd.Column).Resize(25).Value = srcWS.Range(colLetter & "16:" & colLetter & "40").Value
            .Cells(42, fnd.Column).Value = srcWS.Range(colLetter & "42").Value
            .Cells(44, fnd.Column).Resize(2).Value = srcWS.Range(colLetter & "44:" & colLetter & "45").Value
            .Cells(61, fnd.Column).Resize(17).Value = srcWS.Range(colLetter & "61:" & colLetter & "67").Value
            .Cells(82, fnd.Column).Resize(20).Value = srcWS.Range(colLetter & "82:" & colLetter & "101").Value
            .Cells(103, fnd.Column).Resize(3).Value = srcWS.Range(colLetter & "103:" & colLetter & "105").Value
            .Cells(115, fnd.Column).Resize(5).Value = srcWS.Range(colLetter & "115:" & colLetter & "119").Value
            .Cells(124, fnd.Column).Resize(7).Value = srcWS.Range(colLetter & "124:" & colLetter & "130").Value
            .Cells(146, fnd.Column).Resize(2).Value = srcWS.Range(colLetter & "146:" & colLetter & "147").Value
            .Cells(149, fnd.Column).Resize(6).Value = srcWS.Range(colLetter & "149:" & colLetter & "154").Value
            .Cells(156, fnd.Column).Resize(5).Value = srcWS.Range(colLetter & "156:" & colLetter & "160").Value
            .Cells(162, fnd.Column).Resize(5).Value = srcWS.Range(colLetter & "162:" & colLetter & "166").Value
            .Cells(171, fnd.Column).Resize(6).Value = srcWS.Range(colLetter & "171:" & colLetter & "176").Value
            .Cells(178, fnd.Column).Resize(6).Value = srcWS.Range(colLetter & "178:" & colLetter & "183").Value
            .Cells(186, fnd.Column).Resize(3).Value = srcWS.Range(colLetter & "186:" & colLetter & "188").Value
            .Cells(197, fnd.Column).Resize(6).Value = srcWS.Range(colLetter & "197:" & colLetter & "212").Value
            .Cells(301, fnd.Column).Resize(7).Value = srcWS.Range(colLetter & "301:" & colLetter & "307").Value
            .Cells(327, fnd.Column).Resize(4).Value = srcWS.Range(colLetter & "327:" & colLetter & "330").Value
            .Cells(334, fnd.Column).Resize(21).Value = srcWS.Range(colLetter & "334:" & colLetter & "354").Value
            .Cells(356, fnd.Column).Resize(3).Value = srcWS.Range(colLetter & "356:" & colLetter & "358").Value
            .Cells(361, fnd.Column).Resize(5).Value = srcWS.Range(colLetter & "361:" & colLetter & "365").Value
            .Cells(370, fnd.Column).Resize(2).Value = srcWS.Range(colLetter & "370:" & colLetter & "371").Value
        End With
        col = col + 1
    Next i
    Application.ScreenUpdating = True
End Sub

Display More

Code

Sub UpDateForecast()
    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

Display More

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

Kthom019 · Jun 16th 2019

Okay, thanks. This is the consolidated Forecast file. The 4 workbooks should be copied over into this workbook. But only for the months that are not in Cell J2

Kthom019 · Jun 16th 2019

These are 2 of the individual files

Kthom019 · Jun 16th 2019

These are the remainder of the files.

Mumps · Jun 18th 2019

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

Code

Sub UpDateForecast()
    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(6, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
                .Cells(16, fnd.Column + 1).Resize(25, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(16, fnd.Column + 1).Resize(25, 15 - fnd.Column).Value
                .Cells(42, fnd.Column + 1).Resize(1, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(42, fnd.Column + 1).Resize(1, 15 - fnd.Column).Value
                .Cells(44, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(44, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
                .Cells(61, fnd.Column + 1).Resize(17, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(61, fnd.Column + 1).Resize(17, 15 - fnd.Column).Value
                .Cells(82, fnd.Column + 1).Resize(19, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(82, fnd.Column + 1).Resize(19, 15 - fnd.Column).Value
                .Cells(103, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(103, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value
                .Cells(115, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(115, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
                .Cells(124, fnd.Column + 1).Resize(7, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(124, fnd.Column + 1).Resize(7, 15 - fnd.Column).Value
                .Cells(146, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(146, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
                .Cells(157, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(157, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
                .Cells(162, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(162, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
                .Cells(197, fnd.Column + 1).Resize(16, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(197, fnd.Column + 1).Resize(16, 15 - fnd.Column).Value
                .Cells(301, fnd.Column + 1).Resize(6, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(301, fnd.Column + 1).Resize(6, 15 - fnd.Column).Value
                .Cells(327, fnd.Column + 1).Resize(4, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(327, fnd.Column + 1).Resize(4, 15 - fnd.Column).Value
                .Cells(334, fnd.Column + 1).Resize(21, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(334, fnd.Column + 1).Resize(21, 15 - fnd.Column).Value
                .Cells(356, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(356, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value
                .Cells(361, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(361, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
                .Cells(370, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(370, 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(6, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
                .Cells(16, fnd.Column + 1).Resize(25, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(16, fnd.Column + 1).Resize(25, 15 - fnd.Column).Value
                .Cells(42, fnd.Column + 1).Resize(1, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(42, fnd.Column + 1).Resize(1, 15 - fnd.Column).Value
                .Cells(44, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(44, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
                .Cells(61, fnd.Column + 1).Resize(17, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(61, fnd.Column + 1).Resize(17, 15 - fnd.Column).Value
                .Cells(82, fnd.Column + 1).Resize(19, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(82, fnd.Column + 1).Resize(19, 15 - fnd.Column).Value
                .Cells(103, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(103, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value
                .Cells(115, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(115, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
                .Cells(124, fnd.Column + 1).Resize(7, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(124, fnd.Column + 1).Resize(7, 15 - fnd.Column).Value
                .Cells(146, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(146, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
                .Cells(157, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(157, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
                .Cells(162, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(162, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
                .Cells(197, fnd.Column + 1).Resize(16, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(197, fnd.Column + 1).Resize(16, 15 - fnd.Column).Value
                .Cells(301, fnd.Column + 1).Resize(6, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(301, fnd.Column + 1).Resize(6, 15 - fnd.Column).Value
                .Cells(327, fnd.Column + 1).Resize(4, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(327, fnd.Column + 1).Resize(4, 15 - fnd.Column).Value
                .Cells(334, fnd.Column + 1).Resize(21, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(334, fnd.Column + 1).Resize(21, 15 - fnd.Column).Value
                .Cells(356, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(356, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value
                .Cells(361, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(361, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
                .Cells(370, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(370, 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(6, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
                .Cells(16, fnd.Column + 1).Resize(25, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(16, fnd.Column + 1).Resize(25, 15 - fnd.Column).Value
                .Cells(42, fnd.Column + 1).Resize(1, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(42, fnd.Column + 1).Resize(1, 15 - fnd.Column).Value
                .Cells(44, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(44, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
                .Cells(61, fnd.Column + 1).Resize(17, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(61, fnd.Column + 1).Resize(17, 15 - fnd.Column).Value
                .Cells(82, fnd.Column + 1).Resize(19, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(82, fnd.Column + 1).Resize(19, 15 - fnd.Column).Value
                .Cells(103, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(103, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value
                .Cells(115, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(115, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
                .Cells(124, fnd.Column + 1).Resize(7, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(124, fnd.Column + 1).Resize(7, 15 - fnd.Column).Value
                .Cells(146, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(146, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
                .Cells(157, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(157, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
                .Cells(162, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(162, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
                .Cells(197, fnd.Column + 1).Resize(16, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(197, fnd.Column + 1).Resize(16, 15 - fnd.Column).Value
                .Cells(301, fnd.Column + 1).Resize(6, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(301, fnd.Column + 1).Resize(6, 15 - fnd.Column).Value
                .Cells(327, fnd.Column + 1).Resize(4, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(327, fnd.Column + 1).Resize(4, 15 - fnd.Column).Value
                .Cells(334, fnd.Column + 1).Resize(21, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(334, fnd.Column + 1).Resize(21, 15 - fnd.Column).Value
                .Cells(356, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(356, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value
                .Cells(361, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(361, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
                .Cells(370, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(370, 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(6, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
                .Cells(16, fnd.Column + 1).Resize(25, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(16, fnd.Column + 1).Resize(25, 15 - fnd.Column).Value
                .Cells(42, fnd.Column + 1).Resize(1, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(42, fnd.Column + 1).Resize(1, 15 - fnd.Column).Value
                .Cells(44, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(44, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
                .Cells(61, fnd.Column + 1).Resize(17, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(61, fnd.Column + 1).Resize(17, 15 - fnd.Column).Value
                .Cells(82, fnd.Column + 1).Resize(19, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(82, fnd.Column + 1).Resize(19, 15 - fnd.Column).Value
                .Cells(103, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(103, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value
                .Cells(115, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(115, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
                .Cells(124, fnd.Column + 1).Resize(7, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(124, fnd.Column + 1).Resize(7, 15 - fnd.Column).Value
                .Cells(146, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(146, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
                .Cells(157, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(157, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value
                .Cells(162, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(162, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
                .Cells(197, fnd.Column + 1).Resize(16, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(197, fnd.Column + 1).Resize(16, 15 - fnd.Column).Value
                .Cells(301, fnd.Column + 1).Resize(6, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(301, fnd.Column + 1).Resize(6, 15 - fnd.Column).Value
                .Cells(327, fnd.Column + 1).Resize(4, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(327, fnd.Column + 1).Resize(4, 15 - fnd.Column).Value
                .Cells(334, fnd.Column + 1).Resize(21, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(334, fnd.Column + 1).Resize(21, 15 - fnd.Column).Value
                .Cells(356, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(356, fnd.Column + 1).Resize(3, 15 - fnd.Column).Value
                .Cells(361, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(361, fnd.Column + 1).Resize(5, 15 - fnd.Column).Value
                .Cells(370, fnd.Column + 1).Resize(2, 15 - fnd.Column).Value = WB.Sheets("FCAST").Cells(370, 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

Display More

Kthom019 · Jun 20th 2019

Thank you Genius, both are now working beautifully. I really appreciate your assistance on this.

Mumps · Jun 20th 2019

You are very welcome.

VBA script to copy data based on Cell criteria

Participate now!

Share

Users Viewing This Thread