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

adnan1975

New Member
Joined
Aug 24, 2017
Messages
25
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


VBA Code:
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,709
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You need to specify the worksheet for the Cells calls in the DateSerial parts. For example:

Rich (BB code):
DateSerial(Year(rp.Cells(x, 43)), Month(rp.Cells(x, 43)), 1)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,709
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Glad to help. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,795
Messages
5,638,379
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top