Quick lil Macro

[cav~firez22]

Could someone help me with a quick little macro...?

Im trying to get something to do the following

copy the active sheet to the end of the workbook.
rename that copy to the value of cell B2


Think someone could help with this??

 

[Jonmo1]

Sub Macro1()
    x = ActiveSheet.Range("b2").Value
    ActiveSheet.Copy after:=Sheets(Sheets.Count)
    ActiveSheet.Name = x
End Sub

 

[cav~firez22]

wOW, talk about quick. Thank.

how about a few more parts of it??

Im stuck trying to get a range created from the sheet names.

Each time the sheet is copied, and the name is applied from the value of the cell.... Im trying to get it to copy the name of the sheet to Cell A40, on sheet Template. and then as more sheets get created, to keep filling in the sheet names in the first empy cell below A40 on sheet template..


sounds hard, seems hard, as im stuck...

thanks

 

[Jonmo1]

Sub Macro1() 
    LR = Sheets("template").Cells(Rows.Count,"A").End(xlup).Row + 1
    x = ActiveSheet.Range("b2").Value 
    ActiveSheet.Copy after:=Sheets(Sheets.Count) 
    ActiveSheet.Name = x 
    Sheets("template").Range("A" & LR).Value = x
End Sub

 

[cav~firez22]

Thanks for that. works great.

 

[cav~firez22]

Ok, one more question... i think

Cells.Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= False

How can i get this to Password the protection, as i dont want the user to know the password.

Thanx much

 

[Jonmo1]

With ActiveSheet
   .Cells.Locked = True
   .Cells.FormulaHidden = False
   .Protect "Password", DrawingObjects:=False, Contents:=True, Scenarios:=False
End With

You will need to include the password in any code you have that UNprotects the sheet..

.Unprotect "Password"

 

[cav~firez22]

Excellent. Thanks your Help.

Truley Appreciated

 

[cav~firez22]

for
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


How would i make it copy all the formats and such as well? currently its just the values.

 

[Jonmo1]

With 2 lines, one for the values, then one for the formats..

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False