Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a sheets name to the worksheets that are copied over from a
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a sheets name to the worksheets that are copied over from a
Thank you!!! :)
It Works great!! "cush" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File placement with copied sheets | Excel Worksheet Functions | |||
How do i assign the ActiveWorkbook.Worksheets to a worksheets object? | Excel Worksheet Functions | |||
Excel file 4MB.Copied sheets to a new file.Now=64Kb-easier way? | Excel Discussion (Misc queries) | |||
ActiveWorkbook.Sheets.Count | Excel Programming | |||
Question about accessing worksheets on an AddIn file | Excel Programming |