Solved: Design of reformat macro

[jwise]

I need to write a macro which will reformat about 650 rows of data into 41 separate worksheets. Here's my idea:
(1) Open workbook/worksheet which contains only worksheet template. Copy this template.
(2) Open workbook which has data.
(3) Copy the first set of data (actually 14 rows) and reformat.
(4) Set the name of the worksheet to a field in the row.
(5) Close new worksheet
(6) Loop to next set of data.
(7) There are 41 sets of data in the data worksheet.

Obviously, the Open in step (2) is not in the real loop.

Questions: (1) How do I have multiple worksheets open at the same time?
(2) How do I keep them separate, i.e. "Cells(3,2) = Cells(5,6)" would copy the data on the same worksheet? (3) The reason for the "template" is that there are formulas etc. for derived values once the data is moved to the new worksheet. Is there a better way to do this?

TIA

[Bob Phillips]

Only have one open at a time (2 actually with the original).

(1) Open workbook which has data.
(2) Open workbook template.
(3) Loop
(4) Clear the template worksheet of data
(5) Copy the next set of data to the worksheet template
(4) Set the name of the worksheet to a field in the row.
(5) Save template as new file name
(6) next set of data until end condition
(7) Close template workbook

[jwise]

Thanks for the fast response.

I see that your logic is more straightforward.

How do I address the two separate workbooks? To move cell A3 on sheet1 to cell B6 on sheet2 (and they are in separate workbooks), how do I modify the statement "Cells(3,1) = Cells(6,2)"?

I think this is where my OBJECT knowledge is severly lacking.

TIA

[Charlize]

I would make four objects. Two for the workbooks and two for the sheets.
[vba]Dim wbstart as workbook, wbmodel as workbook
Dim wbstartsheet as worksheet, wbmodelsheet as worksheet
Set wbstart = ActiveWorkbook
Set wbstartsheet = wbstart.Worksheets(1)
'The data workbook has been saved before !
'Both workbooks reside in the same directory.
Set wbmodel = Workbooks.Open(wbstart.Path & "\Model.xls")
set wbmodelsheet = wbmodel.Sheets(2)[/vba]
Charlize

[Bob Phillips]

Thanks for the fast response.

I see that your logic is more straightforward.

How do I address the two separate workbooks? To move cell A3 on sheet1 to cell B6 on sheet2 (and they are in separate workbooks), how do I modify the statement "Cells(3,1) = Cells(6,2)"?

I think this is where my OBJECT knowledge is severly lacking.

TIA

Set object variables to point at them as you open them

Set oWBPrimary = Workbooks.Open(Filename:="c:\myFile.xls")
Set oWBTemplate = Workbooks.Open(Filename:="C:\Template.xls")

and use those variables in the ensuing code.

You can also add sheet variable, circa

Set oShPrimary = oWBPrimary.Worksheets("Data")

and so on.

[jwise]

Thanks again to Charlize and XLD.

I'm trying to put this together. Is this close to your suggestion?

[vba]Dim wbInput, wbTemplate As Workbook
...
Set wbInput = Workbooks.Open(Filename:="c:\data.xls")
Set wbTemplate = Workbooks.Open(Filename:="c:\templ.xls")
...
wbTemplate.Cells(6,3) = wbInput.Cells(2,4)
...[/vba]

At the bottom of the loop, I would save the template with a new name. I would clear the modified values in the template, and iterate.

Is this close?

[Charlize]

Something like this (not tested and not a loop. This is just for one cell)
[VBA]Sub test()
Dim wb As Workbook
Dim ws As Worksheet
Dim destwb As Workbook
Dim destws As Worksheet
Dim rng As Range
Dim dest As Range
'the data workbook is already open
'we run the macro from this workbook
Set wb = ActiveWorkbook
Set ws = wb.Sheets(1)
Set destwb = Workbooks.Open("c:\data\template.xls")
Set destws = destwb.Sheets(2)
Set rng = ws.Cells(2, 4)
Set dest = destws.Cells(6, 3)
rng.Copy dest
End Sub[/VBA]Charlize

[jwise]

I really appreciate the code fragment. It answers a lot of questions.

I know very little about spreadsheets in general, but I have read from several sources that this kind of problem is solved by pivot tables. I have not studied this at all- I am just saying what I read.

The source file contains many rows on multiple properties (41 of them), and I am essentially changing the data from "row orientation" to "column orientation", and splitting each property into its own worksheet. This I have no control over.

Perhaps I can figure a way to combine ranges; otherwise this will take a huge number of statements! This is still better than doing it by hand!

Thanks again.

[Bob Phillips]

Thanks again to Charlize and XLD.

I'm trying to put this together. Is this close to your suggestion?

[vba]Dim wbInput, wbTemplate As Workbook
...
Set wbInput = Workbooks.Open(Filename:="c:\data.xls&quot
Set wbTemplate = Workbooks.Open(Filename:="c:\templ.xls&quot
...
wbTemplate.Cells(6,3) = wbInput.Cells(2,4)
...[/vba]

At the bottom of the loop, I would save the template with a new name. I would clear the modified values in the template, and iterate.

Is this close?

Nearly, but not quite.

You have to work your way down the object tree - Workbook - Worksheet - Range.

So you need something like

[vba]Dim wbInput, wbTemplate As Workbook
...
Set wbInput = Workbooks.Open(Filename:="c:\data.xls&quot
Set wbTemplate = Workbooks.Open(Filename:="c:\templ.xls&quot
Set shInput = wbInput.Worksheets("Input")
Set shTemplate = wbTemplate.Worsheets(1)
...
shTemplate.Cells(6,3) = shInput.Cells(2,4)
...[/vba]

[mdmackillop]

A common misunderstanding. The following only Dims the second variable as a workbook,
wbInput will be dimmed as Variant. You need to be specific with each item.

[VBA]Dim wbInput, wbTemplate As Workbook [/VBA]

[jwise]

MDMACKILLOP. I had no idea the "Dim" statement worked that way. Thanks.

[jwise]

Referring back to XLD's last post, I can't figure out how to save the template with the desired name. Restated, I'm reading a spreadsheet with multiple sets of data (14 rows in each set), and changing this data into individual worksheets. I am manufacturing the names of these worksheets. I have one pre-existing worksheet (Templ) which is a template.

XLD's logic shows me how to copy from the "DATA" sheet to the Templ sheet. But I must rename this worksheet and continue to march through the DATA rows.

The real reason for this is that the data is being changed from "row" organization to "column" organization, and is being changed from existing in 1 sheet to 41 sheets.

How do I rename Templ to A01 so I can copy the next set of data to this same template to rename it A02, etc?

[mdmackillop]

Can you be quite clear about renaming Templ as A01 etc. Is it your intention to have multiple templates named A01, A02 etc. or Workbooks based on the original template named A01, A02 etc.?

[jwise]

Thank you for addressing this issue. I apologize for its ambiguity.

The original data is contained in one spreadsheet, and it contains information on 41 properties. The task is to transform this large worksheet into 41 worksheets (one sheet for each property), with the data being changed from "row orientation" to "column orientation"

To restate:

Input is 1 large worksheet.

Output is 41 small worksheets, with the data transformed from row orientation to column orientation.

There are both data and formulas in this template. That is the reason for using a template. Once the data items are inserted, then the formula cells will derive other numbers.

Thanks

[mdmackillop]

Here's a basic solution
[VBA]Sub Macro1()
Dim Source As Workbook
Dim WB As Workbook
Set Source = ActiveWorkbook
Set WB = Workbooks.Add(Template:="C:\Templates\Templ.xlt")
Set tgt = WB.Sheets(1).Range("A1")
Application.ScreenUpdating = False
For i = 1 To 640 Step 14
Rows("1:14").ClearContents
k = k + 1
Source.Sheets(1).Rows(i).Resize(14).Copy tgt
WB.SaveCopyAs Source.Path & "A" & Format(k, "00") & ".xls"
Next
WB.Close False
Application.ScreenUpdating = True
End Sub
[/VBA]

[jwise]

Thanks again for looking at my problem and proposing a solution.

It appears that I have misled you again. I do apologize. I am unable to understand your code.

The code which moves the data from my "input" workbook/worksheet to the destination workbook works properly. The destination workbook is not NAMED correctly. I wind up with a destroyed template, and only the last set of data.

My objective was that the destination workbook would have 42 worksheets. The first worksheet is the template and is named "Templ". The next 41 worksheets are named A01-A41. A01 has 7 rows in it. The data is arranged by columns. There are 4 cells that contain data; the three additional cells contain formulas that derive their contents from these first 4 cells. There is one column in the destination workbook/worksheet for each row in the source workbook/worksheet. Part of the logic is to make the transition from row-oriented data to column-oriented data.

Since this part of the code is working (and is terse!), I will provide a snippet to illustrate my problem:

[vba] Dim wbTempl As Workbook

...
wbTempl.Worksheets(1).Copy After:=Sheets(1) 'Copies templ
' Template is first sheet in wb

'... Move logic from source rows to dest columns in copied
' template

...
' Rename copied wsheet to manufactured name wsname
wbTempl.ActiveSheet.Name = wsname
...
Loop to next set of rows
[/vba]

Again, the objective is to create a workbook with 41 worksheets (one for each set of rows in the source workbook) plus the initial template. This template contains seven rows and 14 columns. Four data items are gleaned from each source row (there are 14 rows per set of data), and these four numbers are placed in the first column. The other three rows are formulas which derive values. From the second row, we likewise build the four data items in column 2, etc, until all 14 rows' data are in the 14 columns.

Said another way, I am doing two things: (1) Swapping the data from row orientation to column orientation, and (2) making the data live in 41 worksheets (since there are 41 SETS of rows) instead of all being in the same worksheet.

I apologize for this being so difficult to explain.

[mdmackillop]

My mistake;; too late for me!
Have a look at this instead.
[VBA]Option Explicit
Sub Macro1()
Dim WBSource As Workbook
Dim WBTarget As Workbook
Dim WSSource As Worksheet
Dim WSTarget As Worksheet
Dim Tgt As Range

Dim i As Long
'Set source locations
Set WBSource = ActiveWorkbook
Set WSSource = WBSource.Sheets(1)
'Open target workbook
Set WBTarget = Workbooks.Open("G:\MyTemplate")
Application.ScreenUpdating = False

Do
i = i + 1
'Copy template to end
WBTarget.Sheets("Templ").Copy After:=Sheets(Sheets.Count)
'Set a variable to the new sheet
Set WSTarget = ActiveSheet
'Rename template
WSTarget.Name = "A" & Format(i, "00")
'Get a target on the new sheet
Set Tgt = WSTarget.Cells(1, 1)

'Copy Stuff to target
WSSource.Cells(1 + 14 * (i - 1), 1).Resize(14, 10).Copy Tgt

Loop Until i = 41
Application.ScreenUpdating = True
End Sub
[/VBA]

[jwise]

Thanks for the sample code.

I created a test file with 3 properties and changed all confidential information. The properties are all fictitious. I built a sample template and hopefully correctly changed your sample code to work on it. I also changed the spacing some in this code to match my style. I am generous with spacing and this annoys some. I am attaching these workbooks (Property.xls and Out_ColXRow.xls). The code is setup to run from "c:\vbax". You may use this or change the code.

My macro (named XForm is included), but it does not compile. It does create the proper output, it just doesn't handle the worksheet names properly. You will probably see very quickly what I was trying to do.

My code appears much more procedural than yours, and your version seems to put all the data in the template. I only move 4 of the 10 columns of numbers. I'm sure that eventually your approach is better. This means that yours will overlay the three rows of formulas. However, you could add 5 more rows before the formulas to the template and all of the data would be moved to the new columnar format. The formulas would need to be changed to use the correct cell references. You would not use the 1st column because it is the property name and the new worksheet will have only 1 property in it.

TIA

I just discovered that my zip file is too large. It is about 300K, so I will repackage and leave out the backup files. It's my experience that my developing code destroys the data files, so I always have a backup for the next try.

[jwise]

This file contains 1 worksheet. Using the previously uploaded test data, it should contain 4 worksheets after execution. Those worksheets would be Templ (the template), A01 (for Park Place), A02 (for Reading RR) and A03 (for Stuart).

Thanks again

[jwise]

Thanks to all who posted.

Using mdmackillop's example use of "object stuff", I was able to get my code to work... at least it works on the three property test file. I have not tried it yet on the 41 property file. I am reasonably sure that it will work.

Mdmackillop's code is superior to mine in that it would handle any number of rows of "same property" data. Using "cells", my code is limited, i.e. it is set to handle 14 rows of data per property. If a new row is added in the future, the inner loop would need to be changed from 14 to 15. This creates a need for perpetual maintenance. The pressure is off now, so I'll have time to change the fixed loop to variable based on property name.

If I use "Output.Cells(2,3) = Input.Cells(3,2), I am basically interchanging the row/column data between the Input and the Output. But this does not copy the formatting information. For example, assume that the data cited was a date field. While the number would be copied by the statement, the output worksheet would display a number, not a date. Is there any way to fix this? Said another way, I want to copy the data and its format.