Adding a sheets name to the worksheets that are copied over from an addin file to the activeworkbook

Hi
I'm using the following code to copy 2 worksheets from and addin file and
insert them into the activeworkbook.
The With part of the code changes cells A1, A2 and A3 from text to formulas
(as I was having problems with ranges etc...)
Anyway.. it works great...
but now I want to add the "sheet name" to cell A4 of the two sheets that get
copied over (Summary Pay I and Pay I Details.)
When they are copied over to the activeworkbook, their name changes to
Summary Pay I (2) , Pay I Details (2)
and so on as more are added.
I'm not sure how to do this...???

Public Sub AddPayIWS()
'Add more sheets to audit
Application.ScreenUpdating = False
PayIStateLoc.Show
Workbooks("TESTAddin.xla").Sheets(Array("Summary Pay I", "Pay I
Details")).Copy After:=ActiveWorkbook.Worksheets(Worksheets.Count)
MsgBox "New Pay I Worksheets have been added!"
With ActiveWorkbook
For i = .Worksheets.Count To .Worksheets.Count - 1 Step -1
With Worksheets(i)
.Range("A1").Formula = .Range("A1").Value
.Range("A2").Formula = .Range("A2").Value
.Range("A3").Formula = .Range("A3").Value
.Protect Password:="Test", DrawingObjects:=True, Contents:=True,
Scenarios:=True

End With
Next
End With
Application.ScreenUpdating = True
End Sub



Thanks in advance!
Kimberly

For i = .Worksheets.Count To .Worksheets.Count - 1 Step -1
With Worksheets(i)
.Range("A1").Formula = .Range("A1").Value
.Range("A2").Formula = .Range("A2").Value
.Range("A3").Formula = .Range("A3").Value
'Add sheet name
.Range("A4").Value = .Name
.Protect Password:="Test", DrawingObjects:=True, Contents:=True,
Scenarios:=True

or to get rid of the Space(2)
use
.Range("A4").Value = left(.Name, Len(.Name)-4)

End With
Next


"KimberlyC" wrote:

> Hi
> I'm using the following code to copy 2 worksheets from and addin file and
> insert them into the activeworkbook.
> The With part of the code changes cells A1, A2 and A3 from text to formulas
> (as I was having problems with ranges etc...)
> Anyway.. it works great...
> but now I want to add the "sheet name" to cell A4 of the two sheets that get
> copied over (Summary Pay I and Pay I Details.)
> When they are copied over to the activeworkbook, their name changes to
> Summary Pay I (2) , Pay I Details (2)
> and so on as more are added.
> I'm not sure how to do this...???
>
> Public Sub AddPayIWS()
> 'Add more sheets to audit
> Application.ScreenUpdating = False
> PayIStateLoc.Show
> Workbooks("TESTAddin.xla").Sheets(Array("Summary Pay I", "Pay I
> Details")).Copy After:=ActiveWorkbook.Worksheets(Worksheets.Count)
> MsgBox "New Pay I Worksheets have been added!"
> With ActiveWorkbook
> For i = .Worksheets.Count To .Worksheets.Count - 1 Step -1
> With Worksheets(i)
> .Range("A1").Formula = .Range("A1").Value
> .Range("A2").Formula = .Range("A2").Value
> .Range("A3").Formula = .Range("A3").Value
> .Protect Password:="Test", DrawingObjects:=True, Contents:=True,
> Scenarios:=True
>
> End With
> Next
> End With
> Application.ScreenUpdating = True
> End Sub
>
>
>
> Thanks in advance!
> Kimberly
>
>
>

Thank you!!!
It Works great!!
"cush" <[email protected]> wrote in message
news:[email protected]...
> For i = .Worksheets.Count To .Worksheets.Count - 1 Step -1
> With Worksheets(i)
> .Range("A1").Formula = .Range("A1").Value
> .Range("A2").Formula = .Range("A2").Value
> .Range("A3").Formula = .Range("A3").Value
> 'Add sheet name
> .Range("A4").Value = .Name
> .Protect Password:="Test", DrawingObjects:=True, Contents:=True,
> Scenarios:=True
>
> or to get rid of the Space(2)
> use
> .Range("A4").Value = left(.Name, Len(.Name)-4)
>
> End With
> Next
>
>
> "KimberlyC" wrote:
>
> > Hi
> > I'm using the following code to copy 2 worksheets from and addin file
and
> > insert them into the activeworkbook.
> > The With part of the code changes cells A1, A2 and A3 from text to
formulas
> > (as I was having problems with ranges etc...)
> > Anyway.. it works great...
> > but now I want to add the "sheet name" to cell A4 of the two sheets that
get
> > copied over (Summary Pay I and Pay I Details.)
> > When they are copied over to the activeworkbook, their name changes to
> > Summary Pay I (2) , Pay I Details (2)
> > and so on as more are added.
> > I'm not sure how to do this...???
> >
> > Public Sub AddPayIWS()
> > 'Add more sheets to audit
> > Application.ScreenUpdating = False
> > PayIStateLoc.Show
> > Workbooks("TESTAddin.xla").Sheets(Array("Summary Pay I", "Pay I
> > Details")).Copy After:=ActiveWorkbook.Worksheets(Worksheets.Count)
> > MsgBox "New Pay I Worksheets have been added!"
> > With ActiveWorkbook
> > For i = .Worksheets.Count To .Worksheets.Count - 1 Step -1
> > With Worksheets(i)
> > .Range("A1").Formula = .Range("A1").Value
> > .Range("A2").Formula = .Range("A2").Value
> > .Range("A3").Formula = .Range("A3").Value
> > .Protect Password:="Test", DrawingObjects:=True, Contents:=True,
> > Scenarios:=True
> >
> > End With
> > Next
> > End With
> > Application.ScreenUpdating = True
> > End Sub
> >
> >
> >
> > Thanks in advance!
> > Kimberly
> >
> >
> >