Insert Sheets & Copy
Think you/I have stumbled into a bug on that one.
Sub Eft()
Worksheets(2).Cells.Copy _
Worksheets(1).Range("A1")
End Sub
works
Sub Eft()
With Worksheets(1)
Worksheets(2).Cells.Copy .Range("A1")
End With
End Sub
works, but adding a line continuation character
Sub Eft()
With Worksheets(1)
Worksheets(2).Cells.Copy _
.Range("A1")
End with
End Sub
doesn't work but adding the argument name
Sub Eft()
Worksheets(2).Copy _
Destination:=.Range("A1")
End Sub
works, so for some reason, excel doesn't recognize the .Range("A1") after
the line continuation character even though I would expect this to be legal
and the use of the named argument shows that it is.
Sub SheetInsert()
Dim s1 As String, s2 As String
Dim x As Long, y As Long, i As Long
Dim pasteto As String
s1 = "How many sheets would you like to add?"
s2 = "What would you like the number of the first sheet to be?"
x = InputBox(s1)
y = InputBox(s2, "Insert Sheets")
For i = 1 To x
Sheets.Add After:=Sheets(Sheets.Count)
pasteto = i + y - 1
ActiveSheet.Name = pasteto
With Sheets(pasteto)
Sheets("Master").Cells.Copy _
Destination:=.Range("A1")
.Range("A1").Value = .Name
.Range("B22").Select
End With
Next i
End Sub
worked for me.
--
Regards,
Tom Ogilvy
"Paul Black" wrote in message
oups.com...
Hi Tom,
I Appreciate your Help and Time on this.
Unfortunately it is giving me an Object Required Error on ...
Sheets("Master").Cells.Copy _
.Range("A1")
... for some Reason.
Thanks in Advance.
All the Best.
Paul
Tom Ogilvy wrote:
s1 = "How many sheets would you like to add?", "Insert Sheets"
should have been
s1 = "How many sheets would you like to add?"
Just didn't get things all cleaned up when I tried to help you overcome
your
inability to discern your word wrap problem, but only exacerbated it by
creating another problem you couldn't discern. Sorry about that.
--
Regards,
Tom Ogilvy
"Paul Black" wrote in message
oups.com...
Tom,
Thanks for the Reply.
For some Reason the line ...
s1 = "How many sheets would you like to add?", "Insert Sheets"
... comes up with a Syntax Error.
Excelenator,
Thanks for the Reply.
It Produces the Correct Results.
All the Best.
Paul
|