• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

Excel VBA script to transform spreadsheet

Hello Experts

I have attached an Excel workbook with two sheets: Current and Transformed.

I would like a script that will run on the Current sheet and then create a new sheet with the data in the same format as the example sheet "Transformed".

The Current sheet uses a merged field in the first column, so each record takes up 2 rows. IN the Transformed sheet, each record takes up only one row.

Thank you!

  • 3
  • 2
1 Solution
Hi Tom,
I'll do this for you after dinner. Please be patient for a little while longer.
OK. Here we go. The entire code is contained in a single module, "Transformer" which you can drag from the attached project to your own in the VB Editor's Project Explorer window. You can change the names of the tabs at the top of the code,
    Const SourceSheet As String = "Current"
    Const TargetSheet As String = "Transformed"
The Source sheet must be available in the workbook. The Target sheet will be created. If a sheet by the same name as the Target sheet already exists it will be deleted and completely rebuilt.
I tried to take good care of your formatting but didn't do everything that I might have. Please point out what you need. It is rather easy to modify the code to give you the formatting you want.
tomfolinsbeeAuthor Commented:
Thanks for this!

I'm going to post a follow up question to split the output into two other tables (so script will output 3 tables)

New tables:
tblFinish ( RowID, PartName, FinishName, BulkPrice)
tblPart (RowID, PartName, Weight)
PartName = column1
PartWeight = column2
PartName = row 1
BulkPrice = all the other cells
RowID = Row()

Post a link to the follow-up here so that I will be notified.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now