Copy from sheet 1 to next free in sheet 2 (compare values)

[gangel]

Hi pplz,
Ive used "record macro" etc but im really sumpted on this one. Below is what I need to do!
(all these values are dates)

Looking at:
Range C3:C34 Sheet1 and comparing it to (a list of dates)
Range C3:C34 Sheet2

Compare Sheet1.C3 with Sheet2.C3,
if same, go to next row in the column (C4)
If DIFFERENT, paste Sheet1.C3 in next free cell on Sheet2 (eg into D3)
Then go to next cell
Continue for whole range


Therefore
C3->D3->E3->
C4->D4->E4

And so on.

I hope this makes sense! Im very new a VBa and trying to piece it all together but im getting confused with how to make one cell compare to another then move onto next. I know i can write every single range... just dont know how to write a small looping situation!

Cheers,

Glenn

[NickyC]

Hi Glenn
Not sure from your post where you want to insert the data on sheet 2 - this macro will insert it in the first blank cell to the right of columns C in sheet 2

Please Login or Register  to view this content.

[smuzoen]

Unsure exactly if it is just 1 column you want to compare because you said C3-D3-E3??? However the following will compare 2 arrays and copy values that are different to next free column. Is there other data in the worksheet. Perhaps a sample workbook would help if this code is not what you need

Please Login or Register  to view this content.

This will also find last column with data and paste to the next cell in the row

[gangel]

ok ill send an example sheet tm.
Ive just noticed i havent explained well enough!
Thanks for the responses and check back in... 12 hrs?

[gangel]

Attached is the Workbook with both sheets as well as an explanation page with what I'm after.

I am aware taht I ahve changed the sheet names from 1 and 2!

THanks again!

[smuzoen]

I would use a worksheet_change event to update on the fly

Please Login or Register  to view this content.

As you add new values to column G on the Record Sheet it will check the Date Record sheet for the same value - if the same value is found no entry is made. If it is new then it is added to next available column. See attached workbook.

Hope this helps
Anthony

“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s)

[gangel]

OMG THANKYOU SO MUCH.
Works really well! Very very very happy, going to make my life so much easier at work!

Going to go throuhg the code now and dissect it to see what i was doing wrong!

I dont see where the actual "copy and paste' part is in that text though!

[smuzoen]

The section of code that copies the new date across is

Please Login or Register  to view this content.

Essentially I place the values in the Date Record sheet into an array by using a range and then I iterate through the array and see if the value in the Webster worksheet is present in the array. If it is not in the array then the variable nFnd is set to true and then this fires the code above.
It is better to do the copy in a Worksheet_Change event as the event occurs rather than when closing the workbook or using buttons otherwise you would need to check every row in the Webster worksheet against the Date Record sheet and this would take longer than just checking one row change when it occurs.
I hope that explains the code a little more for you. If you have any questions just ask.

Hope this helps
Anthony

“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s)

[gangel]

sounds great. Ive expanded on my requirements in this thread:
http://www.excelforum.com/excel-prog...19#post2701319
I started a new link as it is a differernt issue, but for the same workbook