Excel 2007 : Transposing data with mirror flip

Hi All,
I just registered. I hope to share a LOT of information with others and every once in a while ask a question that I just can't seem to wrap or warp my head around. This is one of those cases... DUN DUN

How I arrived here:
I have a trending history of data going back, weekly, to Jan 2009. The data is a weekly report I produce and I have been okay with manually adding an extra column to the end of the data as, "at the time", it was faster than building a macro to do it for me. The report has become a bit crazy now and I will be adding a macro to do this for me, however, I want to add the data by inserting a column at the beginning instead. Not a big deal, except for all the data I already have trended.

Question:
I want to transpose the information so that the most recent date is now at the beginning of the columns and the oldest date is at the end. Is there an easy way to do this?

I played with the Transpose function of the paste values however that is not working. I was thinking of building a vlookup and hlookup to grab the data based on the first row of dates and build it that way, however, that means I still have to manually put in the dates for each week I want to pull in data from.

Ok, I will shut it now for your responses. Thanks so much in advance!

Jim (aka AnalystnotAnal)
Excel Intermediate
VB Novice (not a beginner)

Hi Jim.

Create a sample workbook showing the actual data layout BEFORE and what you want it to be AFTER. With that, it should be simple enough to create a macro to do that for you automatically.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

Absolutely. I should have done that in the first place. Thanks.

Hi Jim, perhaps try:

Sub Macro1()
Dim i As Long, j As Long, k As Long
Application.ScreenUpdating = False
i = Cells(1, Columns.Count).End(xlToLeft).Column
k = 1
For j = i - 1 To 1 Step -1
    Cells(1, i).EntireColumn.Cut
    Cells(1, k).Insert Shift:=xlToRight
    k = k + 1
Next j
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Assumes dates are in row 1 (the "Before" and "After" headings are removed).

Try this:

Option Explicit

Sub Mirror()
Dim Cols As Long, Col As Long
Application.ScreenUpdating = False

Cols = Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Column - 1

For Col = Cols To 1 Step -1
    Columns(Col).Copy Cells(2, Columns.Count).End(xlToLeft).Offset(-1, 1)
Next Col

Range("A1", Cells(1, Cols)).EntireColumn.Delete xlShiftToLeft
Application.ScreenUpdating = True
End Sub

==========
How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

JB - Your code works the same as Pauls? Or how does it differ? I was able to make Pauls work, however, I have to copy the data to a blank worksheet so it is looking at A1 through column.count. Can I modify this to work at wherever my cursor is positioned? For example, almost always the data begins on a worksheet at D3. And I don't want to copy the entire column, just the cells in the row.count that you see in my example. They don't get any longer. So for example D3 : BQ19 would be the amount of data to flip ends on.

Try this (it uses the active cell as the starting point, so be sure to have that cell selected or you're going to have a mess of your data):

Sub Macro2()
Dim startCol As Long, endCol As Long, startRow As Long, endRow As Long
Dim i As Long
Application.ScreenUpdating = False
startCol = ActiveCell.Column
startRow = ActiveCell.Row
endCol = Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column
endRow = startRow + 16

For i = endCol - 1 To startCol Step -1
    Range(Cells(startRow, endCol), Cells(endRow, endCol)).Cut
    Cells(startRow, startCol).Insert Shift:=xlToRight
    startCol = startCol + 1
Next i

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Worked like a charm. Thanks Paul and JB. Great teamwork!