VBA copy & paste

[ashani]

Hi,
I wonder if someone could guide me please with VBA code :

I have list of of names in Column A1 to A100 - I would like to VBA to do the following when pressing Macro button :

- Create a new sheet
- Paste Name from Column A1 to new sheet in Column B3 (so on for every name with new sheet and paste name)
- Change the sheet name to the same name as in Column B3
- If sheet already exist with the same name than ignore it and move to the next one
- If the sheet exist but that name isn't in the list in Column A1 to A100 than delete that particular sheet.

I'd really appreciate you guidance.

Many thanks,

 

[DanteAmor]

I have list of of names in Column A1 to A100

What is the name of the sheet where are those names?

I named it "Names", but you can adjust the name in the macro.

Sub CreateSheets()
  Dim shN As Worksheet, sh As Worksheet
  Dim i As Long, x As Long
  Dim f As Range
  Dim sName As String
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  Set shN = Sheets("Names")    'fit to name of your sheet with names
  
  'If the sheet exist but that name isn't in the list in Column A1 to A100 than delete that particular sheet.
  For x = Sheets.Count To 1 Step -1
    sName = Sheets(x).Name
    If sName <> shN.Name Then
      Set f = shN.Range("A:A").Find(sName, , xlValues, xlWhole, , , False)
      If f Is Nothing Then
        Sheets(x).Delete
      End If
    End If
  Next
  
  For i = 1 To 100
    sName = shN.Range("A" & i).Text
    If sName <> "" Then
      If Not Evaluate("ISREF('" & sName & "'!A1)") Then
        'create sheet
        Sheets.Add after:=Sheets(Sheets.Count)
        ActiveSheet.Name = sName
        ActiveSheet.Range("B3").Value = sName
        
      End If
      'If sheet already exist with the same name than ignore it and move to the next one
    End If
  Next
  
  shN.Select
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub

 

[ashani]

What is the name of the sheet where are those names?

I named it "Names", but you can adjust the name in the macro.

Sub CreateSheets()
  Dim shN As Worksheet, sh As Worksheet
  Dim i As Long, x As Long
  Dim f As Range
  Dim sName As String
 
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
 
  Set shN = Sheets("Names")    'fit to name of your sheet with names
 
  'If the sheet exist but that name isn't in the list in Column A1 to A100 than delete that particular sheet.
  For x = Sheets.Count To 1 Step -1
    sName = Sheets(x).Name
    If sName <> shN.Name Then
      Set f = shN.Range("A:A").Find(sName, , xlValues, xlWhole, , , False)
      If f Is Nothing Then
        Sheets(x).Delete
      End If
    End If
  Next
 
  For i = 1 To 100
    sName = shN.Range("A" & i).Text
    If sName <> "" Then
      If Not Evaluate("ISREF('" & sName & "'!A1)") Then
        'create sheet
        Sheets.Add after:=Sheets(Sheets.Count)
        ActiveSheet.Name = sName
        ActiveSheet.Range("B3").Value = sName
       
      End If
      'If sheet already exist with the same name than ignore it and move to the next one
    End If
  Next
 
  shN.Select
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub

You're amazing - thank you @DanteAmor
I'm sorry I forgot to mention, when it's creating a new sheet, is it possible to copy previous sheet and change the name from Column A1 - A100 (whichever applicable) into Cell B3 in that sheet
Thank you once again!!!

 

[DanteAmor]

is it possible to copy previous sheet and change the name from Column A1 - A100 (whichever applicable) into Cell B3 in that sheet

I don't understand.
You are not explaining all the scenarios.
If there are no sheets and I am going to create a new sheet, what is the previous sheet?
If there is no previous sheet, how do I create the new ones?

 

[ashani]

Hi @DanteAmor

My apologies, so basically there's a sheet call "Master Sheet" - I would like all sheets to copy that Master Sheet and change the name of the sheet as Column B3 based on Cell value A1-A100.

When the name isn't in the list and VBA to delete the sheet then don't delete Master Sheet.

I hope it makes sense.

Thank you once again for your help!

 

[DanteAmor]

My apologies, so basically there's a sheet call "Master Sheet"

I have list of of names in Column A1 to A100

I don't understand.
They are data that you should put in your original post, since it causes a lot of confusion.

Do You have 2 sheets, one with names and another "Master" with the format?
If so try the following, adjust the names of both sheets in the code.

Sub CreateSheets()
  Dim shN As Worksheet, shM As Worksheet, sh As Worksheet
  Dim i As Long, x As Long
  Dim f As Range
  Dim sName As String
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  Set shN = Sheets("Names")     'fit to name of your sheet with names
  Set shM = Sheets("Master")    'fit to name of your sheet with format
  
  'If the sheet exist but that name isn't in the list in Column A1 to A100 than delete that particular sheet.
  For x = Sheets.Count To 1 Step -1
    sName = Sheets(x).Name
    If sName <> shN.Name And sName <> shM.Name Then
      Set f = shN.Range("A:A").Find(sName, , xlValues, xlWhole, , , False)
      If f Is Nothing Then
        Sheets(x).Delete
      End If
    End If
  Next
  
  For i = 1 To 100
    sName = shN.Range("A" & i).Text
    If sName <> "" Then
      If Not Evaluate("ISREF('" & sName & "'!A1)") Then
        'copy master sheet
        shM.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = sName
        ActiveSheet.Range("B3").Value = sName
      End If
      'If sheet already exist with the same name than ignore it and move to the next one
    End If
  Next
  
  shN.Select
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub

 

[ashani]

You’re truly a genius - thank you so much!!!