Copying data between sheets

I'm using VBA linked to buttons at the end of each row in a worksheet (about 20 rows) to xfer data
in the relevant row onto another worksheet. The code looks as follows:

Please Login or Register  to view this content.

I'm doing that for ten columns. What I'm wondering is, is there a way by passing
parameters to pass the number of the relevant row to the subroutine so that where
for instance the code is "Range("A6").Select", that number is inserted next to 'A' and
used in the program?

Hope that was clear enough, any help appreciated!

Have a read of: https://www.excelcampus.com/vba/copy...ls-vba-macros/

No thks that doesn't do the job...

Originally Posted by sintek

How bout uploading a sample file depicting the result.

Yes but the output isn't important - I'm just wondering if there's a more elegant and less
tedious way to change this particular line/s "Range("A6").Select" etc so that the row number
can be automatically changed in the code to the correct row. As is I can copy and paste the code to each button
and go through and manually change all the row numbers - but surely there's a more efficient way to
do the job?

Ok no problem I've figured it out myself (very rusty with VBA) - first declare an int and pass it as follows:

Please Login or Register  to view this content.

then remove the range command and replace it with Cells(iRow, 1).Select etc where iRow is the row number and the '1' is the column :

Please Login or Register  to view this content.

Don't use "Select"
PreExpiry is spelled different. On purpose?
What determines which row or column to copy and paste? And to where?
If you want values only, something like

Please Login or Register  to view this content.

is more efficient.
In my opinion, supplying code that does not do what you want is not very helpful.
A good explanation what you want goes a long way.
Of course an attachment with before and after is the best.

Originally Posted by jolivanes

Don't use "Select"
PreExpiry is spelled different. On purpose?
What determines which row or column to copy and paste? And to where?
If you want values only, something like

Please Login or Register  to view this content.

is more efficient.
In my opinion, supplying code that does not do what you want is not very helpful.
A good explanation what you want goes a long way.
Of course an attachment with before and after is the best.

Thanks jolivanes! - I was sure there must be a more elegant way to xfer the data
but as I said my VBA was too rusty to remember how. (The code I supplied works perfectly
btw - I copied and pasted some of it after running macros which is why it looks so kludgy.)

I've used the Cells command btw so that I can used the passed parameter:

Please Login or Register  to view this content.

In Post #1 you mention
"about 20 Rows" and "I'm doing that for 10 Columns"
What is the logic of these cells addresses in both Sheets? Potentially you have to transfer 200 values.
In Post #8 you have "iRow" but what is "iRow"?

Another possibility maybe.
Change references, Sheets, iRow etc, where required

Please Login or Register  to view this content.

Originally Posted by jolivanes

Another possibility maybe.
Change references, Sheets, iRow etc, where required

Please Login or Register  to view this content.

Ah yes arrays - I remember using them years ago when I was using VBA much more
intensively. There's always more than one way to do things with VBA! I prefer
your earlier code though as it's clearer and simpler.

'iRow' is the passed parameter I mentioned - I've put a button at the end of each row, which when
clicked on passes the row number using iRow to the routine, which then copies the data from that
row to the other worksheet. Simple and efficient!

Thanks very much for your help jolivanes, I'll mark this as Solved.