VBA: Reformat data exported from software to excel.

I have attached a file exported from an accounting software to excel which inserts the data and merges cells unnecessarily.

Can someone do a macro for me to convert this format to a more excellish layout?
Reformat-software-output.xls
LVL 44
Saqib Husain, SyedEngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

save the file as CSV. Close and re-open. Now you can easily delete empty rows and columns.

cheers, teylyn
Saqib Husain, SyedEngineerAuthor Commented:
Thanks, teylyn but I need a macro to do this. I get such a file frequently and I want to put a button on my ribbon which will simply straighten it.
Saqib Husain, SyedEngineerAuthor Commented:
Also, it would be good if the macro can do a sum of numbers which can be compared with the provided totals.
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

ButlerTechnologyCommented:
Can you provide a mock up of what the final results should look like?
Saqib Husain, SyedEngineerAuthor Commented:
It should look just the same - but without the extra columns and merged cells
ButlerTechnologyCommented:
Do you want the merged cells removed starting at B10?
Saqib Husain, SyedEngineerAuthor Commented:
Ok I have done a manual format.


***** File edited ********
Desired-format.xls
ButlerTechnologyCommented:
Try the following macro -- it is close minus some formatting (I think)

Public Sub Reformat()

Range("B10").Activate
Do
  With ActiveCell
  .Range("R1").Value = .Range("A1").Value
  .Range("S1").Value = .Range("F1").Value
  .Range("T1").Value = .Range("K1").Value
  .Range("U1").Value = .Range("O1").Value
  End With
  ActiveCell.Offset(1, 0).Activate
Loop Until IsEmpty(ActiveCell)

' Labels
Range("S1").Value = Range("B1")
Range("S2").Value = Range("B2")
Range("S3").Value = Range("B3")
Range("S4").Value = Range("B4")
Range("S7").Value = Range("B7")
Range("v7").Value = Range("Q7")

' Totals
  ActiveCell.Offset(1, 0).Activate
  ActiveCell.Range("T1").Value = ActiveCell.Range("K1").Value
  ActiveCell.Range("U1").Value = ActiveCell.Range("O1").Value
  
Range("S1").Activate
Range("A1:R1").EntireColumn.Delete
Range("A1").Activate

' Column Widths
  Range("A1").ColumnWidth = 15
  Range("B1").ColumnWidth = 30
  Range("C1").ColumnWidth = 15
  Range("D1").ColumnWidth = 15
  
  Range("A1:D1").Merge
  Range("A2:D2").Merge
  Range("A3:D3").Merge
  Range("A4:D5").Merge
  Range("A1:D5").HorizontalAlignment = xlCenter
  
  Range("A8").EntireRow.Delete
  Rows("9:9").Font.Bold = True
  
  Columns("C:D").NumberFormat = "$#,##0"
  
  Range("A9").Activate

Do
  ActiveCell.Offset(1, 0).Activate
Loop Until IsEmpty(ActiveCell)
ActiveCell.Offset(2, 2).Activate

Dim Top As Integer
  Top = ActiveCell.Row - 10
  ActiveCell.FormulaR1C1 = "=SUM(R[-" & Top & "]C:R[-3]C)"
  ActiveCell.Offset(0, 1).Activate
  ActiveCell.FormulaR1C1 = "=SUM(R[-" & Top & "]C:R[-3]C)"
  Range("A1").Activate
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saqib Husain, SyedEngineerAuthor Commented:
Perfect

Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.