Hi,
I have a problem when I am copying sheets using VBA, every time a get this “error massage” saying that the name already exists. It is referring to Data range names, can I use a code to select “yes to all”? I post a pic of the error and my code down below
Private Sub CommandButton1_Click()
Dim TargetRow1 As Integer
Dim Targetrow2 As Integer
Dim actsheet As String
'GÖR MALL OCH BIGBANG BLAD SYNLIGA
Application.ScreenUpdating = False
ActiveWorkbook.Sheets("Mall").Visible = True
ActiveWorkbook.Sheets("BigBang").Visible = True
'FUNKTION FÖR ÄNDRING AV ARR
If Sheets("BigBang").Range("H3") = "NEW" Then
TargetRow1 = Sheets("BigBang").Range("G2").Value
Targetrow2 = Sheets("BigBang").Range("H2").Value
Else
TargetRow1 = Sheets("BigBang").Range("G4").Value
Targetrow2 = Sheets("BigBang").Range("H4").Value
End If
'DATA INMATNING STARTBLAD
Sheets("start").Range("start_namn").Offset(TargetRow1, 0).Value = namn_org
Sheets("start").Range("start_namn").Offset(TargetRow1, 1).Value = ord_namn
Sheets("start").Range("start_namn").Offset(TargetRow1, 2).Value = SO_namn
'DATA INMATNING BIGBANG
Sheets("BigBang").Range("start_bigbang").Offset(Targetrow2, 0).Value = namn_org
Sheets("BigBang").Range("start_bigbang").Offset(Targetrow2, 1).Value = CDbl(Unik_del)
Sheets("BigBang").Range("start_bigbang").Offset(Targetrow2, 2).Value = ord_namn
Sheets("BigBang").Range("start_bigbang").Offset(Targetrow2, 3).Value = ord_tel
Sheets("BigBang").Range("start_bigbang").Offset(Targetrow2, 4).Value = ord_post
Sheets("BigBang").Range("start_bigbang").Offset(Targetrow2, 5).Value = SO_namn
Sheets("BigBang").Range("start_bigbang").Offset(Targetrow2, 6).Value = SO_tel
Sheets("BigBang").Range("start_bigbang").Offset(Targetrow2, 7).Value = SO_post
'SKAPA NYTT DATABLAD
If Sheets("BigBang").Range("H3") = "NEW" Then
Set ws = Sheets("Mall")
ws.Copy After:=Sheets("GO")
Set wsNew = Sheets(Sheets("GO").Index + 1)
wsNew.Name = namn_org
Unload Ny_org
Else
Unload Ny_org
End If
'LÅS NYA DATABLADET
Sheets(ActiveSheet.Name).Protect Password:="ABF23ABF"
Sheets(ActiveSheet.Name).Protect userinterfaceonly:=True
'GUIDE KOD
GUIDE = Sheets("BigBang").Range("start_bigbang").Offset(Targetrow2, -1).Value
ActiveSheet.Range("K2").Value = GUIDE
'GÖR MALL OCH BIGBANG OSYNLIGA
Sheets(ActiveSheet.Name).Visible = True
ActiveWorkbook.Sheets("Mall").Visible = False
ActiveWorkbook.Sheets("BigBang").Visible = False
Application.ScreenUpdating = True
End Sub
I have a problem when I am copying sheets using VBA, every time a get this “error massage” saying that the name already exists. It is referring to Data range names, can I use a code to select “yes to all”? I post a pic of the error and my code down below
Private Sub CommandButton1_Click()
Dim TargetRow1 As Integer
Dim Targetrow2 As Integer
Dim actsheet As String
'GÖR MALL OCH BIGBANG BLAD SYNLIGA
Application.ScreenUpdating = False
ActiveWorkbook.Sheets("Mall").Visible = True
ActiveWorkbook.Sheets("BigBang").Visible = True
'FUNKTION FÖR ÄNDRING AV ARR
If Sheets("BigBang").Range("H3") = "NEW" Then
TargetRow1 = Sheets("BigBang").Range("G2").Value
Targetrow2 = Sheets("BigBang").Range("H2").Value
Else
TargetRow1 = Sheets("BigBang").Range("G4").Value
Targetrow2 = Sheets("BigBang").Range("H4").Value
End If
'DATA INMATNING STARTBLAD
Sheets("start").Range("start_namn").Offset(TargetRow1, 0).Value = namn_org
Sheets("start").Range("start_namn").Offset(TargetRow1, 1).Value = ord_namn
Sheets("start").Range("start_namn").Offset(TargetRow1, 2).Value = SO_namn
'DATA INMATNING BIGBANG
Sheets("BigBang").Range("start_bigbang").Offset(Targetrow2, 0).Value = namn_org
Sheets("BigBang").Range("start_bigbang").Offset(Targetrow2, 1).Value = CDbl(Unik_del)
Sheets("BigBang").Range("start_bigbang").Offset(Targetrow2, 2).Value = ord_namn
Sheets("BigBang").Range("start_bigbang").Offset(Targetrow2, 3).Value = ord_tel
Sheets("BigBang").Range("start_bigbang").Offset(Targetrow2, 4).Value = ord_post
Sheets("BigBang").Range("start_bigbang").Offset(Targetrow2, 5).Value = SO_namn
Sheets("BigBang").Range("start_bigbang").Offset(Targetrow2, 6).Value = SO_tel
Sheets("BigBang").Range("start_bigbang").Offset(Targetrow2, 7).Value = SO_post
'SKAPA NYTT DATABLAD
If Sheets("BigBang").Range("H3") = "NEW" Then
Set ws = Sheets("Mall")
ws.Copy After:=Sheets("GO")
Set wsNew = Sheets(Sheets("GO").Index + 1)
wsNew.Name = namn_org
Unload Ny_org
Else
Unload Ny_org
End If
'LÅS NYA DATABLADET
Sheets(ActiveSheet.Name).Protect Password:="ABF23ABF"
Sheets(ActiveSheet.Name).Protect userinterfaceonly:=True
'GUIDE KOD
GUIDE = Sheets("BigBang").Range("start_bigbang").Offset(Targetrow2, -1).Value
ActiveSheet.Range("K2").Value = GUIDE
'GÖR MALL OCH BIGBANG OSYNLIGA
Sheets(ActiveSheet.Name).Visible = True
ActiveWorkbook.Sheets("Mall").Visible = False
ActiveWorkbook.Sheets("BigBang").Visible = False
Application.ScreenUpdating = True
End Sub