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

[KimberlyC]

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

 

[cush]

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]

Thank you!!!
It Works great!!

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