Posts by Kthom019

Kthom019 · Jun 20th 2019

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

Kthom019 · Jun 16th 2019

These are the remainder of the files.

Kthom019 · Jun 16th 2019

These are 2 of the individual 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

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.

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 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

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

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

Thank you MUMPS. Much Appreciated.

Kthom019 · Jun 9th 2019

Thanks, it works well. However, I want the actions separated. I want to keep the first Macro you gave me and have this new one only to update the forecast. I will have separate buttons to reflect both Macros.

How do I modify to have them separated?

Kthom019 · Jun 8th 2019

Quote from Mumps

Will the
individual forecast workbooks always be open or does the macro have to open them?

They will always be open.

Kthom019 · Jun 7th 2019

Other files attached. The forecast files will always be in separate workbooks.

Kthom019 · Jun 7th 2019

Thanks.
Each month the Actual will be updated from the "Update Cur" sheet in the 'P&L Actual-Forecast' workbook. Likewise, the other forecasted months should be updated from individual workbooks.
example:
April is update from the first macro to show the actual information
May to Mar should be updated from the individual forecast files

In May
May is update from the first macro to show the actual information
June to Mar should be updated from the individual forecast files

In June
June is update from the first macro to show the actual information
July to Mar should be updated from the individual forecast files

In July
July is update from the first macro to show the actual information
August to Mar should be updated from the individual forecast files

In August
August is update from the first macro to show the actual information
September to Mar should be updated from the individual forecast files

In September
September is update from the first macro to show the actual information
October to Mar should be updated from the individual forecast files

and so on until Mar is update with Actual information from the "Update Cur" sheet.

Next year the cycle continues.

The other two attachments will follow due to the limit on attachments.

Kthom019 · Jun 5th 2019

Hi [USER="138669"]Mumps[/USER], I crave your assistance with another macro, on this same work file. I will now have another workbook, named forecast, which should update these sheets but only for the other months. E.g. If actual is April, the forecast should update from May to March. Should I send the forecast file.

Kthom019 · May 13th 2019

This is absolutely brilliant! It works perfectly. I even added another sheet adjusted the code and it was brilliant. Thanks a million times.

Quote from Mumps

Try:

Code

Sub CopyData()
Application.ScreenUpdating = False
Dim month As Range, fnd As Range, srcWS As Worksheet
Set month = Sheets("Jeep").Range("I2")
Set srcWS = Sheets("Update Cur")
Set fnd = Sheets("Jeep").Rows(4).Find(month, LookIn:=xlValues, lookat:=xlWhole)
With Sheets("Jeep")
.Cells(6, fnd.Column).Resize(6).Value = srcWS.Range("B6:B11").Value
.Cells(14, fnd.Column).Resize(14).Value = srcWS.Range("B14:B27").Value
End With
With Sheets("Barn")
.Cells(6, fnd.Column).Resize(6).Value = srcWS.Range("C6:C11").Value
.Cells(14, fnd.Column).Resize(14).Value = srcWS.Range("C14:C27").Value
End With
With Sheets("Track")
.Cells(6, fnd.Column).Resize(6).Value = srcWS.Range("D6:D11").Value
.Cells(14, fnd.Column).Resize(14).Value = srcWS.Range("D14:D27").Value
End With
Application.ScreenUpdating = True
End Sub

Display More

Kthom019 · May 11th 2019

Just a few questions for clarification:
- Are columns B, C and D in the "Update Cur" sheet the values for JEEP, BARN & TRACK respectively? Yes, the values are for each sheet respectively.
- Each of the 3 sheets JEEP, BARN & TRACK currently has values for each month. Do you want the values from the "Update Cur" sheet to replace the values in the other 3 sheets or to be added to the values that are currently there? The values in Column C "Apr", should be replaced with the values from the "Update Cur" sheet. Monthly I will change Cell "I2" to show the current month to be updated.
-Does the data in the "Update Cur" sheet actually start with the headers in row 5 and the other 3 sheets with the headers in row 4? The header on the "Update Cur" sheet should be in R4, sorry about that.

THANKS MUCH FOR HELPING!

Kthom019 · May 11th 2019

Thank you. See the attachment.

Kthom019 · May 11th 2019

Okay, How do I attached a copy of the file? The attachment only refers to URL links.

Kthom019 · May 10th 2019

How can I write a VBA script to copy data from a column and paste it into another worksheet based on a criteria in a cell?

Example: Assuming the table below = RowsA6 : F6. Cell C1 = the current month.
I would like to copy from another worksheet, which looks like the one below, actual data for only column B (Apr). Monthly, I would change Cell C1 to read the current month.
And of course copy only data for that current month. So, in May, I want only need to update column C, with Actual for May and so on and so forth.

[TABLE="border: 1, cellpadding: 1, width: 500"]

[tr]

[td][/td]

[td]

Apr

[/td]

[td][/td]

[/tr]

[tr]

[td]

Description

[/td]

[td]

Apr

[/td]

[td]

May

[/td]

[td]

Jun

[/td]

[td]

Jul

[/td]

[td]

Aug

[/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[/TABLE]

Posts by Kthom019

VBA script to copy data based on Cell criteria

VBA script to copy data based on Cell criteria

VBA script to copy data based on Cell criteria

VBA script to copy data based on Cell criteria

VBA script to copy data based on Cell criteria

VBA script to copy data based on Cell criteria

VBA script to copy data based on Cell criteria

VBA script to copy data based on Cell criteria

VBA script to copy data based on Cell criteria

VBA script to copy data based on Cell criteria

VBA script to copy data based on Cell criteria

VBA script to copy data based on Cell criteria

VBA script to copy data based on Cell criteria

VBA script to copy data based on Cell criteria

VBA script to copy data based on Cell criteria

VBA script to copy data based on Cell criteria

VBA script to copy data based on Cell criteria

VBA script to copy data based on Cell criteria

VBA script to copy data based on Cell criteria

VBA script to copy data based on Cell criteria