Expert: Adelaide carvalho - 4/11/2008

Question
I want to create a Macro that will Transpose my data from sheet1 to sheet2.
Input data:
Business_Date   CashDeposit   VisaMC   AmexCC   Discover
02-Jan-08   390.99   3,610.92   715.74   117.71
03-Jan-08   76.04   4,955.57   652.66   21.70
04-Jan-08   318.66   7,780.19   950.76   116.32
05-Jan-08   435.86   7,846.62   1,002.54   169.79
06-Jan-08   225.52   5,851.93   545.59   0.00
07-Jan-08   686.85   4,110.84   930.05   318.35

Output:
Business_Date   01-Jan-08   02-Jan-08   03-Jan-08   04-Jan-08   05-Jan-08   06-Jan-08   07-Jan-08
CashDeposit   1,397.73   390.99   76.04   318.66   435.86   225.52   686.85
VisaMC   6,058.57   3,610.92   4,955.57   7,780.19   7,846.62   5,851.93   4,110.84
AmexCC   602.71   715.74   652.66   950.76   1,002.54   545.59   930.05
Discover   69.98   117.71   21.70   116.32   169.79   0.00   318.35


Answer
Hi Learner_VB (or OnthewaytobeExpert_VB?

How are you?  


Please do the following

(I assume that your data is in A1:F7)

1.Select Sheet2

2. Select A1:G6)
Type on the left-upper  hand cell of this range

=Transpose(Sheet1!A1:F7)       
Press at the same time control Shift Enter

or
(you can type = Transpose(then go to sheet1 select the range A1:F5 type ) and press the 3 keys)


I hope this helps
Cheers
Adelaide Carvalho


Hi again !
oops .. I forgot the macro

Sub TransposeToSh2()
Dim R As Range, I As Integer, J As Integer
Dim R1 As Range, addr As String
Set R = Sheets(1).Range("A1:F7")
Set R1 = Sheets(2).Range("A1")
For I = 1 To R.Columns.Count
For J = 1 To R.Rows.Count
R1.Cells(J, I) = R.Cells(I, J)
Next J
K = K + 1
Next I
End Sub

You can try to transform this into a function :-)

Cheers
Adelaide