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.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

RudiS

Active Member
Joined
May 7, 2015
Messages
349
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???
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,712
Office Version
  1. 2010
Platform
  1. Windows
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.
 

ijaraptor

New Member
Joined
Jan 8, 2010
Messages
14
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:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,712
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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?
 

RudiS

Active Member
Joined
May 7, 2015
Messages
349
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
 

ijaraptor

New Member
Joined
Jan 8, 2010
Messages
14
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,712
Office Version
  1. 2010
Platform
  1. Windows
...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
 

ijaraptor

New Member
Joined
Jan 8, 2010
Messages
14
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,323
Messages
5,600,954
Members
414,417
Latest member
Nobu

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
Top