Macro to copy and paste (transpose) data from column to row - How to automate values

[shevchenko2020]

I have managed to create a macro which would allow me to copy and paste an specific number of values from a column and then transpose those values into a specific row on a spreadsheet.

here is the code..

Please Login or Register  to view this content.

the problem is that this code only works up to certain number of rows... up till H13 for example, but if I want to this repeat this process up to row H600 (range of H600:H605) and pasting to I20 for example
without copying and pasting this code hundreds of times, is there a way I can do this?

Thanks for the responses.

[davesexcel]

try this out,

Please Login or Register  to view this content.

[shevchenko2020]

thanks for your code, I have tried it but it has given me the data fully transposed, from column I like this (given me 6 extra columns)
Attachment 641303

while I want the data transposed into only 2 columns like this and in order of the copy and pasting..

Attachment 641304

Anyway I can make alternations to the code to make look like this?

[davesexcel]

That's fine, I only supplied you with a code with the information you provided.

[shevchenko2020]

Ok any idea where I can alter the code to make the transposed data appear in only 2 columns,

like this?

[davesexcel]

Hard to see the logic, possibly supply a sample workbook

[shevchenko2020]

Okay here is the sample workbook on sheet 1.

Columns I and J shows how I want the data to be displayed.

The data is originally copied and transposed from column H.

So in the future any data that is input into column H would then be transposed in order into columns I and J.

[Marc L]

So in the future any data that is input into column H would then be transposed in order into columns I and J.

According to your attachment an already working event code to paste to the (Sheet1) worksheet module :

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge = 1 And Target.Column = 8 And Target.Row > 1 Then
        Application.EnableEvents = False
        Cells(1 + Target.Row \ 2, 9 + (Target.Row And 1)).Value2 = Target.Value2
        Application.EnableEvents = True
    End If
End Sub 

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[shevchenko2020]

Thanks for the code but Iam still struck