macro help...copying and pasteing Solved/Closed

Question

Hello,
I'm trying to write a macro to copy a row of cells to where the cursor is.  The info is constant and the pasteing changes. I've used the "record" button with both relative on and off and the pasted info always goes back to where I ran the "record" button. Example:  I put the cursor in cell A15 and want to copy cells C13 to C15 to cells A15 to A18. After I record this, I put the cursor in cell A17 and run the macro.  The info gets pasted back to cells A15 to A18.  I tried using range names for the info location and the result location (after deleting the range name that I used for the first pasting and creating a new range name for the new spot as part of the macro) but the pasting always goes back to where I first started when I used the "Record" button. I'm using excel 2008

venkat1926 · Answer

I am confused. 

you want to copy C13 to C15(you cursor is in C13) to A13 to A15

but if you put cursor in A17 what do you want to copy A17 to A19 and where 

I presume you mean the cursor is in C17 and c17 to c19 is to be copied to A17 to A19

in that case use tis macro 

Sub test()
Range(Selection, Selection.Offset(3, 0)).Copy
Cells(Selection.Row, "A").PasteSpecial
Application.CutCopyMode = False
End Sub

don't forget to keep the cursor at the proper place.

selflearner · Answer

Hi, Venkat 1926.

Sorry about the confussion, I'm new to this.  I should have used C 13..C15 and A 13..A15.  I'm trying to copy a row of cells that remain constant to where the cursor is.  When I move the cursor the next time I have to copy the cells, the information is copied back to where I recorded the macro.  I've tried with relative reference on and off.  I also gave the place to be pasted a range name, pasted the cells, and deleted it (using the macro).      I have a column of names with numbers in the rows next to them.  I'm trying to use a macro to run after I enter a name in the coulmn, move right one cell,  and if it is the same name as any of the above, I don't have to rewrite the row of cells next to it.  I'm self learning and want to take this one step at a time.  After I get the macro to work, I'll make macro buttons to match the names that is used alot.  Later, I will work on some kind of box that would come up and I click on a name in it.  Then the information will be copied.  The spot where I want the pasted information changes but the spot to be copied is constant.  Here is the macro I've tried.  "this" is the range name I gave to the cells that is constant.  "abc" is the range name that gets created to receive the paste and then gets deleted till the next time I run the macro. After the cells get pasted, I move two cells to the left.  
ActiveCell.FormulaR1C1 = "'SUB"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveWorkbook.Worksheets("Sheet1").Names.Add Name:="abc", RefersToR1C1:= _
        "=Sheet1!R15C3"
    ActiveWorkbook.Worksheets("Sheet1").Names("abc").Comment = ""
    Application.Goto Reference:="this"
    ActiveCell.Range("A1:C1").Select
    Selection.Copy
    Application.Goto Reference:="abc"
    ActiveSheet.Paste
    ActiveCell.Offset(0, -2).Range("A1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Names("abc").Delete

venkat1926 · Answer

I am still confused.
quote
When I move the cursor the next time I have to copy the cells, the information is copied back to
unquote 
inforamtion, back  etc vague terms. 



when you say c13:c15  it is not the same row it is 3 rows in column c
ok
you keep cursor in C13
copy c13Lc15 to A13:A15
next you keep cursor in A17
copy A17:A19 to be copied to c17"c19
is this what you want.

give a few examples using cell addresses.

selflearner · Answer

Hi, Venkat1926..  
What I’m trying to do is save key strokes and learn how to write macros. I know it’s hard to understand, I don’t know the proper terms, yet.  OK, here goes.     Cell A1= DATE…Cell B1= NAME…Cell C1= HOURS…..Cell D1= GROSS…Cell E1= FEDERAL..Cell F1= STATE..Cell A2= 01/01/09….Cell B2= HOYT….Cell C2= 40…Cell D2=  0…Cell E2= 0…Cell F2=  0..Cell A3= 01/08/09…Cell B3= GREEN..Cell C3= 40…Cell D3= 0…Cell E3= 0…Cell F3=0….Cell A4= 01/17/09...Cell B4= JONES…Cell C4= 40..Cell D4= 0..Cell E4= 0…Cell F4=0 .          What I already have is a macro that will freeze the sheet under the headings column, and puts the next available row of cells in the middle of the monitor  (that I made using the record button many,many,many times) with the cursor under the heading “DATE”  (A5).  After I enter the new date and move one cell to the right (B5), if the name is the same as anyone above, and the information is the same,  I want to get his name typed in and copy his information to the cells to the right.  If the name is new, or if the information is not the same, I’ll have to type it in.  After I get the macro to work, I’ll make different macros with different names and information. I’ll have a macro button with his name on it and click it, if the name is the same (and information). I don't want to get involved in "vlookup" yet.  Later on, after my brain resets, I’ll work on making a message box that has the names in it that I can click on instead of buttons. By doing it this way, I’ll have macros to learn from.  The problem I’m having is:  First I gave the  row of cells next to “JONES” from “HOURS  to “STATE” (C4..F4) a range name of “this”. I’m going to have to type in the date so I won’t use it in the macro. I put the cursor in the next available cell under “NAMES” (B5) and using the record button I typed in JONES, then I moved one cell to the right (C5) and created a range name (“abc”) using CTRL+f3, then I did a goto(f5)" this” and right clicked to copy it, then did a goto (f5)" abc" and hit entered.The information gets pasted to the cells to the right of the name JONES.  Then I moved 2 cells to the left (A5)(under “DATE”) and using CTRL+f3, I deleted the range name “abc” so I could create a new spot  the next time.  When I tried to use the macro a few cells down (B9) it would type in the name JONES but it would copy the range name “this” (C4..F4) back to were I recorded the macro. I got the same results using relative reference on or off. I’m only using JONES and the range of "this" (C4..F4) to write the macro.  When I get this to work, I’ll make up range names for each person’s information.  If it’s the same, the macro will do the work, if not I’ll have to type it in myself.  The real  one is about 60 rows long and 12 columns wide.  Sorry to be so long winded. 
Mike

venkat1926 · Answer

suppose your data is like this 
you have date in the first four rows. with row1 having headings.

what you want is 
when you type in A5 the new date and B5 a name and if the name exists in the previous rows the data for that name in that previous row should be filled in B5 to F5
if the name is  not found a mesaage box will appear that the name is not found and asking you ti fill it. 

If my understanding above is correct  the solution is here.

right click the sheet tab and click view code. in the resulting window copy paste this code (this is an event code)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, cfind As Range
Application.EnableEvents = False
If Target.Column <> 2 Then
Application.EnableEvents = True
Exit Sub
End If
Set r = Range(Target.Offset(-1, 0), Target.End(xlUp))
Set cfind = r.Cells.Find(what:=Target.Value, lookat:=xlWhole)
If Not cfind Is Nothing Then
Range(cfind, cfind.End(xlToRight)).Copy Target
Else
MsgBox "name not found fll up data"
End If
Application.EnableEvents = True
End Sub

NOTE:
1. try the code in the experimental data given by you first
2. in A5 type some date and inB5 type the one of the names already available from the previous data . see what happens When you type the same name as in the above rows  be careful of spelling
3.in A6 type some date and in B6 type some name which is NOT available in the rows abvoe and see what happens.
4. There should not be any gap in any of the cells or rows.

selflearner · Answer

hey, Venkat 1926, 
 Thanks for the help. I didn't know anything abour "event codes" This will save me from wearing out the key board. Interesting that the macro doesn't show up in the macro box (CTRL f8). Is there a way to specify which data gets copied?  this macro seems to find and copy the first set of columns down from the top that it finds when the names match. Example, if the data for say GREEN changes, and will stay like that for some time, I want to use this new data instead of the previous stuff. I also have a couple of more questions but it's about message boxes, so I was going to start a new thread with a new heading.

venkat1926 · Answer

I did not catch the point

suppose row 1 has the entry Green

after a few rows of different names e.g in row 5 you type  green
then according to the event code  first row will be copied.
this means both the first row and the 5th  row are exactly same
again after few rows in row 8 you type green the code  copes the row 1 . It may not copy row 5
what does it matter. row 5 is same as row 1. 

this means some where there is a problem of logic. explain fully with example.like above. 

what is that control+F8. what is your version of excel.
in the vb editor (alt+F11) there is standard module in which normally macro are parked. If you want to invoke the macro you have to invoke it every time. 
But in an event code as soon as the event occurs immediately and automatically  the code is invoked.

selflearner · Answer

Hello Venkat1926,
What if I type in Green in row 20 and need to change his data.    I  type in the new data for row 20.  When I type in Green in row 30, I want to start using the data in row 20, not row 1.   Can I make this code copy a specific row rather than go to the first row that has his name. 
Also, The message box comes up if the name does not match. Can I have another sentence in the box that askes me if the data is the same and if not, it stops the macro so I can enter the new data. I hope I'm not asking to much.  I'm trying to learn.   
 Opps, I meant ALT+F8, which brings up a list of macros that I can use.  I'm using Microsoft Office 2007.  As you explained, I don't need to invoke this macro everytime I need it.  It sure makes it easier.  I'm used to using either a shortcut key, or a button.  This keeps the sheet clean and I don't have to remember a shortcut letters!

venkat1926 · Answer

I have modified the EVENT CODE.

now open the experimental sheet containing the four rows. 

date	name	hours	gross	federal	state
1/1/2009	hoyt	40	0	0	0
1/8/2009	green	40	0	0	0
1/1/2009	jones	40	0	0	0

please carefully read these instructions

now go to B5 type Green. a message box will come up "do you want to continue the macro"
click NO button.  The event code skips out. then you enter what ever you want in C5 to F5
now in B6 type some name which is not there in the database. you get msgbox "name not found fll up data".clcik ok . the event code closes  you fill up data for C6 to F6
in B7 type green. now the first message box  do you want to continue the macro will comeup 
click yes. then row no. 5 will be copied .

do a few more names in column b(do not give any blanks) and see whether the event code does what you want. 

the modifed event code is 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, cfind As Range
Dim msg, style, response
Application.EnableEvents = False
If Target.Column <> 2 Then
Application.EnableEvents = True
Exit Sub
End If
Set r = Range(Target.Offset(-1, 0), Target.End(xlUp))
Set cfind = r.Cells.Find(what:=Target.Value, lookat:=xlWhole)
If Not cfind Is Nothing Then
msg = "do you want to continue the macro"
style = vbYesNo
response = MsgBox(msg, style)
If response = vbNo Then
Application.EnableEvents = True
Exit Sub
End If
Set cfind = Cells.FindPrevious(after:=Target)

Range(cfind, cfind.End(xlToRight)).Copy Target
Else
MsgBox "name not found fll up data"
End If
Application.EnableEvents = True
End Sub


then you can use the event code in your data.
BUT BE CAREFUL TO SAVE THE ORIGINAL DATA BASE SOMEWHERE SO THAT IF IT IS MESSED UP YOU CAN RETRIEVE THE DATA.

CONFIRM IF THIS IS OK

selflearner · Answer

That's it!  I want to thank you for your time and effort.  It's exactly what I need


mike

Macro help...copying and pasteing

10 responses

Similar discussions

Subscribe To Our Newsletter!