Copy and paste data between sheets

johnnyvb

New Member
Joined
Oct 19, 2006
Messages
2
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

Well-known Member
Joined
Oct 19, 2006
Messages
567
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

New Member
Joined
Oct 19, 2006
Messages
2
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
 

Forum statistics

Threads
1,136,805
Messages
5,677,829
Members
419,723
Latest member
jamieherethree

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top