Solved: How to copy rows in different worksheets created on the fly

[blindaue]

Hi
I am new to Excel VBA and have the following requirement on Excel 2003.
I have:
- a spreadsheet "Template" which is a template with calc, graph, formating and cell references within the spreadsheet only
- a spreadsheet "Raw Data" with around 20-30 rows of around 25 columns

I would like to create a VBA macro taht for each row of "Raw Data" to
- creates a new worksheet according to the template
- renames the newly created worksheet with the first col of the current row of "Raw Data"
- copies all the values of the row (and the header if possible, but this is optional) at the end of the newly created worksheet (this could be a fix row number, say row 50)

This would be an simplified example of "raw data" sheet data:

Company_Name ; Company_Type ; Country ; Revenue ; President...
"Shell" ; "Energy" ; "NL" ; "10000000000" ; "John Doe"...
"Novartis" ; "Pharma" ; "CH" ; "52000000000" ; "Jack N'Jill"...

I then would like to see 2 worksheets names respectively "Shell" and "Novartis" and having on row 50 the entire row pasted from the "Raw Data" sheet... These 2 spreadsheets are copies of the "template" worksheet present in the same workbook, with fields referencing values from the row 50 etc...

Thanks in advance for any pointer or hints

[mdmackillop]

Welcome to VBAX

Give this a try
[VBA]
Sub Macro1()
Dim Cel As Range
With Sheets("Raw Data")
For Each Cel In Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
Sheets("Template").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Cel
.Range("1:1," & Cel.Row & ":" & Cel.Row).Copy Cells(50, 1)
Next
End With
End Sub

[/VBA]

[blindaue]

Wow... Fantastic

There is so much more to learn to be able to do all this in only 5 lines of code...

Thank you so much mdmackilltop!

This forum rocks!

[blindaue]

A quick follow up on the solution.

After around 15-20 worksheets have been created by the macro, I am having the following error:
"Run-Time error '1004' ; Copy method of Worksheet class failed" ...

I also noticed that the first worksheets are created very quickly, but after 10 or so worsheets created (and row pasted) the following worksheets get created much slower, taking up to 3 sec for the last ones (just before the error comes up...) against 3 worksheets created in about 1 sec for the first ones...
My hunch is that it might be related to a memory issue...

Is this a known issue? Are there limitations or should we include something in the code to refresh/reset the memory so that I can create more than 30 worksheets with this macro.
I tried to save the workbook after 10 worksheets, but that did not seem to change anything... Thank you for sharing your expertise.

Boris

[mdmackillop]

I just ran it and it inserted 300 sheets with no real loss in speed. I suspect your template may contain many calculations. It it possible to post a sample workbook to test?

[blindaue]

Here it is, with fake data but with the same row length and same template to be copied
If you run the macro1 it errors out around worksheet17, it stops at worksheet 10 to save the workbook.
thanks
boris

[mdmackillop]

Without the save, the code runs in 7 seconds.

I'm not finding that turning off calculation makes a difference, but give it a try

[VBA]
Sub Macro1()
Dim Cel As Range
Dim tim
On Error GoTo exits
tim = Timer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

s = 0
With Sheets("Raw Data")
For Each Cel In Range(.Cells(3, 1), .Cells(Rows.Count, 1).End(xlUp))
Sheets("Template").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Cel
.Range("2:2," & Cel.Row & ":" & Cel.Row).Copy Cells(49, 1)
s = s + 1
If s Mod 10 = 0 Then
'ThisWorkbook.Save
End If
' If s = 5 Then ThisWorkbook.SaveAs Filename:= FPath & "\" & FName End If
' If s = 5 Then s = 0 End If
Next
End With
Application.ScreenUpdating = True
exits:
Application.Calculation = xlCalculationAutomatic
MsgBox Timer - tim
'ThisWorkbook.Save

End Sub

[/VBA]

[blindaue]

I am still having the error "Run-Time error '1004' ; Copy method of Worksheet class failed"
I have added a few more rows (til Airline 40) and and it errors out

[blindaue]

I am still having the error "Run-Time error '1004' ; Copy method of Worksheet class failed" on my real file. The error is around worksheet17 as before...

On the sample file, when I run the same macro, commenting the exits part (which prevens from the error msg box to come up) and added more rows (til Airline 72) it errors out around airline43 now... If you delete the worksheets created, save and then rerun the macro, it usually errors out on the first row...
This is driving me nuts...

FYI: I don't care how long the macro runs (it is a weekly process, so no pb) as long as it does not error out.

So what is this error about anyway: "Run-Time error '1004' ; Copy method of Worksheet class failed"

[mdmackillop]

I tried it on another older pc and I error out at 43. Saving, closing and reopening will not add more sheets, so I guess a limit has been reached.

Personally I would redesign this to use only the template and link it to data which can be imported to show the desired display. i.e., select the airline from a list and write the data to the required location.