Transpose Columns to rows vba help

[ijaraptor]

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.

 

[RudiS]

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]

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]

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.

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.

 

[Rick Rothstein]

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]

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]

This is the !LoadData sheet
https://www.dropbox.com/s/h7cfkr7bd5i7y0g/1.JPG?dl=0

It copies to here and I want it to build down rows instead of across columns.

https://www.dropbox.com/s/shne74scoti3axq/2.JPG?dl=0

 

[ijaraptor]

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]

...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)...

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]

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)...

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.