hi guys,
below is the code which KJBox had help me before. The code works fine and does what it is suppose to do. But for some reason it does rename sheets or update formula for some of the staff. the error code which comes up is :
Run-time Error '1004': Method 'select' of object_worksheet' failed
http://www.ozgrid.com/forum/showthread.php?t=200862&page=2
can someone help me update the below code:
Code
Option Explicit
Dim iCopies As Integer
Sub btnCreate_Click()
Dim i As Integer, lcol As Long
lcol = 1: iCopies = Sheet1.[b3]
Application.ScreenUpdating = 0
For i = 1 To iCopies
With Sheet2
[Template].Copy
.Cells(11, lcol).PasteSpecial xlPasteAll
.Cells(11, lcol).PasteSpecial xlPasteColumnWidths
.Cells(12, lcol + 3) = "Option " & i
.Cells(12, lcol + 3).Resize(, 2).Merge
lcol = lcol + 6
End With
Next
With Sheet2
.Activate
With Application
.Goto .[a1]
.CutCopyMode = 0
End With
End With
End Sub
Sub btnSheets_Click()
Dim i As Integer, ii As Integer, s As String
ii = 6
With Application
.ScreenUpdating = 0
.DisplayAlerts = 0
End With
For i = 1 To iCopies - 1
Sheet3.Copy after:=Sheets(Sheets.Count)
With Sheets(Sheets.Count)
.[d3] = "Option " & i + 1
.[b4].FormulaR1C1 = "=Base!R[9]C[" & ii & "]"
.[b4].AutoFill .[b4].Resize(, 4)
.[b4].Resize(, 4).AutoFill .[b4].Resize(219, 4)
.[b226].FormulaR1C1 = "=Base!R[9]C[" & ii & "]"
.[b226].AutoFill .[b226].Resize(, 4)
.[b226].Resize(, 4).AutoFill .[b226].Resize(27, 4)
s = CStr(i + 1)
.Name = s
End With
ii = ii + 6
Next
'ThisWorkbook.SaveAs "Your File Name here" // add file path (if different from Template file path) and name
End Sub