jonattempt_cell
New Member
- Joined
- Mar 5, 2015
- Messages
- 26
Q: I need to simply paste the values of the row at my discretion (button) so that they remain permanently where I pasted them.If they already exist there then update them otherwise paste to the last non empty row. From where they get copied the rows can change , new rows can come and go hence they'd need a unique identifier from the column
The only formula I have for a building block for doing this is:
<code>Function paste() As Variant
macropastec4 = Sheets("Sheet1").Range("I3").Value
End Function</code>
which I could assign to a button to paste the value of the one cell however I need rows and much more (see detailed description) so I don't know whether the right solution would incorp my building block at all!
detailed description:
How can I adjust this formula to paste a row of cells based on a cell value (unique identifier) in that row. I.e incorporating a unique reference in the row ie <code>1</code> in <code>A1</code> in such a way that if I was to run the sub again it would just update the row that exists starting H10 where it has been copied with <code>H10</code> containing that referred <code>1</code>. If it was a new row it would populate the last empty row beginning either <code>H12</code> or <code>H11</code>. I would have push button to run the vba function for each row to be copied so in <code>O1</code> <code>O2</code> <code>O3</code> etc
<code> A B C D E F G
1 1 b c d e f g
2 2 i j k l m n
3 3 p q 5 3 y z
</code>
<code><code>to</code>
</code><code><code> H I J K L M N</code> </code>
<code>"non empty cells containing</code>
<code>8 previously pasted rows from(A:G)</code>
<code>9 which have now disappeared"</code>
<code>10 1 b c d e f g</code>
<code> 11 2 i j k l m n</code>
<code> 12 3 p q 5 3 y z</code>
The more complicated part lies in that <code>A1</code> to <code>G1</code> won't always contain <code>1,b,c,d,e,f,g</code> but as I paste it to where it should be pasted ie in rows <code>H to N</code> according to my rules and at my discretion ( ie clicking on the vba sub button). At a certain time completely independant of when I run the sub for each row , the row <code>A:G</code>will dissapear and repopulate with something new. Moreover it will repopulate with whatever was below <code>A1 to G1</code> ie <code>A2</code> to <code>G2</code> unless <code>A2</code> to <code>G2</code> has already disappeared in which case it would populate whatever remains just filling up to <code>A1 to G1</code> hence why I would need the unique identifier being the <code>1</code> in <code>A1</code> to work after this re-alignment whereby if I run the sub again it would recognize the <code>1</code> or the <code>2</code> and know its place within <code>H to N</code> again knowing to only update the existing row there (permanently pasted) or otherwise ( if it is a new line that exists ) to paste it to the last empty row under <code>H to N</code>
- i.e new rows can appear in <code>A:G</code> any time moving to the top or bottom
n.b. I only need the ability to paste the cells from <code>A1:G3</code>or further depending on how many rows there are ie it could be <code>A1:G20</code> but I want the sub to cator for each row independantly through a button. How those rows <code>A1:G20</code> re-populate and re-order themselves is dependant on of of the columns in the row. Ie they would repopulate if they had a similar column cell but that should be ignored in the framework of what I am trying to achieve, I need only reference to the unique identifiers in column A of the rows
other points
The only formula I have for a building block for doing this is:
<code>Function paste() As Variant
macropastec4 = Sheets("Sheet1").Range("I3").Value
End Function</code>
which I could assign to a button to paste the value of the one cell however I need rows and much more (see detailed description) so I don't know whether the right solution would incorp my building block at all!
detailed description:
How can I adjust this formula to paste a row of cells based on a cell value (unique identifier) in that row. I.e incorporating a unique reference in the row ie <code>1</code> in <code>A1</code> in such a way that if I was to run the sub again it would just update the row that exists starting H10 where it has been copied with <code>H10</code> containing that referred <code>1</code>. If it was a new row it would populate the last empty row beginning either <code>H12</code> or <code>H11</code>. I would have push button to run the vba function for each row to be copied so in <code>O1</code> <code>O2</code> <code>O3</code> etc
<code> A B C D E F G
1 1 b c d e f g
2 2 i j k l m n
3 3 p q 5 3 y z
</code>
<code><code>to</code>
</code><code><code> H I J K L M N</code> </code>
<code>"non empty cells containing</code>
<code>8 previously pasted rows from(A:G)</code>
<code>9 which have now disappeared"</code>
<code>10 1 b c d e f g</code>
<code> 11 2 i j k l m n</code>
<code> 12 3 p q 5 3 y z</code>
The more complicated part lies in that <code>A1</code> to <code>G1</code> won't always contain <code>1,b,c,d,e,f,g</code> but as I paste it to where it should be pasted ie in rows <code>H to N</code> according to my rules and at my discretion ( ie clicking on the vba sub button). At a certain time completely independant of when I run the sub for each row , the row <code>A:G</code>will dissapear and repopulate with something new. Moreover it will repopulate with whatever was below <code>A1 to G1</code> ie <code>A2</code> to <code>G2</code> unless <code>A2</code> to <code>G2</code> has already disappeared in which case it would populate whatever remains just filling up to <code>A1 to G1</code> hence why I would need the unique identifier being the <code>1</code> in <code>A1</code> to work after this re-alignment whereby if I run the sub again it would recognize the <code>1</code> or the <code>2</code> and know its place within <code>H to N</code> again knowing to only update the existing row there (permanently pasted) or otherwise ( if it is a new line that exists ) to paste it to the last empty row under <code>H to N</code>
- i.e new rows can appear in <code>A:G</code> any time moving to the top or bottom
n.b. I only need the ability to paste the cells from <code>A1:G3</code>or further depending on how many rows there are ie it could be <code>A1:G20</code> but I want the sub to cator for each row independantly through a button. How those rows <code>A1:G20</code> re-populate and re-order themselves is dependant on of of the columns in the row. Ie they would repopulate if they had a similar column cell but that should be ignored in the framework of what I am trying to achieve, I need only reference to the unique identifiers in column A of the rows
other points
- In cells <code>A1 to</code>G1<code>AND</code>A2<code>to</code>G2` etc (basically every row) I will have a combination of number, letters (written text) and inserted references (LINKS) to sheets in Microsoft onenote. So I'd need the ability to have the same link copied over with the same alignment of the LINK button I have for that particular onenote file i.e aligned to cell etc. So basically just paste whatever is there in those cells!!
- It is probably worth noting that the unique identifiers as they repopulate won't be in any order - they will be completely random i.e not neceserrily <code>1,2,3,4,</code> could be <code>313,2,32131,2,33</code> but they will be numbers
- I will also need this to referece the one sheet I am working on.
Last edited:
