Solved

copy and paste a row

Posted on 2014-02-01
34
330 Views
Last Modified: 2014-02-06
Hi All,

I have a situation where vba code is populating the correct elements into specific cells in corresponding rows when an event is triggered.   So far so good.

If the event is in row 10 all the work done by vba is in row 10

If the work is done in row 42 all the work done by vba is in row 42

...and so on.


Now I have to add a step.  Here it is and I am having a bit of difficulty figuring it out.

After the row is populated in the specific cells I need for that entire row (From A to Q) to be copied and then pasted in the exact same place but with values only.

For some reason my overall code is not working until I do this step but works fine after the copy and paste (values only) step is included.  

So my problem is to make sure the copy and paste (values only) is in the correct row.  

I was playing around with the macros with this:

 ActiveCell.offset(0, -25).Range("A1:Q1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.Select

The trigger column is "Z", thus the (0,-25) so I think that this portion is probably correct.  What isn't correct is the a1:q1 because it will not correspond to the correct row if say that correct row is now row 55.

thanks for your help.
0
Comment
Question by:BostonBob
  • 15
  • 15
  • 2
  • +1
34 Comments
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39826321
How is the original code populating the information to the sheet?  Is there also a Loop of some kind ( For Each, With, etc.) where we can bypass specifying the row?

For example, if it is pasting values at some point, we can tell it to paste as values.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39826351
Where  ru putting you code in the worksheet change event ?
can you upload the code ?

if it is in the worksheet change event then try replacing
this line
ActiveCell.offset(0, -25).Range("A1:Q1").Select

by this line
ActiveCell.offset(0, -25).Range("A" & ActiveCell.Row & ":Q" & ActiveCell.Row).Select

gowflow
0
 

Author Comment

by:BostonBob
ID: 39827001
gowflow,

That is probably nearly what I am looking for.  

ActiveCell.offset(0, -25).Range("A" & ActiveCell.Row & ":Q" & ActiveCell.Row).Select

VBA is not so excited about it....yet.   :)

I am getting the error message:  "Run-time error '1004': Application-defined or object-defined error."  while the above code is hi-lighted in yellow.    Any ideas?

Thanks!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39827003
yes for sure do this
Range("A" & ActiveCell.Row & ":Q" & ActiveCell.Row).Select

gowflow
0
 

Author Comment

by:BostonBob
ID: 39827032
Vba gives the message:

"Run-time error '1004': Select method of Range class failed"

For some back ground information for you for whatever it is worth this  whole question is an extension of what we were working on together in my "code work....sometimes" question.  Don't know if that is helpful or not.    Basically in that question we had to get the code to put values in certain cells in the row.  ok, solved.

But there were some variables defined in this code:

Dim r1 As String
r1 = Target.row

Dim a1 As String
a1 = Target.Address

Maybe these DIM statements are stopping the execution.  FYI

For this question we are just copying and pasting those exact same values in the same place so the overall code will work.  

thanks!
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39827289
how about posting a sample workbook ?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39827358
ok try instead of this
Dim r1 As String
r1 = Target.row
Dim a1 As String
a1 = Target.Address

ActiveCell.offset(0, -25).Range("A1:Q1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.Select

Open in new window


try this

Dim WS as worksheet
Dim r1 as long, a1 as string

Set WS = Activesheet
WS.Activate
r1= Activecell.Row
WS.Range("A" & r1 & ":Q" & r1).Copy
WS.Range("A" & r1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False
WS.Range("A" & r1).Select

Open in new window


I try always to shy away from default variables like Selection, ActiveCell as when the focus changes for some reason the code start meaning something totally different and becomes totally unstable. Always use specific variable declarations like WS for worksheet and proper types as Row is Long not string !!

Try this piece of code and tell me if it serves the purpose.
Regards
gowflow
0
 

Author Comment

by:BostonBob
ID: 39827622
That didn't work either.  Might even be a small step back wards as it is affecting the other code on the sheet because now that code won't run either.

Side Note:   This business of ["A" & r1] for referring to corresponding rows....is there an actual name for this?  Is this a method?  I like to study it more.

As for a solution for this current problem I can see that we are very, very close.  Thanks for your continued help!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39827866
pls post your whole code for the event this is a cat and mouse issue that you are imposing on us by not posting the code !!!
gowflow
0
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39827871
@gowflow - That's why I backed away, not enough info...
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39827978
agree thinkspacesolution but did not as worked intensively on this same issue on 2 other questions and built a very nice model and feel asker is a bit confused as to the exact issue needed.
gowflow
0
 

Author Comment

by:BostonBob
ID: 39828659
I have attached the code in test11.  Sorry for the late reply; I’ve been dreaming up code/explanation that might be helpful to solving all of this and minimizing your frustration in trying to help me.

Having said that, I will give a brief narrative of what this sheet is with a BACKGROUND.  Then I will briefly discuss the CODE.  Next I will zero in on the worksheet called “BAS” and then finally I will describe the PROBLEM.

One thing that I should note is that whatever happens in a ROW will Stay in the same ROW no matter what sheet it is referring to.  This will become much clearer below but should be kept in mind.
 
BACKGROUND:

The code now works terrifically, before the coding problem of today, thanks to programmers such as yourselves.  In truth I would have had a zero percent chance of moving my project forward unless there were programmers like you who stuck with it and me.  So thank you for that!  

Briefly and back to the background, in worksheet “Pre” put a “1” in Column F between F10 and F25.  This action will in turn put a “1” automatically via vba code into Column “I” and the corresponding row.

So if a “1” is placed into F10 then a “1” will appear in I10
If a “1” is placed into F21 then a “1” will appear in I21
…and so on.  

A “1” appearing in Column “I” will in turn trigger another event in sheet “En” and a “1” will appear in column Z and the corresponding row just as above.

So that is:  If a “1” is placed in F10 in sheet “Pre”, then a “1” will appear in Z10 in sheet “En”.  Once this “1” is placed into Z10 then a bit of code will execute.

THE CODE:

In “En” there is code that, when triggered, places information in corresponding cells and ALWAYS IN THE SAME ROW as the initial trigger event back in “Pre”, Column F, Row(?).
Worksheet “BAS”

The code from “En” put the subject of this question into its corresponding cells in the same row as the initial trigger event back in worksheet “Pre”, Column F and, in turn,  worksheet “En”, Column Z, row(?)

PROBLEM:
After putting in a few “1”s into  Column F, sheet “pre” you will notice that sheet “BAS” populates.  

All of the information is correct and is in the correct place.
The next step is where the problem is and that is submission of this information to a third party application.   It just will not work.

I have determined on my own that if I do a manual copy n’ paste (values only) that this solves the problem.  That copy n paste is by-the-way in the exact place where it is found.  So where we found the data we want to leave it in the exact same place.

SO IN SHORT, WHAT I NEED IS WHEN THE ROW POPULATES WITH THE DATA I NEED THAT INDIVIDUAL ROW TO BE COPIED AND PASTED RIGHT BACK IN THE SAME PLACE WHERE ALL DATA WAS FOUND IN SHEET BAS***.   After the copy n Paste portion is done I need the code also to "escape" so that the dotted line that goes around the section that was copied and pasted to disappear ONLY (but obviously not to stop the rest of the code working for the program).

I am speculating that the vba code is attaching something to the data OR that I am not naming my strings/longs correctly.  

Also, in my previous question above I was having copy and paste difficulty between A:Q.  Since we are going all out here to get to the answer I really need to copy and paste A:Q  AND U:CJ.  

***Turns out that R:T is a “no fly” zone that the third party software uses for their own purposes so we have to work around that if possible and there should be no copy and paste in R:T.

I have been putting the code that gowflow has been giving me in between:
Application.EnableEvents = True and End Sub.

The DIMs went at the top of the code with the rest of the DIMs.

Finally, what I have here (Test11.excel) does NOT have any of the suggestions from gowflow at all since I might have screwed up his instructions.


Thanks for your help.  I hope that this is in the form that you all can understand.  Kindly let me know if I can clarify anything at all.
Test11.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39829213
Sorry I don't get it.
Did you try the sheet11 you posted ?
I put 1 in F10 it puts 1 in H10 and I10 then suddenly they become 0

Is this normal behavior ?
gowflow
0
 

Author Comment

by:BostonBob
ID: 39829247
Yes, that is just resetting itself.  

Now the two trigger events work almost instantaneously and in the second trigger (+code) I have some code that resets the "Pre" worksheet.

Remember our problem last time was that it was taking way too long to execute.  Now it doesn't.  I left this reset in there so you could kind of see how the solution to the last problem worked out.  

The bottom line though is the "En" code is all there and the cells are populated in worksheet Bas.

Thanks.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39829256
ok we put 1 in F10 ..... then we look at Bas it has some text in line 10
So presume this is good and ok.


So what is the problem now in short in 1 sentence if you can.
gowflow
0
 

Author Comment

by:BostonBob
ID: 39829284
Correct. Good and ok.

Copy and paste (values only) of "Bas" in the same place to "clean the data."  So A10 will end up as A10, B10 will end up as B10, C10 will end up as C10 and so on.  
 
Range is A:Q and U:CJ.    (Can't do anything to R:T or it will mess with the next set of code)

Be great too if there is an "escape" to make the dotted line go away each and every time a copy n paste is completed by vba.

Thanks!

Sorry, more than one sentence but hopefully succinct.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39829297
sorry this is chineese to me !!!
please tell me what you do in English and what it is doing and what you want it to do.

Not commenting on code language !!!! hear yourself talk the past explanation and you will understand nothing.

This sentence means nothing to me
Copy and paste (values only) of "Bas" in the same place to "clean the data."  So A10 will end up as A10, B10 will end up as B10, C10 will end up as C10 and so on.  

gowflow
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:BostonBob
ID: 39829319
Code needed:
Quite literally copy and paste (values only) to the exact same place the data came from in the row in Bas.

What the code is not doing; What I would like it to do:
Before the 'copy and paste action' the vba is unable to execute subsequent code.
After the 'copy and paste action' the vba code is able to execute subsequent code.

The why:
The subsequent code is not shown.  However, somehow this copy and paste action "cleans" the data and the subsequent code runs no problem--I've got no better way to describe what it is doing and what I want it to do other than that.

Thanks!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39829382
say this again:


Quite literally copy and paste (values only) to the exact same place the data came from in the row in Bas.

You want to copy the row that was produced in sheet Bas on the row 10 (if we were putting 1 in F10) to sheet Pre from A to Q ???

gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39829658
I don't know is this what you want ? After deduction on past comments ...
I have changed the code and moved it in a module so it is easier to troubleshoot as when you put lots of code in a worksheet_Change event then you end up with spaghetti debugging !!

Also explicitly named the worksheet we are in so to limit confusion.

I kept your old routines that were in Change event as
YourWSChange_En
YourWSChange_Pre

 ... and the new one are
WSChange_En
WSChange_Pre

I also changed the declaration of r1 and r respectively in both routines to be as Long and not as string !!! as a row is a long value not a string.

check it and let me know.
gowflow
Test11-V01.xlsm
0
 

Author Comment

by:BostonBob
ID: 39830835
K.  Will try as soon as I get out of these revolving meetings today.  thanks!
0
 

Author Comment

by:BostonBob
ID: 39831696
I am Playing around with the different combos and seeing what works best.
Definitely be back to you in less than a day.

Thanks for the loops, those will come in handy elsewhere in the program.  

Question while we are waiting for my brain to digest your Gold you've thrown at me:  What are these called?  I sure would like to read up on it some more.    

"A" & r1 & ":" & "Q" & r1
or
Worksheets("Qot").Cells(r1, "A").Value

Seems to me that there must be a "method" or name to these kinds of things where we are able to work with ranges so fluently.  Before I stumbled across this notation I was utterly lost (and yes, way more lost than I am now).      

This "method" was THE hardest thing that I had to get for my little brain but programmers seem to know what it is as a second nature.   Not me.  

Also:  for the very first time I finally "Get IT" why we use modules.  Thanks for that too.

So in short you've given me a bunch to ponder and please know that I AM working with it and experimenting with it. ...and very grateful for it.

Be back to you soon enough.

Thanks again!
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39831805
I think the fact that you 'copy & paste as values' means you are manually changing the values, thus the Change event triggers.

if you try to do the same from code, i don't believe it triggers the event. (yes its a pain i have found before)

try running macro recorder on your copy & paste operation. it will look like
    Range("A1:C7").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Open in new window


add this (after changing the Range select area appropriately) to the end of the macro that copies data now and see if the event throws.
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39831868
So to answer your question: say r1 = 5
"A" & r1 & ":" & "Q" & r1
>> "A5:Q5"

or
Worksheets("Qot").Cells(r1, "A").Value
Cells(Row,Col)
Cells(5,"A")     or
Cells(5,1)   is the same thing

when using Cells you can refrence a column by its label A,B, C etc.. putting it in "A" (quotes) or you can put its corresponding position 1 or 3 for C or 26 for Z
but always easier to debug and to know how to code when oyu reference it with its label
as Column ACB (will take you ages to multiply to find the code where you can simply put
Range("ACB" & r1)
or
Cells(r1,"ACB")

Enjoy !!
gowflow
0
 

Author Comment

by:BostonBob
ID: 39832741
You have given me what I wanted. Thanks!  

Even though I frustrate the heck out of you, you are my beacon on the horizon programming wise.  Really, I am grateful.

It works....99%

The third party vendor message says "hit enter" and when I manually do then that does the job.   The code works!

The hitting the "enter" button manually also gets rid of the dotted line going around the range.

I went onto the macro recorder to get an "enter" function/code and was a bit surprised to see that it does not record the hitting of the "enter" key at all.  

Do you have a quick fix for this?

thanks!
0
 

Author Closing Comment

by:BostonBob
ID: 39832743
I am humbled....thanks.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39832784
yes for sure. You want to remove the doted lines around the copy is that what you want ? what is this hit enter thing ? Explain more pls.

btw tks your nice comments we always thrive for nice comments believe it or not ! for me they keep me going :)
gowflow
0
 

Author Comment

by:BostonBob
ID: 39832845
It is just a simple hitting the "enter" key on the computer board manually. So whatever the vba equivalent of that is that is what I need and that should do the trick.

In other words, when the "enter" key or vba equivalent is placed right after the copy n paste code you have given, the entire code works and does its job.  

This "enter" key also gets rid of the "dotted line around the range" issue.

You guys darn well deserve the happy comments.

Where else in the world would programming gurus/engineers/genius sit around and entertain cognitively profound beginner programmers such as myself and try to extract questions out of them when guys like me don't even know how to ask the questions at all.

Its almost like a baby crying and having the parent trying to guess what the fix is. When baby stops crying we all suppose it worked.

I stopped crying...almost.  :)  thanks!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39832918
tks your comments.
Is this what you want  ?
gowflow
Test11-V02.xlsm
0
 

Author Comment

by:BostonBob
ID: 39833612
Supremely Awesome.  Works like sports car now!

:)))
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39833659
Great !!! I will keep this question monitored case you need help in any new question pls post a link in here.
gowflow
0
 

Author Comment

by:BostonBob
ID: 39837967
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39838311
ok tks but seems other were much faster !!! better luck next time
gowflow
0
 

Author Comment

by:BostonBob
ID: 39840787
check ur hotmail
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now