Copy and paste based on Date and next empty row.

Hello,
I need help. I am trying to find an easier way to copy data by VBA coding. Here source sheet is “Yearly Sales”, and destination sheet is “P01”. Data will be paste column N-X and Row 14 (cell N14). Sheet “P01” contains Product “Rice”. Sheet Yearly Sales has data till now 10th of March. Here 3rd and 10th of March are “Holyday”. The date of 7th and 8th of March I have not sale any Rice. Here is problem that when I copy from source (yearly sales) to paste destination (“P01”) by clicking a Button, then I did not get Row for Holiday and No Sale with date. Look at destination sheet “P01”. But I want to same as Sheet “Sheet1”. My destination sheet always update every day (not holiday).
I attached file “621 Current Send.xlsm” for you. Also I attached 2 screenshots, “621 P01” is my present result which I don’t want. “621 Sheet1” result is which I want.
If I write manually “Holiday” and “no sale” with date, then next update overwrite on holiday’s and No sale’s row. But I do not want it.
What will be the right code? Can any one help me?
Your help is greatly appreciated as always!! thank you for your time!!!

Thanks for your prompt reply. Sorry to say I did not understand you some text.

Please, write it again. Thanks.

This text:
"cY¨ ev †mevi eY©bv (cÖ‡hvR¨ †˙‡Î eŞvŰ bvgmn)"
"Bmy¨i ZvwiL:"

I think, this is keyboard problem. My keyboard is Finnish.

Hi,
"cY¨ ev †mevi eY©bv (cÖ‡hvR¨ †˙‡Î eŞvŰ bvgmn)" this mean Product name and details.

Bmy¨i ZvwiL this mean Date of Issue.
Thanks.

Thanks for your advice. Maybe I did not make you understand that what is my problem and what I want. Advance filter does not work here. Because, source sheet’s row does not paste to destination’s sheet directly. Look at this, source sheet’s column “G” paste to destination sheet’s column “N”, source sheet’s column “I” paste to destination sheet’s column “O”, source sheet’s column “H” no need to copy, source sheet’s column “K” paste to destination sheet’s column “P”, here source sheet’s column “J” no need to copy etc., Source sheet “Yearly Sales” will be update every day from others source book, and same time destination sheet “P01” will be update every day. When I update destination sheet “P01” then “P01” update same as screenshot 1st one ( which I posted), but I want update’s result same as screenshot 2nd one ( which I posted). It will be happen to:
1st March 2021, I will update “P01”, get sales data Row 14 from source,
2nd March 2021, I will update “P01”, get sales data Row 15 from source,
3rd March 2021, I will update “P01”, that day is holiday, and I write manually “holiday” Row 16,
4th March 2021, I will update “P01”, get sales data Row 17 from source, (here all time data will paste to next empty Row).
5th March 2021 I will update “P01”, get sales data Row 18 from source,
6th March 2021, I will update “P01”, get sales data Row 19 from source,
7th March 2021, I will update “P01” manually “No sales” to write Row 20, because that day product “Rice” was not sold.
8th March 2021, same as 7th March, to write Row 21,
9th March 2021, I will update “P01”, get sales data Row 22 from source,
10th March 2021, I will update “P01”, that day is holiday, and I write manually “holiday” Row 23.
So, this way to continue the rest of days of the month.
How it is possible to make it or what is the write code for it? Please, see the “621 current send.xlsm” file. There is VBA code Sheet1 “P01”. Help me, please. Thanks.
Here is Code:

Please Login or Register  to view this content.

Dim LastRow As Long, erow As Long
Dim i As Variant
Dim Name As Variant
Dim StartDate, EndDate, ddate As Date
Dim bUpdateScreen As Boolean

bUpdateScreen = Application.ScreenUpdating
'Prevent Computer Screen from running
Application.ScreenUpdating = False

StartDate = DateValue(Range("M7").Value)
EndDate = DateValue(Range("N7").Value)
Name = "Yearly Sales"
'Here "1" mean column A
LastRow = Worksheets("Yearly Sales").Cells(Rows.Count, 1).End(xlUp).Row
erow = Worksheets("P01").Cells(Rows.Count, 1).End(xlUp).Row
For i = 5 To LastRow ' Source sheet from Row 5 start copy
ddate = Format(Worksheets(Name).Cells(i, 5).Value, "MM/DD/YYYY")
' This is the date filter.
If EndDate >= ddate And StartDate <= ddate Then
' This is the Product Filter.
If LCase(Worksheets(Name).Cells(i, 7).Value) = LCase(Range("J7").Value) Or LCase(Worksheets(Name).Cells(i, 7).Value) = "holiday" Then
Worksheets(Name).Cells(i, 7).Copy
erow = erow + 1
Worksheets(Name).Paste Destination:=Worksheets("P01").Cells(erow + 1, 14)
'*** here 9 is Source sheet's (yearly Sales) column I
Worksheets(Name).Cells(i, 9).Copy
'*** here 16 is Distination sheet's (P01) column P
Worksheets(Name).Paste Destination:=Worksheets("P01").Cells(erow + 1, 15)

Worksheets(Name).Cells(i, 11).Copy
Worksheets(Name).Paste Destination:=Worksheets("P01").Cells(erow + 1, 16)
Worksheets(Name).Cells(i, 13).Copy
Worksheets(Name).Paste Destination:=Worksheets("P01").Cells(erow + 1, 17)
Worksheets(Name).Cells(i, 15).Copy
Worksheets(Name).Paste Destination:=Worksheets("P01").Cells(erow + 1, 18)
Worksheets(Name).Cells(i, 1).Copy
Worksheets(Name).Paste Destination:=Worksheets("P01").Cells(erow + 1, 19)
Worksheets(Name).Cells(i, 3).Copy
Worksheets(Name).Paste Destination:=Worksheets("P01").Cells(erow + 1, 20)
Worksheets(Name).Cells(i, 2).Copy
Worksheets(Name).Paste Destination:=Worksheets("P01").Cells(erow + 1, 21)
Worksheets(Name).Cells(i, 4).Copy
Worksheets(Name).Paste Destination:=Worksheets("P01").Cells(erow + 1, 22)
Worksheets(Name).Cells(i, 5).Copy
Worksheets(Name).Paste Destination:=Worksheets("P01").Cells(erow + 1, 23)
End If
End If
Next i
'Allow Computer Screen to refresh (not necessary in most cases)
Application.ScreenUpdating = True

Originally Posted by mazadb

Hi,
"cY¨ ev †mevi eY©bv (cÖ‡hvR¨ †˙‡Î eŞvŰ bvgmn)" this mean Product name and details.

Bmy¨i ZvwiL this mean Date of Issue.
Thanks.

Originally Posted by mazadb

Thanks for your advice. Maybe I did not make you understand that what is my problem and what I want. Advance filter does not work here. Because, source sheet’s row does not paste to destination’s sheet directly. Look at this, source sheet’s column “G” paste to destination sheet’s column “N”, source sheet’s column “I” paste to destination sheet’s column “O”, source sheet’s column “H” no need to copy, source sheet’s column “K” paste to destination sheet’s column “P”, here source sheet’s column “J” no need to copy etc., Source sheet “Yearly Sales” will be update every day from others source book, and same time destination sheet “P01” will be update every day. When I update destination sheet “P01” then “P01” update same as screenshot 1st one ( which I posted), but I want update’s result same as screenshot 2nd one ( which I posted). It will be happen to:
1st March 2021, I will update “P01”, get sales data Row 14 from source,
2nd March 2021, I will update “P01”, get sales data Row 15 from source,
3rd March 2021, I will update “P01”, that day is holiday, and I write manually “holiday” Row 16,
4th March 2021, I will update “P01”, get sales data Row 17 from source, (here all time data will paste to next empty Row).
5th March 2021 I will update “P01”, get sales data Row 18 from source,
6th March 2021, I will update “P01”, get sales data Row 19 from source,
7th March 2021, I will update “P01” manually “No sales” to write Row 20, because that day product “Rice” was not sold.
8th March 2021, same as 7th March, to write Row 21,
9th March 2021, I will update “P01”, get sales data Row 22 from source,
10th March 2021, I will update “P01”, that day is holiday, and I write manually “holiday” Row 23.
So, this way to continue the rest of days of the month.
How it is possible to make it or what is the write code for it? Please, see the “621 current send.xlsm” file. There is VBA code Sheet1 “P01”. Help me, please. Thanks.
Here is Code:

Please Login or Register  to view this content.

Dim LastRow As Long, erow As Long
Dim i As Variant
Dim Name As Variant
Dim StartDate, EndDate, ddate As Date
Dim bUpdateScreen As Boolean

bUpdateScreen = Application.ScreenUpdating
'Prevent Computer Screen from running
Application.ScreenUpdating = False

StartDate = DateValue(Range("M7").Value)
EndDate = DateValue(Range("N7").Value)
Name = "Yearly Sales"
'Here "1" mean column A
LastRow = Worksheets("Yearly Sales").Cells(Rows.Count, 1).End(xlUp).Row
erow = Worksheets("P01").Cells(Rows.Count, 1).End(xlUp).Row
For i = 5 To LastRow ' Source sheet from Row 5 start copy
ddate = Format(Worksheets(Name).Cells(i, 5).Value, "MM/DD/YYYY")
' This is the date filter.
If EndDate >= ddate And StartDate <= ddate Then
' This is the Product Filter.
If LCase(Worksheets(Name).Cells(i, 7).Value) = LCase(Range("J7").Value) Or LCase(Worksheets(Name).Cells(i, 7).Value) = "holiday" Then
Worksheets(Name).Cells(i, 7).Copy
erow = erow + 1
Worksheets(Name).Paste Destination:=Worksheets("P01").Cells(erow + 1, 14)
'*** here 9 is Source sheet's (yearly Sales) column I
Worksheets(Name).Cells(i, 9).Copy
'*** here 16 is Distination sheet's (P01) column P
Worksheets(Name).Paste Destination:=Worksheets("P01").Cells(erow + 1, 15)

Worksheets(Name).Cells(i, 11).Copy
Worksheets(Name).Paste Destination:=Worksheets("P01").Cells(erow + 1, 16)
Worksheets(Name).Cells(i, 13).Copy
Worksheets(Name).Paste Destination:=Worksheets("P01").Cells(erow + 1, 17)
Worksheets(Name).Cells(i, 15).Copy
Worksheets(Name).Paste Destination:=Worksheets("P01").Cells(erow + 1, 18)
Worksheets(Name).Cells(i, 1).Copy
Worksheets(Name).Paste Destination:=Worksheets("P01").Cells(erow + 1, 19)
Worksheets(Name).Cells(i, 3).Copy
Worksheets(Name).Paste Destination:=Worksheets("P01").Cells(erow + 1, 20)
Worksheets(Name).Cells(i, 2).Copy
Worksheets(Name).Paste Destination:=Worksheets("P01").Cells(erow + 1, 21)
Worksheets(Name).Cells(i, 4).Copy
Worksheets(Name).Paste Destination:=Worksheets("P01").Cells(erow + 1, 22)
Worksheets(Name).Cells(i, 5).Copy
Worksheets(Name).Paste Destination:=Worksheets("P01").Cells(erow + 1, 23)
End If
End If
Next i
'Allow Computer Screen to refresh (not necessary in most cases)
Application.ScreenUpdating = True

Help me, please.