Transpose Columns to rows vba help

ijaraptor

New Member
Joined
Jan 8, 2010
Messages
14
Hello.

I have a particular sheet named "historical" (without quotes).

It contains Data in Cells A1:A5 - I have a macro running that auto populates cells A1-A5 and then in five minutes it goes to the next column and creates data in B1:B5 and so on. This runs for a week week and I start new every week.

I need to get some vba code that will automatically transpose the data that was in !Historical A1:A5 to Cells B1:B5 in the !Export Sheet and down the line.

Please help.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
To clarify...

You want Historical!A1:A5 to paste into Export!B1:B5
Then Historical!B1:B5 to paste into Export!B6:B10
Then Historical!C1:C5 to paste into Export!B11:B15
...
...

Is this accurate???
 
Upvote 0
I need to get some vba code that will automatically transpose the data that was in !Historical A1:A5 to Cells B1:B5 in the !Export Sheet and down the line.
How is A1:A5 to B1:B5 considered, as you thread's title says, a "transpose [of] columns to rows"? I suspect you meant something other than what you posted... please clarify.
 
Upvote 0
How is A1:A5 to B1:B5 considered, as you thread's title says, a "transpose [of] columns to rows"? I suspect you meant something other than what you posted... please clarify.


Hi Rick. Thanks for the reply. I should have clarified better.

A1:A5 contains a set of numbers in A1, A2, A3, A4 is a Date (1/20/2015) , and A5 is a Timestamp (04:24:35PM). All of this is pulled from a !Loaddata sheet that refreshes cells A1:A5 every 5 minutes based on external text file. I have a module that checks for changed numbers on another sheet (!Loaddata) for these 5 variables every 5 minutes. After this time it posts them in B1:B5. On the next change it posts them to C1:C5. It might be easier to change my VBA and paste the data from the start rather than paste in to the column view that I don't want. My module I have included a start timer and stop timer and a button that will start and stop the module.

Here is my code for the module that extracts the data.

Code:
Option Explicit
Public dTime As Date

Sub ValueStore()
Dim dTime As Date
Dim i As Long
    Sheets("LoadData").Range("A1:A5").Copy
    If Sheets("Historical").Cells(1, 1) = "" Then
        Sheets("Historical").Cells(1, 1).PasteSpecial Paste:=xlPasteValues
    Else
        i = Sheets("Historical").Cells(1, Columns.Count).End(xlToLeft).Column + 1
        Sheets("Historical").Cells(1, i).PasteSpecial Paste:=xlPasteValues
   End If
    
    Call StartTimer
    
End Sub


Sub StartTimer()
    dTime = Now + TimeValue("00:05:00")
    Application.OnTime dTime, "ValueStore", Schedule:=True
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime dTime, "ValueStore", Schedule:=False
End Sub


In essence it copies A1:A5 from !Loaddata into The !Historical sheet Cells A1:A5 and after 5 minutes it pastes A1:A5 from !Loaddata into cells B1:B5 in the !Historical sheet.

It copies fine, but I want it to build out vertically down rows instead of Horizontally across Columns.
 
Last edited:
Upvote 0
In essence it copies A1:A5 from !Loaddata into The !Historical sheet Cells A1:A5 and after 5 minutes it pastes A1:A5 from !Loaddata into cells B1:B5 in the !Historical sheet.

It copies fine, but I want it to build out vertically down rows instead of Horizontally across Columns.
Explain that last sentence above in a little more detail please. Nothing you have posted yet show what you would mean by "build out vertically down rows"... what is building out vertically?
 
Upvote 0
Try this:

Change the lines:
i = Sheets("Historical").Cells(1, Columns.Count).End(xlToLeft).Column + 1
Sheets("Historical").Cells(1, i).PasteSpecial Paste:=xlPasteValues

To this:
i = Sheets("Historical").Cells(Rows.Count, 1).End(xlUp).Row + 1 'or + 2 if you want a gap between them...
Sheets("Historical").Cells(i, 1).PasteSpecial Paste:=xlPasteValues
 
Upvote 0
That copies it down the rows, but places it all in column A.

I would like A1 to go to A1, A2 to go to B1, A3 to go to C1, A4 to copy to D1, and A5 to copy to E1



Here is what it did before

Number1 Number1 Number1 Number1
Number2 Number2 Number2 Number2
Number3 Number3 Number3 Number3
Date Date Date Date
Time Time Time Time

After changing that line of code I get this. (On the right track, but not quite there.

Number1
Number2
Number3
Date
Time

Number1
Number2
Number3
Date
Time


and I want it to look like this:

Number1 Number2 Number3 Date Time
Number1 Number2 Number3 Date Time
 
Upvote 0
...and I want it to look like this:

Number1 Number2 Number3 Date Time
Number1 Number2 Number3 Date Time
I think I see what you are after now. Give this macro a try (it will copy the last column of data on the "historical" sheet and place it (transposed) underneath the last row of data on the !Export sheet)...
Code:
Sub MoveLatestHistoricalSheetToExportSheet()
 Sheets("!Export").Cells(Rows.Count, "A").End(xlUp).Resize(, 5).Offset(1) = _
 Application.Transpose(Sheets("historical").Cells(1, Columns.Count).End(xlToLeft).Resize(5))
End Sub
 
Upvote 0
I think I see what you are after now. Give this macro a try (it will copy the last column of data on the "historical" sheet and place it (transposed) underneath the last row of data on the !Export sheet)...
Code:
Sub MoveLatestHistoricalSheetToExportSheet()
 Sheets("!Export").Cells(Rows.Count, "A").End(xlUp).Resize(, 5).Offset(1) = _
 Application.Transpose(Sheets("historical").Cells(1, Columns.Count).End(xlToLeft).Resize(5))
End Sub


Subscript out of range when running that macro.
 
Upvote 0

Forum statistics

Threads
1,218,811
Messages
6,144,610
Members
450,559
Latest member
kwenda farai

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top