worksheets copy problem

[red29]

Hi,
I have a spreadsheet with 2 sheets.
Sheet1 contain a data column A (chem., bio, mat, …)
Sheet2 contains standard format of a document.
I need to create new sheets with the names from Column A (sheet with name chem., bio,….) and to copy sheet2 to all those sheets. (Formulas, column with , pictures, ….)
The problem is that I have a macro witch can do the first step but don’t copy sheet2.

Here is the code:

Sub MakeSheets()
Dim i As Integer
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'Creates sheets and renames it according to the cells
'value in a specific range

'Declare varibles
Dim NewSheetsName, CopysheetsName As String
Dim ExistSheet
Dim Wks As Worksheet
'Go through cells in range A7 to A30
' each to have a tab named by it

'Start loop
For Each cell In Sheets("LIJST FD").Range("A7:A30")
'check if there is a value else excel cannot insert a name into tab
If cell.Value = "" Then GoTo NoValue
' Set varible to the cells value
NewSheetsName = cell
'Add the sheet and insert it in front of all the other tabs
Sheets.Add After:=Sheets(Sheets.Count)
' set the copied sheets name
CopysheetsName = ActiveSheet.Name
' Rename the sheet if it does not exist
ExistSheet = 0
For x = 1 To Sheets.Count
If Sheets(x).Name = NewSheetsName Then
ExistSheet = 1
End If
Next
If ExistSheet = 0 Then Sheets(CopysheetsName).Name = NewSheetsName
If ExistSheet = 1 Then Sheets(CopysheetsName).Delete


NoValue:

Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Thanks for any help

[gajendra.gupta]

Hi,

I have modified your code a bit..try this code now.

Please Login or Register  to view this content.

Regards,
Gajendra Gupta