there are 3 table the source the helper and the data to be pasted
Conditions are: If date is today and there is no NA paste the values otherwise retain contents.
please see attached.
vbaeforum.xlsx
there are 3 table the source the helper and the data to be pasted
Conditions are: If date is today and there is no NA paste the values otherwise retain contents.
please see attached.
vbaeforum.xlsx
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
Is the column with the letter variables (a,b,d,w,f) to be copied also or are they just to illustrate your problem?
Thanks,
Solus
Please remember the following:
1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.Highlight the code in your post and press the # button in the toolbar.2. Show appreciation to those who have helped you by clicking below their posts.
3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.
"Slow is smooth, smooth is fast."
no it stays it's like updating the values of a,b,d,w,f
a,b,d,w,f the First table column A,b, c will be updated manually.
the second table colored yellow is the search(helper table)
the 3rd is where the data will be pasted. -> this is the update so when date is today ->update the 3rd table if it is found in the second(helper table)
the helper table has only 5 lines while the other two have 6
is that by design or is helper just missing one line?
so in this example only sample 5 is to be copied over the sample 5 in the 3rd table?
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
just missing one
ok try thisPlease Login or Register to view this content.
with paste values instead of copy formulaPlease Login or Register to view this content.
Last edited by humdingaling; 07-12-2013 at 01:42 AM.
Maybe:
Please Login or Register to view this content.
thanks to both of you.
they both meet the requirement
Or:
This method will prevent from copying the date if its not supposed to move.Please Login or Register to view this content.
as an option (with helper column in green)
Please Login or Register to view this content.
yup the loop is shorter nilem. ..
thanks all.
This should do what you want
Please Login or Register to view this content.
OK. will test it. on larger scale. thanks jindon.
@ jindon it's copying the data even date is not yet reached say date is july 20, 2013 that's the time it will copy paste the data.
OOps, missed that condition
change
toPlease Login or Register to view this content.
Please Login or Register to view this content.
Ok thanks.
Sorry guys what Am i missing in the code.
it's not copying though with the sample here.
Last edited by vlady; 07-12-2013 at 11:09 PM.
What are you trying to do?
Same thing as above but with separate sheets. copy data if date is today but i get errors when i add here
and the date I think refers to the wrong cellPlease Login or Register to view this content.
i've deleted the "#" error handling there since i think it's not needed any more.Please Login or Register to view this content.
thanks.
Your origin is now
SoPlease Login or Register to view this content.
means you are concatenating Col.H & I.Please Login or Register to view this content.
Is that what you are tying to do?
And rest of the code doesn't make sense to me at all.
the code is is from post #12. The code refers to the same sheet.
i just need it to refer to two sheet.. the formula and the data sheet
and add some range to be copied since it only has 2 range to copy i think this partso add another 2 refering to sheet formula to be copied to sheet data.Please Login or Register to view this content.
Look sample attach in Post#17 for the outline
Variable txt was used to form a unique key based on your original question.
But now your data changed, so I don't understand which old column refers to column in new data.
Error value in vba can not be compared to any value, so you will need to escape from it anyway.
What is the unique key now and which data should be transferred to where?
The unique keys will be in Formula sheet Column C6 from value and it will also change depends in the index match formula
ALG
BDGE
#Na
down
copy only column F ti I --- if the date in formula sheet is today --date is excluded in the copy paste.
paste the values in Data sheet if the date is equal to today.
Column C "dates" in Data sheets remain intact since this will update through index match in formula sheet.
I'm still not sure where to paste....
Result looks doesn't look good.
Please Login or Register to view this content.
the target to paste the values is in Data D, E,F,G
C contains the index match for date so this is not included.
I'll check it.
yup. they are in the right cells.
Thanks again Jindon. Marking solved now.
I think I'm missed this part
paste the values in Data sheet if the date is equal to today.Please Login or Register to view this content.
hmm but it work when i change the date but to be sure will be using that . btw where is the best location for this code so it updates frequently. worksheet selection change ,worksheet calculate or any suggestion aside from the two?
I would place the main code to a Standard module and call it from Worksheet_Activate event, so that it will updates every time Data sheet is selected.
to Data sheet module:
Please Login or Register to view this content.
ok will do but also i would include it in worksheet open maybe
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks