Link to home
Start Free TrialLog in
Avatar of Pabilio
PabilioFlag for Spain

asked on

EXCEL TRANSPOSE AND CREATE NEW WORKBOOK WITH FORMAT

Hi,

I need to create a new workbook (or several workbooks) with the data showed in the attached file: "WOKBOOK DATA".

The goal is to create a single sheet (Or workbook) for EACH row with information from WORKBOOK DATA (starting at row 7) with the format that you could see in the other attached file here called "RESULT".

WORKBOOK DATA contains in Row 6 the Titles that Must go in column A in the RESULT file... and the information located in row 7 at WORKBOOK DATA (and next rows) must go in column B.

The simple file RESULT has the target range, where the information must go, highligthed in yellow.

I appreciatte your help and time,

Regards,
Roberto.
WORKBOOK-DATA.xls
RESULT.xls
Avatar of byundt
byundt
Flag of United States of America image

Roberto,
Here is a macro that will create a new workbook, then add detail worksheets for each row of data in worksheet LISTA DE PRECIOS. It will name those detail worksheets after the Name value from column B. Worksheet names are truncated to 31 characters and / (illegal character in worksheet names) is replaced with _. As written, the macro assumes a template worksheet named Detail Template is located in the workbook containing the macro.
Sub DetailSheets()
Dim ws As Worksheet, wsMaster As Worksheet, wsTemplate As Worksheet
Dim rg As Range, rw As Range
Dim wb As Workbook
Dim i As Integer, n As Integer
Application.ScreenUpdating = False
Set wsMaster = Worksheets("LISTA DE PRECIOS")       'Get data from this worksheet
Set wsTemplate = Worksheets("Detail Template")      'Make a copy of this worksheet as a detail sheet for each row in wsMaster
Set wb = Workbooks.Add                              'Detail sheets will go here
n = wb.Worksheets.Count
With wsMaster
    Set rg = .Range("A7")       'First datum to be listed on detail sheet
    Set rg = Range(rg, .Cells(.Rows.Count, rg.Column).End(xlUp)) 'All the data in that column
    Set rg = Intersect(.UsedRange, rg.EntireRow) 'All the data, all columns
End With
For Each rw In rg.Rows
    wsTemplate.Copy After:=wb.Worksheets(wb.Worksheets.Count)
    Set ws = ActiveSheet
    With ws
        .Name = Replace(Left(rw.Range("B1").Value, 31), "/", "_")
        .Range("B7:B9").Value = Application.Transpose(rw.Range("A1:C1").Value)
        .Range("B12:B33").Value = Application.Transpose(rw.Range("D1:Y1").Value)
    End With
Next
Application.DisplayAlerts = False
For i = 3 To 1 Step -1
    wb.Worksheets(i).Delete
Next
Application.DisplayAlerts = True
End Sub

Open in new window

Brad
WORKBOOK-DATA-Q28313448.xls
Avatar of Pabilio

ASKER

Dear Brad,

Hi Brad,

Thank you VERY much for your help... it Works exactly as I need and it will help me A LOT in my daily work.

There is only a Little detail and I'm not sure if it is for Excel 2003 limitations or what.

The code runs perfectly untill row 118... so it creates 111 sheets as I need but then it crash and shows the error message 1004 (error in method copy of the object _worksheet)

I tried deleting row 118 in case there was a problem with names or values on that row but it happens anyway.

I could live doing two steps to have all files done, but if you know how to fix this bug please just let me know.

Thanks again for this REALLY great work.

Roberto.
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Pabilio

ASKER

Hi Brad,

I'm sorry for my mistake not posting the Excel versión.

Thank you very much for your time and support.

Best regards,
Roberto.