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
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