Link to home
Start Free TrialLog in
Avatar of Kamran Almelkar
Kamran Almelkar

asked on

Copy and Paste data to other sheet after Validation But when i excecute the code i get Argument not optional Error

We have a data which has expiry date which is in Month and Year format in say Column K.  so I want to validate the value in the column K with todays date ( i.e Month and Year format) and if it matches then I want to copy the whole active row data to other sheet.

if the value does not match, then I want to copy only the value of the Cell  G column Same row to other sheet to a specific column.

below is the code id have written But when i excecute the code i get Argument not optional Error



Sub Rentfree()

Dim Valid As Date 'Variable to Hold the Today date
Dim irow As Integer 'Variable to Hold the Row count or Cell number
Dim Val As Date 'Variable to hold the value data sheet active cell
Dim i As Integer ' Counter Variable
Dim lngLastRow As Long 'Variable to hold the Last row address



i = 2
Valid = Now(FormatDateTime(MonthName & Year))

Worksheets(1).Range("K & i").Activate

While ActiveCell.Value <> ""

Val = Worksheets(1).Range("K & i").Value

If Val = Valid Then
Selection.EntireRow.Select
Selection.EntireRow.Copy
Worksheets(2).Activate
lngLastRow = Worksheets.Cells(Rows.Count, "A").End(xlUp).Row + 1
Worksheets(2).Range ("A & Lnglastrow")
Selection.EntireRow.Paste

Else
Worksheets(1).Range("G & i").Activate.Select.Copy
Worksheets(3).Activate
lngLastRow = Worksheets.Cells(Rows.Count, "C").End(xlUp).Row + 1
Range("A & lnglastrow").Select
ActiveSheet.Paste
End If
End
End Sub
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

can you attach the workbook.

Also, to help us read and copy your posted code, in future please select the code after posting and click  CODE in the post menu.
ASKER CERTIFIED SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
To remove the error replace

Valid = Now(FormatDateTime(MonthName & Year))

Open in new window


with

Valid = Format(Date, "mmmm yyyy")

Open in new window


However,  attach your workbook because the code could be improved.
My suggestion prevents the error!
No comment has been added to this question in more than 14 days, so it is now classified as abandoned.

If you feel this question should be closed differently, post an objection and a moderator will read all objections and then close it as they feel fit. If no one objects, this question will be closed automatically the way described above