
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:
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
Bookmarks