copy and paste a row

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.

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

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!

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

gowflow

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!

how about posting a sample workbook ?

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

Select allOpen 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

Select allOpen 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

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!

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

@gowflow - That's why I backed away, not enough info...

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

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

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

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.

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

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.

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

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!

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

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

K.  Will try as soon as I get out of these revolving meetings today.  thanks!

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!

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

Select allOpen 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.

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!

I am humbled....thanks.

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

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!

tks your comments.
Is this what you want  ?
gowflow
Test11-V02.xlsm

Supremely Awesome.  Works like sports car now!

:)))

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

https://www.experts-exchange.com/questions/28357784/Fast-keys-into-VBA.html

ok tks but seems other were much faster !!! better luck next time
gowflow

check ur hotmail