I can only run the macro when i am on the "Report" tab

[adnan1975]

Hi,
I can only run the following macro from "Report" worksheet. If I am on any other worksheet and try to run the macro it gives me error. But it works find on "Report". Since my date references are from another worksheet it just super annoying that I have to move to "Report" sheet every time to run this code. I cant seem to figure out what is wrong with my code. Also is there a way to shorten the code. all am trying to do is to copy dates from one column to another column and based on month set it to 1st date of the month.

any help is much appreciated.

Thanks

Dim rp As Worksheet
Dim data As Worksheet
Dim data2 As Worksheet
Dim sDate As Date
Dim Date1 As Date
Dim Date2 As Date
Dim Date3 As Date
Dim Date4 As Date
Dim Date5 As Date
Dim Date6 As Date
Dim i As Integer, x As Integer


Set rp = ThisWorkbook.Sheets("Report")
Set data = ThisWorkbook.Sheets("data")


LR = rp.Cells(Rows.Count, 1).End(xlUp).Row
'lrd.rp.Cells(Rows.Count, 46).End(xlUp).Row


rp.Range("AQ3:AR4000").ClearContents
rp.Range("AQ3:AR4000").ClearFormats

rp.Range("AQ3:AQ" & LR).Name = "MOCOP"
rp.Range("AR3:AR" & LR).Name = "MOCCL"

'Start Date
sDate = data.Range("L2").Value
Date1 = data.Range("B27").Value
Date2 = data.Range("B28").Value
Date3 = data.Range("B29").Value
Date4 = data.Range("B30").Value
Date5 = data.Range("B31").Value
Date6 = data.Range("B32").Value
'End Date
eDate = data.Cells(2, 4)


Application.ScreenUpdating = False


'///////////////////////////////Monthly Open MOCs/////////////////////////////////
For x = 3 To LR

If (rp.Cells(x, 17) <= sDate And rp.Cells(x, 17) >= Date1) And rp.Cells(x, 34) = "MOC" Then
    rp.Cells(x, 17).Copy
        rp.Cells(x, 43).PasteSpecial xlPasteValues
'rp.Cells(x, 43).Value = WorksheetFunction.EoMonth(Cells(x, 43), 0)
        rp.Cells(x, 43).Value = DateSerial(Year(Cells(x, 43)), Month(Cells(x, 43)), 1)

ElseIf (rp.Cells(x, 17) < Date1 And rp.Cells(x, 17) > Date2) And rp.Cells(x, 34) = "MOC" Then
    rp.Cells(x, 17).Copy
        rp.Cells(x, 43).PasteSpecial xlPasteValues
    rp.Cells(x, 43).Value = DateSerial(Year(Cells(x, 43)), Month(Cells(x, 43)), 1)

ElseIf (rp.Cells(x, 17) < Date2 And rp.Cells(x, 17) > Date3) And rp.Cells(x, 34) = "MOC" Then
    rp.Cells(x, 17).Copy
        rp.Cells(x, 43).PasteSpecial xlPasteValues
    rp.Cells(x, 43).Value = DateSerial(Year(Cells(x, 43)), Month(Cells(x, 43)), 1)

ElseIf (rp.Cells(x, 17) < Date3 And rp.Cells(x, 17) > Date4) And rp.Cells(x, 34) = "MOC" Then
    rp.Cells(x, 17).Copy
        rp.Cells(x, 43).PasteSpecial xlPasteValues
    rp.Cells(x, 43).Value = DateSerial(Year(Cells(x, 43)), Month(Cells(x, 43)), 1)

ElseIf (rp.Cells(x, 17) < Date4 And rp.Cells(x, 17) > Date5) And rp.Cells(x, 34) = "MOC" Then
    rp.Cells(x, 17).Copy
        rp.Cells(x, 43).PasteSpecial xlPasteValues
    rp.Cells(x, 43).Value = DateSerial(Year(Cells(x, 43)), Month(Cells(x, 43)), 1)

ElseIf (rp.Cells(x, 17) < Date5 And rp.Cells(x, 17) > Date6) And rp.Cells(x, 34) = "MOC" Then
    rp.Cells(x, 17).Copy
        rp.Cells(x, 43).PasteSpecial xlPasteValues
    rp.Cells(x, 43).Value = DateSerial(Year(Cells(x, 43)), Month(Cells(x, 43)), 1)
End If
Next x

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Monthly Close MOCs~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

For x = 3 To LR

If rp.Cells(x, 31) <= sDate And rp.Cells(x, 31) >= Date1 And rp.Cells(x, 34) = "CLMOC" Then
rp.Cells(x, 31).Copy
    rp.Cells(x, 44).PasteSpecial xlPasteValues
rp.Cells(x, 44).Value = DateSerial(Year(Cells(x, 44)), Month(Cells(x, 44)), 1)


ElseIf rp.Cells(x, 31) < Date1 And rp.Cells(x, 31) > Date2 And rp.Cells(x, 34) = "CLMOC" Then
rp.Cells(x, 31).Copy
    rp.Cells(x, 44).PasteSpecial xlPasteValues

rp.Cells(x, 44).Value = DateSerial(Year(Cells(x, 44)), Month(Cells(x, 44)), 1)


ElseIf rp.Cells(x, 31) < Date2 And rp.Cells(x, 31) > Date3 And rp.Cells(x, 34) = "CLMOC" Then
rp.Cells(x, 31).Copy
    rp.Cells(x, 44).PasteSpecial xlPasteValues
rp.Cells(x, 44).Value = DateSerial(Year(Cells(x, 44)), Month(Cells(x, 44)), 1)



ElseIf rp.Cells(x, 31) < Date3 And rp.Cells(x, 31) > Date4 And rp.Cells(x, 34) = "CLMOC" Then
rp.Cells(x, 31).Copy
 rp.Cells(x, 44).PasteSpecial xlPasteValues
rp.Cells(x, 44).Value = DateSerial(Year(Cells(x, 44)), Month(Cells(x, 44)), 1)



ElseIf rp.Cells(x, 31) < Date4 And rp.Cells(x, 31) > Date5 And rp.Cells(x, 34) = "CLMOC" Then
rp.Cells(x, 31).Copy
    rp.Cells(x, 44).PasteSpecial xlPasteValues
rp.Cells(x, 44).Value = DateSerial(Year(Cells(x, 44)), Month(Cells(x, 44)), 1)



ElseIf rp.Cells(x, 31) < Date5 And rp.Cells(x, 31) > Date6 And rp.Cells(x, 34) = "CLMOC" Then
rp.Cells(x, 31).Copy
    rp.Cells(x, 44).PasteSpecial xlPasteValues
rp.Cells(x, 44).Value = DateSerial(Year(Cells(x, 44)), Month(Cells(x, 44)), 1)


End If
Next x

 

[RoryA]

You need to specify the worksheet for the Cells calls in the DateSerial parts. For example:

DateSerial(Year(rp.Cells(x, 43)), Month(rp.Cells(x, 43)), 1)

 

[adnan1975]

Thanks RoryA. it worked.
much appreciated.

 

[RoryA]

Glad to help.