Looking for a faster method to transpose Column data to a row

[askpcguy]

I've got a workbook called MyBook1 which has 12 groups of 5 columns representing each calendar month. I am looking to create a macro (one for each month) which will be executed from another workbook. This macro will pull a column of data from a workbook called Data1 and paste into a row in the MyBook1 workbook.

Using the macro recorder, I've got the following code that works great but was trying to shorten the code and get it to work from another workbook but haven't been successful. I believe all that needs to be done is create a variable for the source/destination workbooks then set the selections and do the copy/paste but I'm having a hard time finding example macros which I can learn from.

Working code below for January, for Febuary the Data1 range is D2:D6, and the destination is 5 columns over making it M3. Then just follow the code below as a template.

Sub GetJanuaryData()

'Start
    Application.ScreenUpdating = False ' turn off the screen updating
    Windows("MyBook1.xlsm").Activate
    Windows("Data1.xls").Activate
    Range("C2:C6").Select
    Selection.Copy
    Windows("MyBook1.xlsm").Activate
    Range("H3:H3").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    'Next row.
    Windows("Data1.xls").Activate
    Range("C7:C11").Select
    Selection.Copy
    Windows("MyBook1.xlsm").Activate
    Range("H10:H10").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    'Next row.
    Windows("Data1.xls").Activate
    Range("C12:C16").Select
    Selection.Copy
    Windows("MyBook1.xlsm").Activate
    Range("H20:H20").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    'Next row.
    Windows("Data1.xls").Activate
    Range("C17:C21").Select
    Selection.Copy
    Windows("MyBook1.xlsm").Activate
    Range("H25:H25").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    'Next row.
    Windows("Data1.xls").Activate
    Range("C22:C26").Select
    Selection.Copy
    Windows("MyBook1.xlsm").Activate
    Range("H27:H27").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    'Next row.
    Windows("Data1.xls").Activate
    Range("C27:C31").Select
    Selection.Copy
    Windows("MyBook1.xlsm").Activate
    Range("H39:H39").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    'Next row.
    Windows("Data1.xls").Activate
    Range("C32:C36").Select
    Selection.Copy
    Windows("MyBook1.xlsm").Activate
    Range("H44:H44").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    'Next row.
    Windows("Data1.xls").Activate
    Range("C37:C41").Select
    Selection.Copy
    Windows("MyBook1.xlsm").Activate
    Range("H55:H55").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    'Next row.
    Windows("Data1.xls").Activate
    Range("C42:C46").Select
    Selection.Copy
    Windows("MyBook1.xlsm").Activate
    Range("H68:H68").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    'Next row.
    Windows("Data1.xls").Activate
    Range("C47:C48").Select
    Selection.Copy
    Windows("MyBook1.xlsm").Activate
    Range("H71:H71").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    'Next row.
    Windows("Data1.xls").Activate
    Range("C49:C53").Select
    Selection.Copy
    Windows("MyBook1.xlsm").Activate
    Range("H73:H73").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Selection(1).Select
    Application.CutCopyMode = False
End Sub

[Norie]

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

[askpcguy]

Hi Norie,

I just found out I can take the macro seen above and assign it to a button on the other Workbook. It works great.

I'll work on creating three example workbooks but basically the data is arranged as seen in the code above.

[apo]

Hi..

I'll work on creating three example workbooks but basically the data is arranged as seen in the code above.

That's probably a good idea.. it just makes it much easier for anyone to help you (we don't have to recreate your situation/data etc)..

You will most probably find that the code needed to do what you want is actually much less than what you have and maybe even will make more sense..

[askpcguy]

Here's a zip with the three workbooks. Master.xlsm contains the macros and buttons, the rest should be self-explanatory.

I am trying to make things as user-friendly as possible and don't want to put the macro/code into the Destination workbook.

(Going to upload another workbook soon)

[apo]

Hi..

I am trying to make things as user-friendly as possible and don't want to put the macro/code into the Destination workbook.

I can see how you might want a 'Control Panel' of sorts for the User.. but I am not sure why you would want an extra workbook just for the Control Panel.. why not just have it in the Destination Workbook on a seperate sheet?

That way, you're dealing with 2 Workbook, not 3.. makes sense?

[askpcguy]

Hi..
I can see how you might want a 'Control Panel' of sorts for the User.. but I am not sure why you would want an extra workbook just for the Control Panel.. why not just have it in the Destination Workbook on a seperate sheet?

That way, you're dealing with 2 Workbook, not 3.. makes sense?

It does make sense to not use an extra workbook. I'm only trying to keep things as simple as possible. What is unknown to me (until I ask) is how the Destination workbook will be used. I know there's charts and other data so that's why I went down the path of an extra workbook to handle the macro buttons.

[askpcguy]

Ok, I did a lot of find and replace but I got the macro all setup to copy the column of data and transpose it on the destination workbook at the correct row.

I just learned that the destination workbook has a chart and trying to make it a template of 12 months will break the chart! The chart's data is a rolling year. Meaning Aug-2013 to Aug-2014. Then Sept-2013 to Sept-2014. This also means my transpose macro won't work because the column headers (or maybe letters?) will be changing and I don't know how to code VBA to paste the data when the column lettering will be changing. Ugh! So back to the beginning for me.

[apo]

I think most of what you want to do is possible.. just trying to understand exactly what that is, that's the hard part..

Your best bet would be to attach the 'actual' destination Workbook you are using (including the chart etc), and attach again your exampledata workbook.

Changing header values can be coded for and sounds like your broken chart might be fixed using dynamic ranges or something like that.. don't give up.

[askpcguy]

I think most of what you want to do is possible.. just trying to understand exactly what that is, that's the hard part..

Your best bet would be to attach the 'actual' destination Workbook you are using (including the chart etc), and attach again your exampledata workbook.

Changing header values can be coded for and sounds like your broken chart might be fixed using dynamic ranges or something like that.. don't give up.

I'm going to attach a sample workbook with the columns and formulas in place. But it might be easier to post a new question.

[askpcguy]

Hi Apo, I'd love to upload the actual workbooks as a point of reference but have to make sure I'm allowed to do so.

In the destination workbook, the rows where data from exampledata workbook is transposed remains the same but the columns are going to increase by 5. Then the chart is simply updated to drop the last month and add the current/previous month.

[askpcguy]

I just learned today the requester is going to get the data directly from SQL, no excel so my question is closed. Thanks!