You can do the transposing without any macro:
The direct way for your first step would be to use the
TRANSPOSE function, i.e. select a range that is 365rowsx24cols and enter
=TRANSPOSE(B4:NB27) and press Ctrl+Shift+Enter.
However, I'd recommend to address problem 1 and 2 in a slightly different manner: Translate your data to a list, i.e. a table with 365x24 rows x 3 columns: Date, Hour, Value. This table can then be easily be used as the source for a pivot table!
To do so, you could either use two helper columns - the first one starting at 1 and increasing every row by one until it's 24 then reset (In A2:
1, in A3:
=IF(A2=24,1,A2+1)), the second starting with 1 and increasing by 1 every 24 rows (B2:
=IF(A3=1,B2+1,B2). Alternatively, you could simply use the
ROW() function (which returns you the row of the cell with the formula if no parameter is provided) to derive the number 1 to 24 with
MOD(ROW(),24)-1 and the 1 to 365 with
-2 is to adjust for the data table starting in row 2).
INDEX function with those two parameters, you can now easily retrieve the data for the 3 columns:
=INDEX($B$3:$NB$3,B2) (or without the helper columns: