Not only don't want to know why, don't want to know the outcome!:(


"Rick Rothstein" wrote in message
...
I definitely don't want to know why the OP wants to do this.<g Given that,
would it be more efficient (can you imagine I'm asking about efficiency for
an application like this<vbg) if instead of inserting a sheet and then
populating it like this...

' create new sheets and fill them ad infinitum
' until VBA has a meltdown.
Do Until False ' loop until you run out of memory
With ThisWorkbook.Worksheets
Set WS = .Add(After:=.Item(.Count))
End With
WS.Cells.Value = "I am a winner"
Loop

couldn't we just copy any one of the previously populated worksheets and
put after any existing sheet? I'm thinking of something like this...

Do Until False ' loop until you run out of memory
With ThisWorkbook.Worksheets(Worksheets.Count)
.Copy After:=ActiveSheet
End With
Loop

By the way, I am *not* willing to test this code.<bg

--
Rick (MVP - Excel)


"Chip Pearson" wrote in message
...
I'm not sure that I even want to know why you want to do this, but
assuming you have your reasons, try something like:

Sub WhyInTheWorldDoYouWantToDoThis()
Dim WS As Excel.Worksheet
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
On Error GoTo ErrH:
' fill the existing sheets
For Each WS In ThisWorkbook.Worksheets
WS.Cells.Value = "I am a winner"
Next WS
' create new sheets and fill them ad infinitum
' until VBA has a meltdown.
Do Until False ' loop until you run out of memory
With ThisWorkbook.Worksheets
Set WS = .Add(after:=.Item(.Count))
End With
WS.Cells.Value = "I am a winner"
Loop
ErrH:
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With
End Sub

You'll blow up with a "not enough resources" error somewhere along the
way. Remember, XL2007 has 17 BILLION cells PER WORKSHEET so the
workbook that results will be VERY large.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Wed, 22 Jul 2009 16:34:01 -0700, Louie
wrote:

I am looking for help with a macro that will enter in every cell in a
workbook the contents "I am a winner" so that every row in every column
is
filled with these contents until Excel cannot create any more worksheets
to
store these contents and then at that point the macro will stop. Can you
help me with that? Here is what I am starting with.


Sub Macro1()
Range("A1: ").Select
ActiveCell.FormulaR1C1 = "I am a winner"
Range("A2").Select

A3:IV65535 then

ActiveCell.FormulaR1C1 = "I am a winner"
Range("IV65536").Select
ActiveCell.FormulaR1C1 = "I am a winner"
End Sub