Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Use VBA to cop sheets from a range but skip allready exist.

0

Hi I have this sheet, where I wan't it to add sheets based on a pre defined range.
So fare so good, I got that working, but if I come back to the sheet and add date within the range, it comes up with an error.
It tryes to make a sheet that's allready there, what I need it to do is skip sheets allready present.

Sub OpretFanerEfterListe()

Dim ws As Worksheet, Ct As Long, c As Range
Set ws = Worksheets("Skabelon")
Application.ScreenUpdating = False

For Each c In Sheets("Forside").Range("A6:A29")
    If c.Value <> "" Then
        ws.Copy after:=Sheets(Sheets.Count)
        ActiveSheet.Name = c.Value
        Ct = Ct + 1
    End If
Next c
If Ct > 0 Then
    MsgBox Ct & " Nye aftale sedler oprettet"
Else
    MsgBox "Der er ingen aftalesedler at oprette"
End If
Application.ScreenUpdating = True
End Sub

The range I work with is A6:A29, so is I set the Datavilidation field (B2) to 5 i makes a list within the range of 1-5, and i hit the button it works and i get 4 new sheets names 2-4 no problem there.
But if I come back and change B2 to lets say 7, it updates the list to 1-7 that works, but when i hit the button to create sheets the error occurs.
I cant seem to figure out what code to add to make it skip allready exixting sheets and just make the uniqe once.

excel
MS Office Version: Excel 365
posted by: Rackneck (rep: 6) on Thu Oct 20, 2022 at 11:00 am
Answer
Discuss

Answers

0
Selected Answer

 Hi Rackneck

I suggest you add a function to check if a worksheet exists with a given name, as follows:

Function WsExists(Ws As String) As Boolean
On Error Resume Next
WsExists = (ActiveWorkbook.Sheets(Ws).Index > 0)
End Function

Then change your code as follows (changes in bold):

Sub OpretFanerEfterListe()

Dim Ws As Worksheet, Ct As Long, c As Range
Set Ws = Worksheets("Skabelon")
Application.ScreenUpdating = False

For Each c In Sheets("Forside").Range("A6:A29")
    If c.Value <> "" And Not WsExists(c.Value) Then
        Ws.Copy after:=Sheets(Sheets.Count)
        ActiveSheet.Name = c.Value
        Ct = Ct + 1
    End If
Next c
Application.ScreenUpdating = True

If Ct > 0 Then
    MsgBox Ct & " Nye aftale sedler oprettet"
Else
    MsgBox "Der er ingen aftalesedler at oprette"
End If

End Sub

The attached file contains this change (and the function in Module2).plus I updated the screen before the message box (makes more sense to me).

Hope this fixes things for you.

posted by: John_Ru (rep: 6142) on Thu Oct 20, 2022 at 6:50 pm
Discuss

Discussion

Thank you John Once again you come to my rescue, it was just what I was looking for. I have a follow up question, it doesnt have to be VBA it can be a normal formular solution. In the same workbook on the first sheet "Forside" i have enteres formulars in collum B and C that extractracts data from the new sheets that cam be created 2-25. And I have fixed so they the same even if you delete some of thes sheets. But then doesnt show the value of the target cell anymore they show #REFERENCE! insted.
aIf I enter the code line and press enter the formular works without having to edit it, is there a way to make that update havven automatic throu as formular og throu the macro ? 
Rackneck (rep: 6) Oct 23, '22 at 5:31 am
Hi Rackneck. Glad that worked for you and thanks for selecting my answer.

I won't be near my PC today but the #Ref problem occurs when you delete with absolute referencing. Suspect you could use OFFSET in your formula but would need to see a file. Please raise a new question with file and advise the formula (/where it's used). 
John_Ru (rep: 6142) Oct 23, '22 at 5:48 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login