run time error 1004 - method range of object worksheet failed.

Dear Friends,

Below is the VBA codes where i tried in my excel. I am new to this. Need help from your team.
Please look at the mcopy, where i am trying to copy the cells which is listed below..
but upto q11 my vba code is working. after that even one cell not accepting
I need to write many cells like this
PLEASE support.





Sub MISOTTINPUT()
'

' MISOTTINPUT Macro
Dim historyWks As Worksheet
Dim inputWks As Worksheet

Dim nextRow As Long
Dim oCol As Long

Dim myRng As Range
Dim myCopy As String
Dim myCell As Range


'cells to copy from Input sheet - some contain formulas
myCopy = "b6,C6,d6,e6,f6,g6,h6,i6,j6,k6,l6,m6,n6,o6,p6,q6,R6,S6,b7,C7,d7,e7,f7,g7,h7,i7,j7,k7,l7,m7,n7,o7,p7,q7,R7,S7,b8,C8,d8,e8,f8,g8,h8,i8,j8,k8,l8,m8,n8,o8,p8,q8,r8,s8,m10,n10,o10,p10,q10,R10,S10,b11,C11,d11,e11,f11,g11,h11,i11,j11,k11,l11,m11,n11,o11,p11,q11,r11"


Set inputWks = Worksheets("Sheet2")
Set historyWks = Worksheets("MISOUTWARD")

With historyWks
nextRow = .Cells(.Rows.Count, "E").End(xlUp).Offset(1, 0).Row
End With


With inputWks
Set myRng = .Range(myCopy)


If Application.CountA(myRng) <> myRng.Cells.Count Then
MsgBox "Hey you must write all the cells!"
Exit Sub
End If
End With



With historyWks
With .Cells(nextRow, "C")
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
.Cells(nextRow, "kk").Value = Application.UserName
oCol = 4
For Each myCell In myRng.Cells
historyWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With



' Sub Send_Range_Or_Whole_Worksheet_with_MailEnvelope()
'Working in Excel 2002-2013
Dim AWorksheet As Worksheet
Dim Sendrng As Range
Dim rng As Range

On Error GoTo StopMacro

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Fill in the Worksheet/range you want to mail
'Note: if you use one cell it will send the whole worksheet
Set Sendrng = Worksheets("Sheet2").Range("A1:I27")

'Remember the activesheet
Set AWorksheet = ActiveSheet

With Sendrng

' Select the worksheet with the range you want to send
.Parent.Select

'Remember the ActiveCell on that worksheet
Set rng = ActiveCell

'Select the range you want to mail
.Select

' Create the mail and send it
ActiveWorkbook.EnvelopeVisible = True
With .Parent.MailEnvelope

' Set the optional introduction field thats adds
' some header text to the email body.
.Introduction = "These are the MIS recorded for the Outward Team"

With .Item

.To = "anuwers@gmail.com"

.Subject = "Daily MIS of Number of TT's Processed and the Internal Errors"
.Send
End With

End With

'select the original ActiveCell
rng.Select
End With

'Activate the sheet that was active before you run the macro
AWorksheet.Select

StopMacro:
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
ActiveWorkbook.EnvelopeVisible = False

'clear input cells that contain constants
With inputWks
On Error Resume Next
With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.GoTo .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With

'clear input cells that contain constants
With inputWks
On Error Resume Next
With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.GoTo .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With

Sheets("Sheet2").Select
Range("C4").Select
Range(Selection, Selection.End(xlDown)).Select


End Sub

Deleted...posted twice

I've made some slight changes to your code, see if it works for you..

Sub MISOTTINPUT()
 '

 ' MISOTTINPUT Macro
 Dim historyWks As Worksheet
 Dim inputWks As Worksheet

 Dim nextRow As Long
 Dim oCol As Long

 Dim myRng As Range
 Dim myCopy As Range
 Dim myCell As Range


 'cells to copy from Input sheet - some contain formulas
Set myCopy = Sheets("Sheet1").Range("B6:R11")


 Set inputWks = Worksheets("Sheet2")
 Set historyWks = Worksheets("MISUOTWARD")

 With historyWks
 nextRow = .Cells(.Rows.Count, "E").End(xlUp).Offset(1, 0).Row
 End With


 With inputWks
 Set myRng = myCopy


 If Application.CountA(myRng) <> myRng.Cells.Count Then
 MsgBox "Hey you must write all the cells!"
 Exit Sub
 End If
 End With



 With historyWks
 With .Cells(nextRow, "C")
 .Value = Now
 .NumberFormat = "mm/dd/yyyy hh:mm:ss"
 End With
 .Cells(nextRow, "kk").Value = Application.UserName
 oCol = 4
 For Each myCell In myRng.Cells
 historyWks.Cells(nextRow, oCol).Value = myCell.Value
 oCol = oCol + 1
 Next myCell
 End With



 ' Sub Send_Range_Or_Whole_Worksheet_with_MailEnvelope()
 'Working in Excel 2002-2013
 Dim AWorksheet As Worksheet
 Dim Sendrng As Range
 Dim rng As Range

 On Error GoTo StopMacro

 With Application
 .ScreenUpdating = False
 .EnableEvents = False
 End With

 'Fill in the Worksheet/range you want to mail
 'Note: if you use one cell it will send the whole worksheet
 Set Sendrng = Worksheets("Sheet2").Range("A1:I27")

 'Remember the activesheet
 Set AWorksheet = ActiveSheet

 With Sendrng

 ' Select the worksheet with the range you want to send
 .Parent.Select

 'Remember the ActiveCell on that worksheet
 Set rng = ActiveCell

 'Select the range you want to mail
 .Select

 ' Create the mail and send it
 ActiveWorkbook.EnvelopeVisible = True
 With .Parent.MailEnvelope

 ' Set the optional introduction field thats adds
 ' some header text to the email body.
 .Introduction = "These are the MIS recorded for the Outward Team"

 '.To = "DIBUAEGBOOutwardRemittance@dib.ae"
 ' .CC = "Yousef.Hindash@dib.ae"

 With .Item
 ' .To = "DIBUAEGBOOutwardRemittance@dib.ae"
 .To = "manwar.iqbal@dib.ae"
 ' .CC = "Yousef.Hindash@dib.ae"
 ' .BCC = "manwar.iqbal@dib.ae"
 ' .Subject = "TEST EMAIL - PLEASE IGNORE"
 .Subject = "Daily MIS of Number of TT's Processed and the Internal Errors"
 .Send
 End With

 End With

 'select the original ActiveCell
 rng.Select
 End With

 'Activate the sheet that was active before you run the macro
 AWorksheet.Select

StopMacro:
 With Application
 .ScreenUpdating = True
 .EnableEvents = True
 End With
 ActiveWorkbook.EnvelopeVisible = False

 'clear input cells that contain constants
 With inputWks
 On Error Resume Next
 With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
 .ClearContents
 Application.GoTo .Cells(1) ', Scroll:=True
 End With
 On Error GoTo 0
 End With

 'clear input cells that contain constants
 With inputWks
 On Error Resume Next
 With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
 .ClearContents
 Application.GoTo .Cells(1) ', Scroll:=True
 End With
 On Error GoTo 0
 End With

 Sheets("Sheet2").Range("C4").Select
 'Range("C4").Select
 Range(Selection, Selection.End(xlDown)).Select


 End Sub

I wasn't sure where your mycopy range was so I just did it on sheet1

Add this after your copy paste line

Sheets("Sheet1").Range("B6:R11").Clear

This might be a better option

Sheets("Sheet1").Range("B6:R11").Value = ""

See if this works

Set myCopy = Sheets("Sheet1").Range("B6:S18,B18:B30")

Its not letting me post the formula to your thread

In the attached is the formula in cell G1 and G2. See if it works
anuswers formula.xlsx