Copy / paste specfic DATE to other worksheet.

Solved/Closed
carman - Aug 13, 2010 at 03:59 AM
 carman - Aug 22, 2010 at 09:06 PM
Hello,
I've got a few columns of data in which 3 columns contain dates (in date format : dd/mm/yyyy) . Now, I want excel to seperate the entire row into another worksheet according to the YEAR in the 3 columns.

In cell ( 17,B) and (18,B), i type in the year for reference ( which i want excel to seperate the rows accoding to this year) . * The year is not in "absolute term" and so the macro needs to be capable to capture the value in cell (17,B) and (18,B).

For example::
If i typed in "2011" in cell ( 17,B) and "2012" in cell ( 18,B), i want excel to look through the 3 columns (I to K),copy the entire row if the years in ANY of the column ( I to K) contains year of 2011 and 2012 and paste them in sheet 2.

Please take a look at this reference file:
https://authentification.site/files/23779983/01.xls

In this file, i want to seperate the entire rows from column (E to M) in sheet 1 to sheet 2. And sheet 3 is approximately the final product i want. I need a VBA code for this....


Thanks! I couldn't find relavent information on google...

1 reply

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Aug 17, 2010 at 11:19 PM
You can add this formula
=IF(OR(AND(B$17<>"",OR(YEAR(I2)=B$17,YEAR(J2)=B$17,YEAR(K2)=B$17)),AND(B$18<>"",OR(YEAR(I2)=B$18,YEAR(J2)=B$18,YEAR(K2)=B$18))),1,0)

Filter on "1"

Copy the range


If you need macro, you can record your action
0
Thanks for your reply...your code works.
However, there is a new situation here. I now got more years that i need to do the filter and i want seperate sheet for each year.
So, instead of manually type in the data, i have used VBA to find the year range, put into one column and delete all duplicate ones. The task is similar, but since excel could give more than 20 years, it would be tendious to do all the filtering maunally. AND, now i need excel to seperate the rows that contain the specific year range in any one the three cells and put them into a new sheet.

e.g.
The years that excel could find in the three columns(F:H) are ( 2001,2003,2006,2010, 2012,2020.....2033).. and they are pasted in column "S" in sheet 1. How could i tell excel create new sheets for the years ( sheets 2001, sheets 2003, sheet2006....),search through column (F:H) in sheet 1 to see if ANY of those columns contain that year, and paste them into the new sheet. To be more specific, in the newly created "Sheet 2001", the entire row where column(F:H) contains "2001" should be pasted. and in the newly created "Sheet 2033", the entire row where column(F:H) contains "2033" should be pasted..

Enclosed please find the reference.
https://authentification.site/files/23851477/Book32.xls
I have got sheet "2002" and "2003" here as results but for the real one i will need more years' sheets (as many as how many excel could extract in the previous stage; as shown in column L ) ......
I think this task should be quite usual (extracting by date), but i couldnt google the result....Pleas help!!
0
"in any one the three COLUMNS
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Aug 18, 2010 at 07:21 AM
Use a loop

Loop thru each year and apply formula. Filter the result and copy the visible rows to the sheet
0
Thanks for your help. however, i am totally new to "looping" so i could hardly figure out a way that perform: loop through the column,open new worksheet as well as sort out the correct year in the new sheet AT THE SAME TIME....
So far i could only figure out how to give sheet names to the newly created worksheet:( not even sure if it is looping)

Dim i As Long, LR As Long, sName As String

i = 2
5
If Cells(i, 19) = "" Then GoTo 10 ' I'm trying to count the no. of year
i = i + 1: GoTo 5
10
N = i - 1

For i = 2 To N

sName = Sheet4.Cells(i, 19).Value

Worksheets.Add(after:=Worksheets(Worksheets.count)).Name = sName
Next

I tried to code you suggest and filtered,
Range("K2").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(AND(RC[8]<>"""",OR(YEAR(RC[-5])=R2C19,YEAR(RC[-5])=R2C19,YEAR(RC[-3])=R2C19))),1,0)"

It works... but i simply couldn't find a way to put the pieces together...can you give me any hints? This task is driving me insane!

Thanks again!
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Aug 19, 2010 at 05:22 AM
Here is a link
https://ccm.net/forum/affich-258700-interesting-question-for-excel-champions

That code first creates a list of unique value to see what we have to loop thru, you dont have that case as you already know

Then it loops for each value, apply filter and create sheet and copy the data. For you the change would be that as it loops thru each value, you have to apply formula first, then apply filter and when it comes to creating sheet, it uses the value that was used to filter, you either have to change formula to use year, instead of 1 as flag or when you create sheet, use the year

see if it makes any sense to you
0