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!

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.

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.

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
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.
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.