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(MonthNa me & 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.Coun t, "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.Coun t, "C").End(xlUp).Row + 1
Range("A & lnglastrow").Select
ActiveSheet.Paste
End If
End
End Sub
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(MonthNa
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.Coun
Worksheets(2).Range ("A & Lnglastrow")
Selection.EntireRow.Paste
Else
Worksheets(1).Range("G & i").Activate.Select.Copy
Worksheets(3).Activate
lngLastRow = Worksheets.Cells(Rows.Coun
Range("A & lnglastrow").Select
ActiveSheet.Paste
End If
End
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
To remove the error replace
with
However, attach your workbook because the code could be improved.
Valid = Now(FormatDateTime(MonthName & Year))
with
Valid = Format(Date, "mmmm yyyy")
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
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
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.