Help with creating a Macro that reads then writes to another worksheet, but transposes also! RRS feed

  • Question

  • Hi,

    I would like to create a Macro which reads data from the below

    Title (A1)

    Header 1(A3)      Header 2       Header 3          Header 4             Header 5               Header 6       Header 7

    Text(A4)             Number          Text                  Time                    Time                      Text               Text

    ""                        ""                    ""                     ""                         ""                         ""                   ""

    And so on as the user enters data....

    What I would like the Macro to do is this:

    Titles (A1)

    New Header       Header 3          Header 4          Header 5        Header 2           New Header            New Header         New Header

    Blank Col            Text                 Time                    Time               Number             New Text

                                                                                                                                                               New Header(H4)    DataHeader 1    

                                                                                                                                                               New Header(H5)    DataHeader 7

                                                                                                                                                               New Header(H6)   DataHeader 5

    Blank Col           Text                   Time                   Time             Number              New Text

    This pattern to be repeated. The DataHeader (#) means data from header (#).

    Ultimate apologies if the formatting is screwed when its posted.

    Below is the code I have managed myself but I'm stumped as to how to create empty blank lines and transpose the data as above.

    Sub CopyData()

    Worksheets.Add().Name = "Output"

        ActiveCell.FormulaR1C1 = "Title"
        ActiveCell.FormulaR1C1 = "New Header"
        ActiveCell.FormulaR1C1 = "Header 3"
        ActiveCell.FormulaR1C1 = "Header 4"
        ActiveCell.FormulaR1C1 = "Header 5"
        ActiveCell.FormulaR1C1 = "Header 2"
        ActiveCell.FormulaR1C1 = "New Header"
        ActiveCell.FormulaR1C1 = "New Header"
        ActiveCell.FormulaR1C1 = "New Header"
        ActiveCell.FormulaR1C1 = "New Header"

        Dim src As Range, dest As Range
        'set source, exclude first row
        Set src = Worksheets("Source").Range("A3").CurrentRegion.Offset(1, 0)
        'destination is one row below last row
        Set dest = Worksheets("Output").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        src.Copy Destination:=dest
    End Sub

    Of course I've changed the headers and actual data to preserve confidentiality.

    This code successfully brings all the inputted rows into a new worksheet, but of course doesn't reformat the columns into the order I require or transpose certain rows into columns.

    Thanks for any attempts at this - a loose vba structure would be nice.


    • Edited by cardinaluk Thursday, March 14, 2013 10:56 AM
    Thursday, March 14, 2013 10:51 AM

All replies