auto populate values--plsss help!!

[luria]

I need a code to auto populate values from sheet 1 , rows A2 to A5.
Example :
Whenever I click on the macro button, in A1 it should be:

abc123
def456
ghi789
jkl000

Also, please suggest where should I be including this code in an already existing macro code.

 

[CharlesChuckieCharles]

Maybe post your existing code

Are all four lines suposed to go in A1 ( with line feeds?)?

 

[luria]

Hello!

I posted the code in detail in this question which has 0 answers :
http://www.mrexcel.com/forum/excel-questions/802454-no-text-change-a2-a6-column-sheet-1-a.html

yes, the first four values from a2 to a5 should go transposed with a comma separating them to A1 on sheet 2..

Thank you..

 

[CharlesChuckieCharles]

why does everything happen on sheet1?

 

[luria]

am so sorry! The first 4 values should go to row A2 on Sheet 2 from Sheet 1 column A1.
So, the column from A1 on Sheet 1 should transpose to Sheet 2 A2.
Am already using a macro for this, however, the code doesnot include that the first 4 values should always be the same.

Note : The macro transposes first 1000 values from sheet 1 column A to sheet 2 Row A2
Next thousand from sheet 1 column A to sheet 2 Row A3 and so on.(it has to be strictly thousand or 999)

Now, whenever I use the macro, no matter what data I paste, I need the first 4 values to be the same and fixed and get transposed everytime. the values are svcdlvcpo, sdocpw, cpoprod1, sdocpo11 (Sheet 1 a2 to a5). I tried to write these values from a2 to a5 and hide those cells, but it dint work and didnot look great as well.

For both the sheets am not using Column B.

My thoughts :

I thought instead of making changes to the code in Sheet1, can we write a code so that the values svcdlvcpo, sdocpw, cpoprod1, sdocpo11 auto populate to sheet 2 A2 and then the next values pasted follow

 

[luria]

I need to submit this by saturday

 

[CharlesChuckieCharles]

why does everything happen on sheet1?

I looked at the code you'd posted in the other question, and everything happens on sheet1 there are no references to sheet 2


This function concatenates cells into one string

Function Myconcate(rngRange As Range, Optional myspacer As String) As String
Dim MyResult As String
Dim MyCell As Range
MyResult = ""
For Each MyCell In rngRange
    MyResult = MyResult & IIf(MyResult = "", "", myspacer) & MyCell.Value
Next MyCell
Myconcate = MyResult
End Function

Use like

This format use a comma to seperate the cells like

BitOfdata1,BitOfdata2,BitOfdata3,BitOfdata4,VAC

<COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY>
</TBODY>

in sheet as =Myconcate(L3:P3,",")

in code Range("A1").value =Myconcate(Range("L3:P3"),",")

 

[luria]

Charles..thanks for your response!
Yes, on the original macro that I am using everything happens on sheet 1 and the results show on sheet 2.
So, where should I make the changes to the already existing code so that the 4 values on sheet 1 always throw on sheet 2 ..where should I include the above mentioned code to the existing code. I totally have no idea about VB.

Many thanks,
Luria

 

[luria]

macro sheet

I have pasted the URL for the sheet.
a2 to a6 should always be fixed and should not change and they should not actually appear everytime I open the sheet, but should get transposed to sheet 2.