Run type error '424' object required

Question

Here is my code . 

Sub Prod() ActiveWorkbook.Sheets("UserInput").Activate ' a sheet where date picker and db/server names are taken from user Set con = New ADODB.Connection Set rs = New ADODB.Recordset Dim slctqry As String Dim dealdate As String Dim today As String Dim msg As String msg = "Sorry, for this date data is yet to come" today = Range("B2").Value 'B2 cell has today() function today = Format(today, "yyyy-mm-dd") dealdate = Range("B1").Value 'date picker is linked to this cell dealdate = Format(dealdate, "yyyy-mm-dd") con.ConnectionString = "Provider=SQLOLEDB;Data Source=sql123abce\sql01;Initial Catalog=sqldb;User ID=abcd;Password=Windows;Integrated Security=SSPI" con.Open If (dealdate > today) Then MsgBox msg ElseIf (dealdate = today) Then slctqry = "select Number,Premium, TransactionID, money from traders(nolock)" slctqry = slctqry & " where convert(date,tradedate,103)='" & dealdate & "'" Set rs.ActiveConnection = con rs.Open slctqry ActiveWorkbok.Sheets("Prod").Activate ' prod named worksheet where data will be copied from SQL db Range("A2").CopyFromRecordset (rs) ElseIf (dealdate < today) Then slctqry = "select Number,Premium, TransactionID, money from tradersaudit(nolock)" slctqry = slctqry & " where convert(date,tradedate,103)='" & dealdate & "'" Set rs.ActiveConnection = con rs.Open slctqry 'Dim ws4 As Worksheet ActiveWorkbook.Sheets("Prod").Activate Range("A2").CopyFromRecordset (rs) End If con.Close End Sub

i'm getting this error at ActiveWorkbook.Sheets("Prod").Activate . i don't know why its happening today as the same code was working fine till yesterday. :(

Please help if anyboday have the solution

Answer 1

Hi,

you might need to define the activeworkbook

in the beginning......

Sub Prod()

Dim  myWb As Workbook

Set myWb = ThisWorkbook

...............