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

thanks gmr4evr1. now its working with the change.

I have one more issue. please help me in that as well

from the r sheet1, i need to clear the cells which is in b6:r11 - after copy paste.

Dear gmr4evr1

Now i required to add more cells to copy. i.e. i need to copy the cells b6:s18 and b18:b30. Need with two different range to copy. Please suggest

Code given by you is as below.
Set myCopy = Sheets("Sheet1").Range("B6:R11")

thanks. it is working.. now another one very basic formula required

i have pasted the information below
A B C D E F Result h Colum Result i column Q
1 50000 maggie andrew sunil andrew maggie
2 1000000 baby sugar mani sugar stews
3 2000000 maggie sugar sunil sugar cars
4 500000 toys chika tikka chika geo
5 8000000 hakka gabriel mento gabriel toys


My result columns are h and i
If A column is greater than 200000 and if c CELL item is not matching with the p column listed items, then get the result E column
If A column is greater than 200000 and if c CELL item is matching with the p column listed items, then get the result on I column

Dear gmr4evr1

Below is the vba code where i copied and trying to have in excel with small changes of cell details and sheet details - Everything is working fine. The Change i am looking on the cells below.

In below cells i have to write some data, otherwise my code will ask " hey you must write all the cells". What i required is, this cells should not be must in asking data when i run macro, it can be empty also in sheet2 in the below said cells.
.
also this empty cells should be copied in MISOUTWARD sheet

B6:S18,B23:S30,AR15:AR27,AR34:AR41

.
VBA code - copied for your reference - 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 Range
Dim myCopy1 As Range
Dim myCell As Range



'cells to copy from Input sheet - some contain formulas
Set myCopy = Sheets("Sheet2").Range("B6:S18,B23:S30,AR15:AR27,AR34:AR41")


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 = 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, "OM").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").Range("B6:q18").Value = ""
Sheets("Sheet2").Range("S6:S18").Value = ""




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


End Sub