Copy and paste data between sheets

[johnnyvb]

Hi,

I need your help!!!!!!!!!!!!!!!!!!
I've built a macro which locates a value in Sheet1, copies it, goes to Sheet2, finds and highlights it, goes back to Sheet1, with that cell still highlighted, goes right, copies it, goes back to Sheet2, goes down to next free cell and pastes that value, goes back to Sheet1 and clears that vlaue and the value to its left (the original value it used).

All of this works, however, to do this for every row in Sheet1 will take a long time and may cause problems if i make any changes in the future. Sheet1 has about 100 rows. I would like a looping formula that can do this for each row. I heard of a For Next Loop but am not sure how to go about it.

The code i have at the moment is:

Sub Macro1()

Sheets("Sheet2")
Range("A1").Select
Selection.Copy
Sheets("Sheet1").Select
Cells.Find(What:="1A")_.Activate
Sheets("Sheet2").Select
Range("B1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Selection.End(xlDown).Select
Range("B4").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select
Selection.ClearContents


Range("A2").Select
Selection.Copy
Sheets("Sheet1").Select
Cells.Find(What:="2A")_.Activate
Sheets("Sheet2").Select
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Selection.End(xlDown).Select
Range("H4").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A2").Select
Selection.ClearContents


This only does the first two rows in Sheet2. Any HELP would be most appreciated!!!!!!!!!!!!!
Cheers
Johnny

 

[acw]

Johnny

What does your data structure look like in the 2 sheets? Does the data you are selecting from sheet2 column A exist anywhere on sheet1, or is it in column B???


Tony

 

[hsk]

What exactly u want to do?

Compare 2 sheets! Hilight the cells in sheet2 which r present in sheet1 and remove the same from sheet1
Copy all the common cells to new destination!

If this is what u want i have a utility which handles duplicates in excel.
Drop me a mail at hsk200653@rediffmail.com

 

[johnnyvb]

Johnny

What does your data structure look like in the 2 sheets? Does the data you are selecting from sheet2 column A exist anywhere on sheet1, or is it in column B???


Tony

Hi, The data selected from Sheet2 column a exists in the row 2 of my spreasheet. E.g. Column A has numbers 1-50 from A2-A51. Row 1 has 1A in B1, 1B in C1 etc. The reason for having 1-50 in column A is because a heading (For example - 1B) can have more than one reference number from Sheet 1.

Cheers
Johnny