I am creating several worksheets based on some worksheet "format" just simply copying all its content with formulas. Only I give a new name to the sheet.
I want that each time I create a sheet and giving it a name "format!" word in all cells has been replaced with active worksheet name.
I tried to write some codes but it seems does not work.
Sub createsheet()
LastRow = Sheets("SUMMARY").Range("A" & Rows.Count).End(xlUp).Row
Dim ws As Worksheet
For i = 9 To LastRow
Set ws = ThisWorkbook.Sheets.Add(after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
ws.Name = Left(Sheets("SUMMARY").Cells(i, 1), 31)
wsrepl = Worksheets(Worksheets.Count).Name
Sheets("format").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Worksheets(Worksheets.Count).Activate
ActiveSheet.Paste
ActiveSheet.Cells(2, 2) = Sheets("SUMMARY").Cells(i, 1)
ActiveSheet.Cells(5, 2) = Sheets("SUMMARY").Cells(i, 1)
Worksheets(Worksheets.Count).Activate
Cells.Replace What:="format!", Replacement:= _
wsrepl, LookAt:=xlPart, SearchOrder:=xlByRows _
, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next i
End Sub
Any help will be highly appreciated.
Aucun commentaire:
Enregistrer un commentaire