copy paste Macro

I was wondering if it is possible to make a macro do the following.

copy selected cell, move to another cell (same row) and copy
change worksheets
seach a column for the first copied value
moe 4 cells from the cell it had found in the search
then paste the second value

Is it possible??


---
Message posted from http://www.ExcelForum.com/

0
2/2/2004 10:47:16 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
126 Views

Similar Articles

[PageSpeed] 34

Olark,

Yes.  Put this in a regular module: (paste it from here, but watch for line
feeds).

Dim Data1, Data2

Sub Copy1()
Data1 = Selection.Value
Application.StatusBar = "Data1: " & Data1
End Sub

Sub Copy2()
Data2 = Selection.Value
Application.StatusBar = "Data1: " & Data1 & " Data2: " & Data2
End Sub

Sub Putt()
    Selection.EntireRow.Find(What:=Data1, After:=ActiveCell,
LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Offset(0, 4) = Data2
    Application.StatusBar = False
    Data1 = ""
    Data2 = ""
End Sub

Select the first cell, and run Copy1.  You may want to assign it to a
keyboard shortcut (Tools - Macro - Macros - Options).  Then select the
second cell and run Copy2.  Then select th destination sheet, select any
cell in the desired row, and run Putt.  Pronounced "put."  This doesn't
actually do a paste, so you'll not get formats and other things from the
original cell.  We can change that if you need it.

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"olark >" <<olark.110toq@excelforum-nospam.com> wrote in message
news:olark.110toq@excelforum-nospam.com...
> I was wondering if it is possible to make a macro do the following.
>
> copy selected cell, move to another cell (same row) and copy
> change worksheets
> seach a column for the first copied value
> moe 4 cells from the cell it had found in the search
> then paste the second value
>
> Is it possible??
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
nowhere1083 (630)
2/2/2004 11:22:04 PM
I will need the Paste to be a paste special, value.

But, I'll give this try, thanks for the headstart.


---
Message posted from http://www.ExcelForum.com/

0
2/2/2004 11:38:09 PM
Olark,

The code I gave you does pretty much the same as paste special - value.  But
you can't use that command - it would paste-special right at the selection,
not four cells hence from where the first value is found.  You must use the
macro (Putt).

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"olark >" <<olark.110w1j@excelforum-nospam.com> wrote in message
news:olark.110w1j@excelforum-nospam.com...
> I will need the Paste to be a paste special, value.
>
> But, I'll give this try, thanks for the headstart.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
nowhere1083 (630)
2/3/2004 5:09:00 AM
Earl, thank you very much for your help. the code worked great.  I am
going to try and streamline it for the end user.

Thanks,
Ian


---
Message posted from http://www.ExcelForum.com/

0
2/3/2004 8:35:20 AM
Reply:

Similar Artilces:

Copy sheet in Excel
Hi Wonder if anyone of you encounter this problem before? When I try to copy worksheet in a workbook, the following prompt comes up, indicating name conflict. "A formula or sheet you want to move or copy contains the name 'aaaa', which already exists on the destination worksheet. Do you want to use this version of the name? - To use the name as defined in the destination sheet, click Yes - To rename the range referred to in the formula or worksheet, click No, and enter a new name in the Name Conflict dialog box" When I clicked yes, another prompt comes up indicating ...

Avoid pasting over hidden rows
Hi All I have a quick question in regards to pasting data over a range with hidden rows. Is it possible to paste data over a range of cells and avoid pasting data in hidden rows? Let's say sheet2 has data in range A1:B5 and row 2 is hidden. Let's say I copy range A1:B4 from Sheet1 and paste on Sheet2 starting in cell A1. I would like the data to paste over rows 1,3,4 and 5. (skip hidden row 2) I'm pretty sure the answer is no but I thought I'd ask anyways since there are some clever folks out there who might find a way. (Without using VBA) Thanks in advance. W...

Paste Cells in Column A to Columns B-K
Hi All, I was wondering if someone can help with writing an Excel Macro or some scripting. What I am trying to achieve is the following. In Column A (A1:A1000) there are values of L0xxxxxx xxxxxx=numbers (i.e L0123456) What I want to be able to do is move the values from column A to Columns B-K (10 Columns) 10 values at a time, upto 100 lines. A B C D E F G H I J K L00001 L00001 L00002 L00003 L00004 .5 .6 .7 .8 .9 L00010 L00002 L00011 L00003 L00004 ..5 ..6 ..7 ..8 ..9 L00010 L00011 Any help would be appreciated. T...

Macro
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: intel Hi, I'm thinking of buying Mac: Office 2008, but before I do I want to check that I will be able to download Scritpsmart collection of templates without any glitches: <http://www.bbc.co.uk/writersroom/scriptsmart/faq.shtml> Does anyone know if I can do this? Regards, SP. In article <ee98ff5.-1@webcrossing.caR9absDaxw>, StregaItalia@officeformac.com wrote: > Version: 2008 > Operating System: Mac OS X 10.4 (Tiger) > Processor: intel > > Hi, > > I'm thinking of buying Mac: Of...

how do I copy specific rows without copying each row individually
I am using Excel 2007. I am trying to copy every 5th row of information and there is no specific criteria met in each row. Can anyone help? for i=1 to 25 step 5 rows(i).copy somewhere next i -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Chris H" <ChrisH@discussions.microsoft.com> wrote in message news:47EE6635-F4CF-4D70-BC02-ADBEB8113D75@microsoft.com... >I am using Excel 2007. I am trying to copy every 5th row of information >and > there is no specific criteria met in each row. Can anyone help? Hi, ...

How to copy text from a picture/image & make it available for edi.
I am trying to copy a text from a picture/image and make it available in excel/word/powerpoint as normal text and not as an image/picture. ...

use macro to change dates
I have never used macros before but am trying to convert dates using excel 2003. The dates are in the format of +1.123.456.1234 and I would like to change that to 123-456-1234. There are also some listed as 123.456.1234 and would like them to be the same as the first ones, that is, 123-456-1234. I tried using the "start recording" method and "stop recording" but the cursor just jumps back to the original cell when I try to use the recorded macro. I have 2000 cells to change. Anybody have a suggestion on how to change these? I looked at the VBE, but don't kno...

Macro recording
I understand the concept of macros, I just don't know how they execute and the possible functions. I would like to import a considerable amount of data from excel into access. The problem I have is that the information I want to import for each record is on two adjacent rows. I need a macro that will move the data contained in just one cell to the end of the row above it and then delete the row that, that information was contained in. Then I need it to repeat that function on every other row. Sounds easy right? Way my luck runs you could do anything you want to do except what I need. Anyo...

Need help in data copying. #2
Hi Dave Thanks for your reply. It is working perfect *if there is more than one row*. But If I have the data only one row (B8 in my case) , then it selec the data till the end of the file ( till Row no. 65536) I will appreciate your reply. Tom -- SMIL ----------------------------------------------------------------------- SMILE's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=488 View this thread: http://www.excelforum.com/showthread.php?threadid=25956 SMILE, Don't use .End(xlDown) For example, if the code is Range(ActiveCell, ActiveCell.End(xlDown)...

Merge cell & Copy Data from different cell
Can data been copy from a merge cell, if possible pls explain. How to copy word from another cell. Thank you ...

Copying row hieghts
Hello, I developed a form that I need to make copies of as I go down the worksheet but when I copy the form and paste it the row heights do not adjust. How can I copy down a new row height? Any suggestions are appreciated. Thanks. --- Message posted from http://www.ExcelForum.com/ Select the whole row and copy it. Then paste. (were you just dragging the autofill button?) "q4u <" wrote: > > Hello, > > I developed a form that I need to make copies of as I go down the > worksheet but when I copy the form and paste it the row heights do not > adjust. Ho...

how to auto copy excel cells to outlook email
hi, great site. not sure if this is the right place to post this: here's what i'm trying to do: i have to send daily emails to a list of people. the info on the email is in an excel doc (eg cells A1 to e5) i manually copy-paste the cells on my daily emails. (not as attachment just ctrl C crtl V) is there a way to automate this process? i.e. a form to get the info from the excel sheet and display it on th email template? hope someone can help, thanks -- asdfasd ----------------------------------------------------------------------- asdfasdf's Profile: http://www.excelforum.co...

How do I fill (copy) nonadjacent cells to adjacent cells?
I have data that is 22 cells apart which are related which has data between them which are 22 cells apart also, i.e. 1's are related, 2's are related data, etc. I have currently: 1 2 3 1 2 3 1 2 3 I want: 1 1 1 2 2 2 3 3 3 etc. Thanks Here's the quick and easy on this one. Select your top row that labels your data and then turn on the auto filter feature Data --> Filter --> Auto Filter. You'll then see a drop down on the row above your first column. Click on it and you'll see you can sort ascending/descending. This should put all your data in the format you...

Error with exporting macro from SPSS 14 to Excel 2007
I have been using this macro for a while with excel 2003. I've recently upgraded to exc= el 2007 and now when I run this I get the following error "10091ActiveX Automation: no= such property or method." which occurs on this line "line1 =3D .Selection.Row - 1" any thoughts on what's changed that might be causing this? Sub ExcelMacro(NumLayerDim As Long) ' This macro ' adds a Table number to the title of each table ' turns that line bold and blue ' groups the other lines of the table so that it is easy to ' have only titles visible in excel (th...

Copy & paste Table
I have a weekly table (cells are empty at the beginning of the week an data is added each day). This table constructs of few rows of 12.5 Pt. high, others with 15 Pt and one row 20 Pt. Now, at the end of the week, I copy this table and paste it below th previous and delete the data (only from the new table), so I'm read with empty table for the coming week. Problem is, after the pasting, the row heights I have define previously (12.5, 15, 20) aren't being kept and I need to redefined i each time. Is there any way to paste and keep the -*Rows height*- definitio intact -- I'm n...

pausing macro while cells update
I have a spreadsheet with some Reutes Wealth Manager formulas. I wish to change the RIC code in some of these cells and then check if the code is valid. For this i need to pause the macro while the cell/s update. If i use Application.wait then everything pauses and the cells do not update. I guess i need to find out what the code is to refresh the Reuters links Any ideas will be gratefully recieved -- with kind regards Spike You can insert this snippet in where you need to pause. It will operate on tenths of seconds as well as whole seconds. i.e. 1 for whole, 0.1 ...

how to find used region with macro
Hi, sorry if this obvious or has been asked recently. I have a macro that goes through all rows and all columns in a selection. If a row or a column is hidden, the font of that row or column is changed. A friend wants to use the macro in a Workbook with many sheets. He asked if I could change it so that he does not need to do a selection on every sheet before he runs it. I imagine that a nested loop through all rows and all columns will take a long time? I�ve tried to figure out how to get the range defined by the last row and the last column filled with data. First I tried with Current...

Copying from multiple worksheets
I want to copy a cell (say A1) from multiple worsheets to a single sheet in different cells (say A2 thru A15) using a formula. if you have some system in the naming of the sheets with numbers like in the default names (Sheet1, Sheet2 etc) it might be possible. Maybe you can post back with more info about the sheet names, otherwise a macro would be the only way Regards, Peo Sjoblom "GMP" wrote: > I want to copy a cell (say A1) from multiple worsheets to a single sheet in > different cells (say A2 thru A15) using a formula. Hi GMP Try this http://www.rondebruin.nl/su...

Losing cell content when copying and pasting
When I copy multiple cells, and then do a paste, the contents of some cells not involved with the copy and paste action are lost. Any ideas? What do you mean by 'not involved'? If you select, say, 12 cells (3 rows and 4 cols) and paste it on D1 then cells in the range D1:G3 will be overwritten with the source cells... Also if formulae are involved then you may see unintended (by you) impact... "tomprochaska" wrote: > When I copy multiple cells, and then do a paste, the contents of some cells > not involved with the copy and paste action are lost. Any ideas? IF ...

Problem viewing shared Outlook calendar past certain months
I've got MS Outlook users on an Exchange 2003 server who are having problems with shared calendars. They are able to view other users calendars just fine, but only up to a certain month (maybe 1 or 2 months ahead). For instance, user A can view user B's calendar, but everything after October 2005 and on doesn't show up. Same when they try to schedule a meeting for people, they can see their meetings and tasks, but only up to a certain month. Is this by design, or is there a fix for this? Is it the actual calendar or the other users free/busy? Default free/busy is 2 months un...

Macros on Windows 7
Has anyone had any experience with EXcel 2007 macros written on XP running on Windows 7 ? I am considering upgrading from XP Pro to Windows 7 but very concerned that I may have issues with existing macros. I am sure that many others will be asking similar questions. Wes: I'm sure you will get plenty of responses on this, but I'll throw in my two cents on issues I have encountered. The most troublesome problems for me were file open and file save functions. Since the xls can now be xlsx, xlsm, etc, I had to program in some tests to see which version of Excel was running...

Copy Calendar Files
Hi, Can anyone help me please,. I want to copy my work appointments in outlook calendar to my home PC. Which file are these saved into? Thanks -- Liam Keefe Insoluble Records You can export your calendar folder to a PST file then import it on your home machine using the File menu > Import & Export command "Liam Keefe" <liamkeefe@btopenworld.com> wrote in message news:d8ne38$dl0$1@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com... > Hi, > > Can anyone help me please,. I want to copy my work appointments in outlook > calendar to my home PC. Which file are the...

Year dependent macros......lengthy but i really need help thx
Dear Helpers, I'm making a project to manage my school's geography department' accounts and stock. I will try to explain my problems in as much detai as possible within my capabilities. I have recieved help from people such as yourselves before, bu advanced knowledge over mine has led to me making mistakes. I reall need some help again. Please see my attached spreadsheet. Please e-mai me at KrisB_bacon@hotmail.com if you have any queries/would prefe e-mail correspondence. Here we go: When you open the worksheet, you are requested to select the curren year. This allows a year t...

Font changes when pasting into another doc
I have two docs created originally in Word 2000, and I am using Office 2007. The docs are in compatibility mode. When I copy a selection that has two different fonts from one doc to the other doc, the whole selection changes to the font/style that is in the selection point. (I have a list of paragraphs, each preceded by some symbols in a proprietary font, i.e.  Your car has ...;  Try toothpaste ...;  This flower is ...; etc. Each paragraph has a line following with a series of ******** which are center aligned for looks) In trying to copy a selection from...

How do I copy/paste drop downs withough locking the cell link inpu
Help - I'm desperate! Hi, Could you write the question in the body of the email rather than the subject line - part is getting cut off. Also the question is unclear to me? -- Thanks, Shane Devenshire "ryerye" wrote: > Help - I'm desperate! ...