Snasui Excel Forum

Code: Select all

   Sheets("PL_Dept_MTD_03").Select
    Sheets("PL_Dept_MTD_" & format$(date-1, "mm").Select

Code: Select all

Sub Run_03()
'
' Run_03 Macro
'

'
    Sheets("PL_YTD").Select
    ActiveWindow.SmallScroll Down:=-114
    Range("G76").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Main").Select
    Range("E6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("J9").Select
End Sub

Code: Select all

Sub Run_03()
Dim n As Long
Dim i As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Sheets(1)
Set ws2 = Sheets(2)
On Error Resume Next
For n = 5 To 16
If Month(ws2.Cells(2, n)) = Month(Date) Then
For i = 6 To ws1.Range("a" & ws1.Rows.Count).End(xlUp).Row
    ws1.Cells(i, 5).Value = Application.WorksheetFunction.Index(ws2.Range("c3:p300"), _
    Application.WorksheetFunction.Match(ws1.Cells(i, 1), ws2.Range("c3:c300"), 0), _
    Application.WorksheetFunction.Match(ws1.Cells(4, 5), ws2.Range("e2:p2"), 0))
Next i
End If
Next n
End Sub

Code: Select all

Sub Run_03()
Dim n As Long
Dim i As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Sheets(1)
Set ws2 = Sheets(2)
On Error Resume Next
With Application.WorksheetFunction
For n = 5 To 16
If Month(ws2.Cells(2, n)) = Month(ws1.Cells(2, 15)) Then
For i = 6 To ws1.Range("a" & ws1.Rows.Count).End(xlUp).Row
    ws1.Cells(i, n - 2).Value = .Index(ws2.Range("e3:p300"), _
    .Match(ws1.Cells(i, 1), ws2.Range("c3:c300"), 0), _
    .Match(ws1.Cells(4, Month(ws1.Cells(2, 15)) + 2), ws2.Range("e2:p2"), 0))
Next i
End If
Next n
End With
End Sub