Solved: Format Data

[Pete]

See attached workbook.
Hi Experts.

If my explaination is not clear just say so on this question, i am going to break it down

First point please look at the worksheet "Original_Data". This is the data in it raw format. I want the macro to do the following steps so the first stage looks like the second worksheet "Test_Table1".

1. Shift data down two rows to add table headings as shown in worksheet "Test_Table1".

2. Highlight row 4 as shown in worksheet "Test_Table1"

3. Move the data so that Baseline_Finish and Finish are directly under columns E and F.

4. Move Text 6 and text 26 to sit under columns H and I.

[Bob Phillips]

Where does column A and B come from?

[Pete]

Hi

Column A and B come from a MicroSoft Project file, i am trying to produce a report in excel as MS Project is a complete pain to work with.

[Bob Phillips]

So this code would just ignore them?

Agree about MS Project. I have a client that wants an Excel planner, much like MS Project, but simpler to work with.

[Pete]

hi Xld
So this code would just ignore them? - if i understand your question correctly and then YES.

[Bob Phillips]

Sub Format_Data()
Dim shOriginal As Worksheet
Dim aryCols As Variant
Dim LastRow As Long
Dim NextRow As Long
Dim PrevValue As String
Dim i As Long
Dim tmp

Application.ScreenUpdating = False

Set shOriginal = Worksheets("Original_Data")

aryCols = Array(20, 107, 20.86, 14.71, 14.71, 14.71, 12.14, 5.43, 8, 12.14, 12.14)
With Worksheets("Task_Table1")

shOriginal.Cells.Copy .Range("A1")
.Columns("D").Insert
.Rows("1:2").Insert

For i = LBound(aryCols) To UBound(aryCols)

.Columns(i - LBound(aryCols) + 1).ColumnWidth = aryCols(i)
Next i

With .Rows(1)

.Value = Array("TCIP Ref.", "NAME", "REGION", "OWNER", "BASELINED FINISH", "FORECAST FINISH", _
"PROPOSED CHANGE TO FINISH DATE", "DEP", "RAG previous report", _
"RAG reported in this report", "COMMENTS")
.RowHeight = 38.25
.Font.Bold = True
.Interior.ColorIndex = 6
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
End With
With .Rows(2)

.Value = Array("Text 8 (from the TCIP plan)", "Name", "Text 23", "Manual input / no export", _
"Baseline Finish", "Finish", "Manual input / no export", "Text 6", "Text 26", _
"Manual input / no export", "Manual input / no export")
.RowHeight = 25.5
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
End With
.Range("D2,G2,J2:K2").Font.Bold = True
.Range("D2,G2,J2:K2").Font.ColorIndex = 5
.Rows(4).Interior.ColorIndex = 35
End With

Application.ScreenUpdating = True
End Sub

[Pete]

thanks for the excellent feedback.

xld