Reformatting excel data in a specific way

Question

I have an excel sheet that has say, 10 different columns, with a few hundred records.

e.g.

column1|column2|column3|column4

data    data    data    data

I have another sheet, a template, which has the headings arranged in a certain way, e.g.

column1|column2
data    data
        column3   column 4
        data      data

So, the template block in my second sheet has to be copied and filled in for each record.

Is there a way to do this with VBA?

I am aware this is a horrible way of doing things, however I can not convince my superior of that, and so this is all I can try and do. If this is not possible, it will just be done by hand, so I am hoping some automation is possible.

Tim Williams · Accepted Answer · 2016-01-18 18:37:12Z

1

Try this out:

Sub Tester()

    Const SHT_SRC As String = "Sheet1" 'sheet w source data
    Const SHT_DEST As String = "Sheet2" 'sheet w template
    Const RNG_COPY As String = "A1:E6" 'your template area

    Dim rngDest As Range, rngSrc As Range, rngCopy As Range

    Set rngCopy = ThisWorkbook.Sheets(SHT_DEST).Range(RNG_COPY)
    Set rngDest = rngCopy.Cells(1)
    Set rngSrc = ThisWorkbook.Sheets(SHT_SRC).Rows(2)

    Do While rngSrc.Cells(1).Value <> ""

        rngCopy.Copy rngDest 'copy template area
        With rngDest
            'adjust offsets to fit your template layout
            .Offset(1, 0).Value = rngSrc.Cells(1).Value
            .Offset(1, 1).Value = rngSrc.Cells(2).Value
            '...etc etc
            .Offset(5, 5) = rngSrc.Cells(10).Value
        End With

        Set rngDest = rngDest.Offset(rngCopy.Rows.Count + 1, 0)
        Set rngSrc = rngSrc.Offset(1, 0)
    Loop

End Sub

edited Jan 18, 2016 at 18:37

answered Nov 15, 2010 at 22:09

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Donald Love Over a year ago

Hmm, thanks having a look at this now. The template is actually in a different file...does that make a big difference? I cana import it is a sheet so both are in one file I guess. I am also unsure how this works with different field names and such...should I upload a sample to show what I mean?

Tim Williams Over a year ago

If the template is in a different file, do you also want the reformatted data in that file ? Try: Set rngCopy = Workbooks("otherworkbook").Sheets(SHT_DEST).Range(RNG_COPY) Tim

Lance Roberts · Accepted Answer · 2010-11-15 18:12:03Z

0

You can read the range into an array, then parse it out via individual elements:

Dim dataArray As Variant
Dim i As Integer

dataArray = Range("B1:B4").Value

For i = 1 to Ubound(dataArray)/2
  Range("B2").Offset(2 * (Ceiling(i/2)-1), Ceiling((i-1)/2)) = dataArray(1, i)
Next i

Using this function:

Public Function Ceiling(ByVal X As Double) As Integer

  Ceiling = Int(X) - (X - Int(X) > 0)

End Function

answered Nov 15, 2010 at 18:12

Lance Roberts

22.9k32 gold badges115 silver badges132 bronze badges

2 Comments

Donald Love Over a year ago

The template it a seperate file...also I am not sure about ranges...is there a way just to do it for each line?

Lance Roberts Over a year ago

@Donald, you could just address the other workbook if it's in a separate file. I don't know what you mean by 'do it for each line', my code is doing it for each line. You just have to set the ranges right, My example ranges were based on your included sample, just change them to what you need.

Collectives™ on Stack Overflow

Reformatting excel data in a specific way

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related