Enter Date After copy filtered data

[elsg]

I need copy data sheet Org to sheet Dest, then go to sheet Dest and enter with date in last cell column K and L.

look my manul result in sheet Dest, blue color is that i try do.

[elsg]

Someone understand my question?

[dxider]

So, what you want to do is:

Enter data on sheet 1.
Click on the Adicionar button.
Then excel has to copy only new data into Sheet 2
An then add the date when the copy was done to Column K

Is that right?

[elsg]

Sorry, aneed vba rotine

[dxider]

Well, I was trying to clarify your request, but ok, good luck with that.

[elsg]

Hye dxider (http://www.vbaexpress.com/forum/member.php?54376-dxider) , sorry my english fool, maybe i understand you answer.

I try use code after copy data to another sheet insert date in column K

.Range("K7").Value = Date
.Range("L7").Value = "No"

[dxider]

And where does the "Date" value comes from?
If you want the current date, you can use the object Now of the DateTime object, something like this:


.Range("K7").Value = Now
.Range("L7").Value = "No"

And just add a few formatting to the cell to avoid the full date specification.

[elsg]

my proble is insert below last cell in column K without chage last data above rows

[dxider]

Well, I'm sure I can help you, but so far, I have not fully understand what you need :think:

Maybe if you explain it step by step, I can have an idea of what you need. :thumb

[elsg]

My code filter and copy to another sheet, after i need enter date and text "NO" in another sheet.

[SamT]

And where does the "Date" value comes from?
If you want the current date, you can use the object Now of the DateTime object, something like this:


.Range("K7").Value = Now
.Range("L7").Value = "No"

And just add a few formatting to the cell to avoid the full date specification.

Date and Now are both VBA functions. Date returns System Date as a Date. Now returns the Serial value of System Date + System Time as a Double.

[elsg]

my problem is enter date and text in last cell another sheet, if you test may code you know is wrong

[SamT]

Dois métodos

With wsDest
Lr2 = .Cells(Rows.Count, "J").End(xlUp).Row
.Range("K" & Lr).Value = Date
.Cells(Lr2, "L").Value = "No"
End With
Translations by http://mymemory.translated.net/

[elsg]

Sorry, your code not show expected result, see my picture

[SamT]

Show me the macro code that make the wrong result

[elsg]

Look

Option Explicit

Sub FilterAndCopy()
Dim wsDest As Worksheet, wsOrg As Worksheet
Dim Lr, Lr2 As Long

Set wsOrg = ThisWorkbook.Worksheets("Orig")
Set wsDest = ThisWorkbook.Worksheets("Dest")
Application.ScreenUpdating = 0
With wsOrg.Range("A6").CurrentRegion
Lr = wsDest.Cells(Rows.Count, "J").End(xlUp).Row
.AutoFilter field:=10, Criteria1:="Yes"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy Destination:=wsDest.Cells(.Rows.Count, "A").End(xlUp).Offset(1)
.Offset(1, 0).SpecialCells(xlCellTypeVisible).Delete shift:=xlUp
.AutoFilter
With wsDest
Lr2 = .Cells(Rows.Count, "J").End(xlUp).Row
.Range("K" & Lr).Value = Date
.Cells(Lr2, "L").Value = "No"
End With

End With
Application.ScreenUpdating = 1
End Sub

[SamT]

.Range("K" & Lr2).Value = Date

[elsg]

Now 1 row is filled but another row no.

[SamT]

That is good. Yes?

[elsg]

No, code need fill all rows copied (blue need write date in 2 rows) and (green need write date in 2 rows) and (pink need write date in 3 rows).

[SamT]

With wsDest
Lr2 = .Cells(Rows.Count, "J").End(xlUp).Row
Do While LR =< LR2
.Range("K" & Lr).Value = Date
.Cells(Lr, "L").Value = "No"
Lr = Lr + 1
Loop
End With

[elsg]

You code work, but change before dates.

[SamT]

I don't understand

[elsg]

you code change date.

look last blue (two rows), result is 20/09/2015 <-(dd/MM/yyyy).

When you code run, change dates before in secuence (08..09..10..11..12..13/09/2015)

[SamT]

Show me your code with my code.

[elsg]

This my code

Option Explicit

Sub FilterAndCopy()
Dim wsDest As Worksheet, wsOrg As Worksheet
Dim Lr, Lr2 As Long


Set wsOrg = ThisWorkbook.Worksheets("Org")
Set wsDest = ThisWorkbook.Worksheets("Dest")
Application.ScreenUpdating = 0
With wsOrg.Range("A6").CurrentRegion
Lr = wsDest.Cells(Rows.Count, "J").End(xlUp).Row
.AutoFilter field:=10, Criteria1:="Yes"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy Destination:=wsDest.Cells(.Rows.Count, "A").End(xlUp).Offset(1)
.Offset(1, 0).SpecialCells(xlCellTypeVisible).Delete shift:=xlUp
.AutoFilter
With wsDest
Lr2 = .Cells(Rows.Count, "J").End(xlUp).Row
Do While Lr <= Lr2
.Range("K" & Lr).Value = Date
.Cells(Lr, "L").Value = "No"
Lr = Lr + 1
Loop
End With
End With
wsDest.Activate
wsDest.Range("K7:L7").AutoFill Destination:=Range("K7:L" & Lr2), Type:=xlFillDefault
wsOrg.Select
Application.ScreenUpdating = 1
End Sub

[SamT]

Your code is AutoFilling the Date and "No" columns. This changes the Date column to a Series of Dates incrementing by 1.

Sub FilterAndCopy()
Dim wsDest As Worksheet, wsOrg As Worksheet
Dim Lr, Lr2 As Long

Set wsOrg = ThisWorkbook.Worksheets("Org")
Set wsDest = ThisWorkbook.Worksheets("Dest")
Application.ScreenUpdating = 0

With wsOrg.Range("A6").CurrentRegion
Lr = wsDest.Cells(Rows.Count, "J").End(xlUp).Row
.AutoFilter field:=10, Criteria1:="Yes"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy Destination:=wsDest.Cells(.Rows.Count, "A").End(xlUp).Offset(1)
.Offset(1, 0).SpecialCells(xlCellTypeVisible).Delete shift:=xlUp
.AutoFilter

With wsDest
Lr2 = .Cells(Rows.Count, "J").End(xlUp).Row
Do While Lr <= Lr2
.Range("K" & Lr).Value = Date
.Cells(Lr, "L").Value = "No"
Lr = Lr + 1
Loop
End With

End With
wsOrg.Select
Application.ScreenUpdating = 1
End Sub

[elsg]

Your code is AutoFilling the Date and "No" columns. This changes the Date column to a Series of Dates incrementing by 1.yes

how evoid this incremental date?

[snb]

Sub M_snb()
y=ThisWorkbook.sheets("Dest").Cells(Rows.Count, 1).End(xlUp).Offset(1).row

with ThisWorkbook.sheets("Org").Range("A6").CurrentRegion
.AutoFilter 10, "Yes"
.Offset(1).Copy ThisWorkbook.sheets("Dest").Cells(y, 1)
.Offset(1).Delete
.AutoFilter
end with

With ThisWorkbook.sheets("Dest").Cells(Rows.Count, 1).End(xlUp).Offset(1)
.offset(.row-y,10).resize(.row-y)=Date
.offset(.row-y,11).resize(.row-y)="No"
end with
End Sub

[SamT]

Your code is AutoFilling the Date and "No" columns. This changes the Date column to a Series of Dates incrementing by 1.yes

how evoid this incremental date?
the last code i gave you. Either snb code

[elsg]

Sam, you code work very well, thank very much!!!

snb, your code offset date and "no"

But sam code solve question, thank you both!