Macro to rearrange data

Closed
Paramesh77
Posts
3
Registration date
Saturday June 7, 2014
Status
Member
Last seen
June 13, 2014
- Jun 7, 2014 at 08:08 AM
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
- Jun 16, 2014 at 11:36 AM
I would like to enter a data as shown in the sheet 1 pic below and want it to be stored as shown in sheet 2. Can someone share how to code using macro and / or other active x controls in excel


7 replies

TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
514
Jun 10, 2014 at 11:20 AM
Hi Paramesh,

Your pics are giving me a headache! Next time posting a pic, please adjust the pixel count by changing the px value to 500 (which is max):
...png|325px||center]
to
...png|500px||center]


Source sheet is called "Sheet1".
Destination sheet is called "Sheet2".
Here is the code:
Sub RunMe()
Dim lRow, x, y As Long

Sheets("Sheet1").Select
lRow = Range("A1").End(xlDown).Row
x = 1

Do
    x = x + 1
    y = y + 1
    Sheets("Sheet2").Cells(y, "A") = Sheets("Sheet1").Cells(1, "A")
    Sheets("Sheet2").Cells(y, "B") = Sheets("Sheet1").Cells(x, "A")
    Sheets("Sheet2").Cells(y, "C") = Sheets("Sheet1").Cells(1, "B")
    Sheets("Sheet2").Cells(y, "D") = Sheets("Sheet1").Cells(x, "B")
    Sheets("Sheet2").Cells(y, "E") = Sheets("Sheet1").Cells(1, "C")
    Sheets("Sheet2").Cells(y, "F") = Sheets("Sheet1").Cells(x, "C")
    Sheets("Sheet2").Cells(y, "G") = Sheets("Sheet1").Cells(1, "D")
    Sheets("Sheet2").Cells(y, "H") = Sheets("Sheet1").Cells(x, "D")
    Sheets("Sheet2").Cells(y, "I") = Sheets("Sheet1").Cells(1, "E")
    Sheets("Sheet2").Cells(y, "J") = Sheets("Sheet1").Cells(x, "E")
    Sheets("Sheet2").Cells(y, "K") = Sheets("Sheet1").Cells(1, "F")
    Sheets("Sheet2").Cells(y, "L") = Sheets("Sheet1").Cells(x, "F")
Loop Until x = lRow

End Sub


Best regards,
Trowa
0
Paramesh77
Posts
3
Registration date
Saturday June 7, 2014
Status
Member
Last seen
June 13, 2014

Jun 11, 2014 at 03:02 AM
Hi Trowa
Thanks for the feedback and response. I tried your code in the command button. If i run it the system continuously copies the same value for n number of times. I would like to copy the value from sheet 1 upon clicking the "Submit" button in the first row of "sheet 2" and then to the 2nd row on the same sheet and so on . Can you pls help me with this.
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
514
Jun 12, 2014 at 10:42 AM
Hi Paramesh,

Your reply startled me at first, but when I tried to explain what I did, I noticed I wrote the code the other way around. So it will change pic 2 into pic 1. My bad (must be the headache).

Just one note for the code below: Make sure all 6 fields are entered or else it will mess up the order. If this doesn't work for you, then let me know which data (column letter(s)) is always entered.

Sub RunMe()
Sheets("Sheet2").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Range("B1").Value
Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Range("D1").Value
Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Range("F1").Value
Range("D" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Range("H1").Value
Range("E" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Range("J1").Value
Range("F" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Range("L1").Value
End Sub


Best regards,
Trowa
0
Hi Trowa
It works in getting the contents while the results are copied in the sheet 1 itself. I would like to copy them in sheet 2. can you pls tweak accordingly. I absolutely don't have any knowledge on the macros. Thanks for the support.
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
514
Jun 16, 2014 at 11:36 AM
But sheet 2 has all the data? and that data will be overwritten.

Can you tell me which sheet and range has the source data and which sheet and range is the destination?
0

Didn't find the answer you are looking for?

Ask a question
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Jun 13, 2014 at 12:59 AM
Paramesh
this is in connection with your reply in this thread. is there way any way that pics can be converted to excel. I always would like to work on excel sheet and test the macro I write, in replying to the threads in this newsgroup

thanks in advance
0
Paramesh77
Posts
3
Registration date
Saturday June 7, 2014
Status
Member
Last seen
June 13, 2014

Jun 13, 2014 at 11:54 PM
Hi Venkat
I dont see the possibility of attaching excel sheet in the reply. (not sure i am missing something here or not) and i can attach only picture files. Hope the attached will help


0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Jun 14, 2014 at 12:51 AM
I am asking members to upload excel file to some third party source like
speedyshare.comn
and send the download address

thanks for the reply
0