Hi all!
Below is code that works, but I'm sure there is a way to shorten/simplify it. It's basically the same code over and over again for each line I'm copying over. Any thoughts on how to accomplish the same thing with less code?
Thanks so much!
Below is code that works, but I'm sure there is a way to shorten/simplify it. It's basically the same code over and over again for each line I'm copying over. Any thoughts on how to accomplish the same thing with less code?
Thanks so much!
VBA Code:
Sub Monthly_CP()
Dim wb1 As Workbook
Dim fm
With Workbooks("SLMR Master - All Regions").Sheets("Main")
Set wb1 = Workbooks("Service Level Miss " & .Range("k2") & " MTD " & .Range("e2"))
fm = Application.Match(.Range("E14"), wb1.Sheets("MTD performance").Range("A4:A40"), 0)
If IsNumeric(fm) Then
wb1.Sheets("MTD Performance").Range("B" & fm + 3 & ":C" & fm + 3).Value = .Range("F14:G14").Value
End If
fm = Application.Match(.Range("E17"), wb1.Sheets("MTD performance").Range("A4:A40"), 0)
If IsNumeric(fm) Then
wb1.Sheets("MTD Performance").Range("B" & fm + 3).Value = .Range("F17").Value
End If
fm = Application.Match(.Range("I19"), wb1.Sheets("MTD performance").Range("A4:A40"), 0)
If IsNumeric(fm) Then
wb1.Sheets("MTD Performance").Range("B" & fm + 3 & ":C" & fm + 3).Value = .Range("F19:G38").Value
End If
fm = Application.Match(.Range("I20"), wb1.Sheets("MTD performance").Range("A4:A40"), 0)
If IsNumeric(fm) Then
wb1.Sheets("MTD Performance").Range("B" & fm + 3 & ":C" & fm + 3).Value = .Range("F20:G20").Value
End If
fm = Application.Match(.Range("I21"), wb1.Sheets("MTD performance").Range("A4:A40"), 0)
If IsNumeric(fm) Then
wb1.Sheets("MTD Performance").Range("B" & fm + 3 & ":C" & fm + 3).Value = .Range("F21:G21").Value
End If
fm = Application.Match(.Range("I22"), wb1.Sheets("MTD performance").Range("A4:A40"), 0)
If IsNumeric(fm) Then
wb1.Sheets("MTD Performance").Range("B" & fm + 3 & ":C" & fm + 3).Value = .Range("F22:G22").Value
End If
fm = Application.Match(.Range("I23"), wb1.Sheets("MTD performance").Range("A4:A40"), 0)
If IsNumeric(fm) Then
wb1.Sheets("MTD Performance").Range("B" & fm + 3 & ":C" & fm + 3).Value = .Range("F23:G23").Value
End If
fm = Application.Match(.Range("I24"), wb1.Sheets("MTD performance").Range("A4:A40"), 0)
If IsNumeric(fm) Then
wb1.Sheets("MTD Performance").Range("B" & fm + 3 & ":C" & fm + 3).Value = .Range("F24:G24").Value
End If
fm = Application.Match(.Range("I26"), wb1.Sheets("MTD performance").Range("A4:A40"), 0)
If IsNumeric(fm) Then
wb1.Sheets("MTD Performance").Range("B" & fm + 3 & ":C" & fm + 3).Value = .Range("F26:G26").Value
End If
fm = Application.Match(.Range("I27"), wb1.Sheets("MTD performance").Range("A4:A40"), 0)
If IsNumeric(fm) Then
wb1.Sheets("MTD Performance").Range("B" & fm + 3 & ":C" & fm + 3).Value = .Range("F27:G27").Value
End If
fm = Application.Match(.Range("I28"), wb1.Sheets("MTD performance").Range("A4:A40"), 0)
If IsNumeric(fm) Then
wb1.Sheets("MTD Performance").Range("B" & fm + 3 & ":C" & fm + 3).Value = .Range("F28:G28").Value
End If
fm = Application.Match(.Range("I29"), wb1.Sheets("MTD performance").Range("A4:A40"), 0)
If IsNumeric(fm) Then
wb1.Sheets("MTD Performance").Range("B" & fm + 3 & ":C" & fm + 3).Value = .Range("F29:G29").Value
End If
fm = Application.Match(.Range("I30"), wb1.Sheets("MTD performance").Range("A4:A40"), 0)
If IsNumeric(fm) Then
wb1.Sheets("MTD Performance").Range("B" & fm + 3 & ":C" & fm + 3).Value = .Range("F30:G30").Value
End If
fm = Application.Match(.Range("I32"), wb1.Sheets("MTD performance").Range("A4:A40"), 0)
If IsNumeric(fm) Then
wb1.Sheets("MTD Performance").Range("B" & fm + 3 & ":C" & fm + 3).Value = .Range("F32:G32").Value
End If
fm = Application.Match(.Range("I33"), wb1.Sheets("MTD performance").Range("A4:A40"), 0)
If IsNumeric(fm) Then
wb1.Sheets("MTD Performance").Range("B" & fm + 3 & ":C" & fm + 3).Value = .Range("F33:G33").Value
End If
fm = Application.Match(.Range("I34"), wb1.Sheets("MTD performance").Range("A4:A40"), 0)
If IsNumeric(fm) Then
wb1.Sheets("MTD Performance").Range("B" & fm + 3 & ":C" & fm + 3).Value = .Range("F34:G34").Value
End If
fm = Application.Match(.Range("I36"), wb1.Sheets("MTD performance").Range("A4:A40"), 0)
If IsNumeric(fm) Then
wb1.Sheets("MTD Performance").Range("B" & fm + 3 & ":C" & fm + 3).Value = .Range("F36:G36").Value
End If
fm = Application.Match(.Range("I37"), wb1.Sheets("MTD performance").Range("A4:A40"), 0)
If IsNumeric(fm) Then
wb1.Sheets("MTD Performance").Range("B" & fm + 3 & ":C" & fm + 3).Value = .Range("F37:G37").Value
End If
fm = Application.Match(.Range("I38"), wb1.Sheets("MTD performance").Range("A4:A40"), 0)
If IsNumeric(fm) Then
wb1.Sheets("MTD Performance").Range("B" & fm + 3 & ":C" & fm + 3).Value = .Range("F38:G38").Value
End If
End With
End Sub