Help on macro

[vinayashwitha]

Hi,

I have below format in excel and i will be updating scores for each paramater (column B).

I Need help on the macro. I will be inserting a command/click button and when i click on the button macro should take the name, contact No., scores from column B and paste it in sheet 2 in the same workbook in one row, in the same way when i click the button the scores should be taken from sheet 1 column b and paste one below other row in sheet 2 each time i click on button.

Below is how raw data looks like.

Column A</SPAN>	Column B</SPAN>
Name:</SPAN>	ABC
Contact Number:</SPAN>	XYZ
Parameter</SPAN>	Score</SPAN>
Parameter A</SPAN>	2</SPAN>
Parameter B</SPAN>	5</SPAN>
Parameter C</SPAN>	6</SPAN>
Parameter D</SPAN>	7</SPAN>
Total Score</SPAN>	5</SPAN>

<TBODY>
</TBODY>

Output expected in sheet 2 is as below.

Name:</SPAN>	Contact Number:</SPAN>	Parameter A</SPAN>	Parameter B</SPAN>	Parameter C</SPAN>	Parameter D</SPAN>	Total Score</SPAN>
ABC</SPAN>	XYZ</SPAN>	2</SPAN>	5</SPAN>	6</SPAN>	7</SPAN>	5</SPAN>

<TBODY>
</TBODY>

I am using Excel 2010.

Thanks in advance.

Regards
Vini

 

[Andrew Poulsom]

What have you tried?

 

[nuked]

Option Explicit

Sub TransposeData()
Dim wsSource As Worksheet, wsDestination As Worksheet

Set wsSource = ActiveWorkbook.Sheets(1)
Set wsDestination = ActiveWorkbook.Sheets(2)

wsSource.Range("A1:A" & wsSource.Range("A" & wsSource.Rows.Count).End(xlUp).Row).Copy
wsDestination.Range("A1").PasteSpecial Transpose:=True

End Sub

 

[vinayashwitha]

Hi,

I am using below code, but problem is that everytime i trigger the macro data is getting pasted in the same row.

I want macro to paste data one below the other row everytime i trigger the macro.

Thanks in advance.

Sub RoundedRectangle5_Click()
'
' RoundedRectangle5_Click Macro
'
' Keyboard Shortcut: Ctrl+a
'
Range("R5:R18").Select
Selection.Copy
Sheets("Data").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
Range("A4").Select
Sheets("Form").Select
Range("B5").Select
End Sub

Regards
Vini

 

[Andrew Poulsom]

Try:

Sub RoundedRectangle5_Click()
'
'   RoundedRectangle5_Click Macro
'
'   Keyboard Shortcut: Ctrl+a
'
    Dim NextRow As Long
    Range("R5:R18").Copy
    With Sheets("Data")
        NextRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
        .Range("A" & NextRow).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=True
    End With
End Sub

I removed the unnecessary Selects.

 

[vinayashwitha]

thanks a lot it is working fine.

Regards
Vini