Excel problem create sheets

[KlausW]

I use this VBA code to create sheets based on what is written in column A, starting in A3. What I would like Excel to do is just create folders based on whether there is something in cells A3 and below. As you can see in the example, there are numbers from 1 to 6 but it can easily be 1 to 10 or higher.
All help will be appreciated, regards Klaus W

Option Explicit

Dim wb As Workbook

Dim ws As Worksheet, wsStamdata As Worksheet, wsCopy As Worksheet

Dim Area As Range, Arr() As Variant

Dim Count As Integer

Sub CopyArk()

With Application

.ScreenUpdating = False

.DisplayAlerts = False

End With

Set wb = ThisWorkbook

Set wsCopy = Sheets("Master") ' Navnet på kopisiden

Set wsStamdata = Sheets("Stamdata")

Set Area = wsStamdata.Range("A3")

Set Area = Range(Area, Area.End(xlDown))

Arr = Area

For Count = LBound(Arr, 1) To UBound(Arr, 1)

For Each ws In wb.Sheets

If ws.Name = Arr(Count, 1) Then ws.Delete

Next

Next

For Count = LBound(Arr, 1) To UBound(Arr, 1)

wsCopy.Copy After:=Sheets(Sheets.Count)

Set ws = ActiveSheet

With ws

.Name = Arr(Count, 1)

.Range("A1").Formula = "='Stamdata'!A" & Count + 2

End With

Next

With Application

.ScreenUpdating = True

.DisplayAlerts = True

End With

End Sub

 

[KlausW]

Hi
GWteB
It doesn't Work. Now I will try to explain if I can. The VBA code listed in # 1 works. But do not do what I would like. What I would like is described below. I do not know if you can build on the VBA code in # 1. The Create Sheet (Opret Ark) button in the Sheet (Stamdata) makes me make sheets according to what is written in Column A. There is a formula SEE PICTURE 1. Sometimes there are from 1 to 10 and sometimes there are from 1 to 4 or 1 to or other number. SEE PICTURE 2 and 3. The challenge is, if there is not from 1 to 10, there is an error in VBA code SEE PICTURE 4, and it makes an extra sheet SEE PICTURE 5, it should not do that. Hope this is understandable.

Best Regards

Klaus W

 

[GWteB]

I do not know if you can build on the VBA code in # 1.

Did that in my post #8 code. The first part is from your post #1 code. That part cycles through column A and checks whether there are worksheets with the names in column A. As soon as such a worksheet exists, this worksheet will be deleted unconditionally(!). The second part is the newly added one and cycles again through column A and creates new sheets with names according to the values in column A. An error will occur when there is an attempt to rename a worksheet using a name that already exists, since there cannot be two worksheets with exact te same name. Such an error is unlikely because potential duplicates have been deleted beforehand.

The "Master (2)" sheet is a result of your original code, which copies sheet.
My code doesn't copy sheets, it creates new ones and renames each of them based on the values in column A on the "Stamdata" sheet. Before that however, it checks the names of the present sheets and deletes the ones with names according to those in column A, to avoid any above mentioned naming conflict. It is therefore important to know whether any existing worksheet whose worksheet name appear in column A can be deleted beforehand or should be skipped.

 

[KlausW]

Hi

GWteB, I look at it, thanks. Klaus W​

 

[KlausW]

Hi, was just about to meet with the job so that's why you have not heard from me. I tried to enter your code from # 8 in a module. Sorry, I received an error message in VBA code. And Excel only makes the sheets but does not copy the formulas with over from the sheet MASTER. Hope you can solve the problem. Best Regards Klaus W

 

[KlausW]

The error is oWsNew.Name = Arr(Count, 1)

 

[GWteB]

Excel only makes the sheets but does not copy the formulas with over from the sheet MASTER.

I see, but this request was not clear to me.

I tried to enter your code from # 8 in a module. Sorry, I received an error message in VBA code. The error is oWsNew.Name = Arr(Count, 1)

I tested the code and it works for me. What you are experiencing can have various causes.

As I mentioned in a previous post, the name of a worksheet must be unique within a single workbook. Renaming a worksheet with the name of another already existing worksheet within that particular workbook results in an error. In addition to that, a worksheet name cannot exceed 31 characters and the following seven special characters may not be used: \ / * ? : [ ]
Whenever one renames a worksheet manually and make attempts to violate these rules one discovers that Excel ignores those attempts. However, whenever one tries to rename a worksheet through VBA violating these rules, a run-time error will occur. It is therefore important that any formulae in column A of the Stamdata worksheet do not evaluate to invalid worksheet names. Almost any problem can be solved, but it must be clear to me what your ultimate goal is and, to be honest, that is not yet the case.

In summary,
- check whether the intended worksheet names (those in column A of the Stamdata worksheet) are valid according to the above mentioned rules;
- please answer the following questions:
1. should fresh, empty worksheets be created?
2. should an existing worksheet be copied entirely to a new worksheet and if so, which one?
3. should an existing worksheet be copied partially to a new worksheet and if so, which one and what part should be copied?
4. if it turns out that a worksheet already exists with a name that appears in column A (as the intended name for one of the new sheets), should that existing sheet be kept or deleted?
5. if that pre-existing sheet should be kept, should that sheet be renamed in order to create a new worksheet with that name?

 

[KlausW]

Hi GWteB
I have verified that the names in column A of the Master Data spreadsheet are correct. Now you get the answers to the questions.
1. should fresh, empty spreadsheets be created?
No
2. should an existing spreadsheet be copied completely to a new spreadsheet, and if so, which?
Yes it should and it should be the spreadsheet called Master. There are formulas in it, I do not know if it matters.
3. should an existing spreadsheet be partially copied to a new spreadsheet, and if so, which and which part should be copied?
No it should not
4. If it turns out that a spreadsheet already exists with a name displayed in column A (as the intended name of one of the new sheets), should the existing sheet be kept or deleted?
It must be retained.
5. If the existing sheet is to be stored, must that sheet be renamed to create a new spreadsheet with that name?
Yes it needs to be renamed and a new that name needs to be created.
Thank you very much and have a great weekend
Best regards Klaus W

 

[GWteB]

Hi @KlausW,

Because of your following answers ....
1. should fresh, empty worksheets be created? (no)
2. should an existing worksheet be copied entirely to a new worksheet and if so, which one?
yes >> worksheet called MASTER
3. should an existing worksheet be copied partially to a new worksheet and if so, which one and what part should be copied? (no)
4. if it turns out that a worksheet already exists with a name that appears in column A (as the intended name for one of the new sheets), should that existing sheet be kept or deleted?
>> it should be kept
5. if that pre-existing sheet should be kept, should that sheet be renamed in order to create a new worksheet with that name?
yes >> it needs to be renamed and a new that name needs to be created.
... there is actually one question left:

In what way should they (ie any pre-existing worksheet regarding 5) be renamed? After all, duplicate worksheet names are not allowed.
The code below demonstrates what I mean. Be sure to copy both procedures, the CopyArk_r4 procedure as well as the SheetExists function.

Public Sub CopyArk_r4()

    Dim oWb         As Workbook
    Dim oWsMaster   As Worksheet
    Dim oWsStamdata As Worksheet
    Dim oWsNEW      As Worksheet
    Dim rngNames    As Range
    Dim Arr()       As Variant
    Dim i           As Long

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    Set oWb = ThisWorkbook
    Set oWsMaster = oWb.Sheets("Master")
    Set oWsStamdata = oWb.Sheets("Stamdata")
    Set rngNames = oWsStamdata.Range("A3")

    If Not Len(rngNames.Text) = 0 Then

        Set rngNames = Range(rngNames, rngNames.End(xlDown))
        Arr = rngNames

        For i = LBound(Arr, 1) To UBound(Arr, 1)
            
            ' copy desired sheet
            oWsMaster.Copy After:=oWb.Sheets(oWb.Sheets.Count)
            Set oWsNEW = ActiveSheet
            
            If Not SheetExists(oWb, Arr(i, 1)) Then
                ' rename copied sheet according to (next) name in table (column A)
                oWsNEW.Name = Arr(i, 1)
            Else
                ' renaming is not allowed due to duplicate sheet name
                MsgBox "A sheet called [" & Arr(i, 1) & "] already exists and should (according to your answer on question 4) be retained." & vbNewLine & _
                       "Therefore the newly copied sheet called [" & oWsNEW.Name & "] cannot be renamed to [" & Arr(i, 1) & "]!", vbExclamation
                       
                ' what would be the new name ???????
            End If
         Next i
    End If

    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
End Sub


Public Function SheetExists(ByVal argWb As Workbook, ByVal argSheetName As String) As Boolean
    Dim oWs As Worksheet
    For Each oWs In argWb.Sheets
        If StrComp(oWs.Name, argSheetName, vbTextCompare) = 0 Then
            SheetExists = True
            Exit For
        End If
    Next oWs
End Function

 

[KlausW]

Hi GWeN it work really well, 1000 thanks for the help. Many greetings Klaus W and nice Sunday

 

[GWteB]

My pleasure and thanks for letting me know. Glad it's sorted.