Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Won't Copy/Paste
MS EXCEL 2000:
Hi, I am having trouble recording a macro that will copy the text in a cell, and paste it into the Find box, and then find the text (on a seperate worksheet). The macro Finds the text that was in the cell when I recorded it, but I want it to find what is there at the time that I run the macro. Any help would be greatly appreciated. More Specifically, I want the macro to work as follows: ' Go to a specific Cell ' Copy to the clipboard all the text in that cell. ' Paste what it copied into the Find box. ' Go to a specific worksheet. ' Go to A1 cell ' Paste what's on the clipboard into the Find Box and Find. ' Select the contents of that cell, and Copy it to the clipboard. ' Go to a specific worksheet and a specific cell, and paste the contents of the clipboard in it. FOLLOWING IS THE CODE AS IT WAS RECORDED, WITH MY COMMENTS IN IT TO POINT OUT WHAT'S WRONG WITH IT. __________________________________________________ ____________________ Sub Macro5() ' Application.Goto Reference:="R9C7" ActiveCell.Range("A1:T1").Select ActiveCell.FormulaR1C1 = "1 2 b3 4 5 6 b7" ' [WRONG! COPY WHATEVER IS IN THERE AT THE TIME I RUN THE MACRO] Sheets("SCIT").Select Application.Goto Reference:="R1C1" Cells.Find(What:="1 2 b3 4 5 6 b7", After:=ActiveCell, LookIn:=xlFormulas _ ' [WRONG! FIND WHATEVER IS ON THE CLIPBOARD AT THE TIME I RUN THE MACRO] , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate ActiveCell.Select ActiveCell.FormulaR1C1 = "1 2 b3 4 5 6 b7 " ' [WRONG! ACTIVATE WHATEVER IS FOUND THERE AT THE TIME I RUN THE MACRO] Sheets("FINDER").Select Application.Goto Reference:="R34C7" ActiveCell.Select ActiveCell.FormulaR1C1 = _ "Major-Mode 1 - Ionian (The Major Scale) 1 2 b3 4 5 6 b7 " ' [WRONG! THE TEXT SHOULD BE WHATEVER IS IN THERE AT THE TIME I RUN THE MACRO] ActiveCell.Offset(1, 0).Range("A1").Select End Sub ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Won't Copy/Paste
Forget the clipboard! The general idea is to:
get the value of the selected cell (not the formula) and store it in a temporary variable (I called it "lookfor") Application.Goto Reference:="R9C7" lookfor = Activecell.value2 and then use this variable in the find statement: Sheets("SCIT").Select Application.Goto Reference:="R1C1" Cells.Find(What:=" & lookfor & ", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate And so on. Try it! Nikos Y. -----Original Message----- MS EXCEL 2000: Hi, I am having trouble recording a macro that will copy the text in a cell, and paste it into the Find box, and then find the text (on a seperate worksheet). The macro Finds the text that was in the cell when I recorded it, but I want it to find what is there at the time that I run the macro. Any help would be greatly appreciated. More Specifically, I want the macro to work as follows: ' Go to a specific Cell ' Copy to the clipboard all the text in that cell. ' Paste what it copied into the Find box. ' Go to a specific worksheet. ' Go to A1 cell ' Paste what's on the clipboard into the Find Box and Find. ' Select the contents of that cell, and Copy it to the clipboard. ' Go to a specific worksheet and a specific cell, and paste the contents of the clipboard in it. FOLLOWING IS THE CODE AS IT WAS RECORDED, WITH MY COMMENTS IN IT TO POINT OUT WHAT'S WRONG WITH IT. _________________________________________________ _________ ____________ Sub Macro5() ' Application.Goto Reference:="R9C7" ActiveCell.Range("A1:T1").Select ActiveCell.FormulaR1C1 = "1 2 b3 4 5 6 b7" ' [WRONG! COPY WHATEVER IS IN THERE AT THE TIME I RUN THE MACRO] Sheets("SCIT").Select Application.Goto Reference:="R1C1" Cells.Find(What:="1 2 b3 4 5 6 b7", After:=ActiveCell, LookIn:=xlFormulas _ ' [WRONG! FIND WHATEVER IS ON THE CLIPBOARD AT THE TIME I RUN THE MACRO] , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate ActiveCell.Select ActiveCell.FormulaR1C1 = "1 2 b3 4 5 6 b7 " ' [WRONG! ACTIVATE WHATEVER IS FOUND THERE AT THE TIME I RUN THE MACRO] Sheets("FINDER").Select Application.Goto Reference:="R34C7" ActiveCell.Select ActiveCell.FormulaR1C1 = _ "Major-Mode 1 - Ionian (The Major Scale) 1 2 b3 4 5 6 b7 " ' [WRONG! THE TEXT SHOULD BE WHATEVER IS IN THERE AT THE TIME I RUN THE MACRO] ActiveCell.Offset(1, 0).Range("A1").Select End Sub ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and Paste Macro | Excel Discussion (Misc queries) | |||
HELP with macro for copy and paste | Excel Discussion (Misc queries) | |||
Should I use a macro to copy and paste value? | Excel Worksheet Functions | |||
Copy Paste macro | Excel Discussion (Misc queries) | |||
copy/paste macro | Excel Programming |