Macro help...copying and pasteing [Solved/Closed]

Report
Posts
16
Registration date
Wednesday October 21, 2009
Status
Member
Last seen
March 12, 2010
-
Posts
16
Registration date
Wednesday October 21, 2009
Status
Member
Last seen
March 12, 2010
-
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

10 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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.
Posts
16
Registration date
Wednesday October 21, 2009
Status
Member
Last seen
March 12, 2010

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
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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.
Posts
16
Registration date
Wednesday October 21, 2009
Status
Member
Last seen
March 12, 2010

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
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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.

Posts
16
Registration date
Wednesday October 21, 2009
Status
Member
Last seen
March 12, 2010

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.
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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.
Posts
16
Registration date
Wednesday October 21, 2009
Status
Member
Last seen
March 12, 2010

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!
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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
Posts
16
Registration date
Wednesday October 21, 2009
Status
Member
Last seen
March 12, 2010

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


mike