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.

 

[Rick Rothstein]

Subscript out of range when running that macro.

That means I spelled one, or both, of the sheet names differently from the way you spelled them on the tab... correct the spelling (that includes any spaces you might have tacked on the beginning or end of the name on the tab) and the code should then work.

 

[ijaraptor]

That means I spelled one, or both, of the sheet names differently from the way you spelled them on the tab... correct the spelling (that includes any spaces you might have tacked on the beginning or end of the name on the tab) and the code should then work.

Awesome. That worked great. Thank you so much

 

[RudiS]

Thanks for the clarity in your last post...

Here is the code to do what you need:

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, Transpose:=True
    Else
        i = Sheets("Historical").Cells(Rows.Count, 1).End(xlUp).Row + 1
        Sheets("Historical").Cells(i, 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
    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

Oops.... I didn't see this thread had a page two and that Rick had long time ago resolved this!! Sorry Rick.
I'll leave the code up anyways for anyone who can use it