The idea of this code is to copy and paste initial values for an Excel Worksheet. My file has 10 sheets and the user selects through a listbox, the different sheets in which he wants to duplicate the data.
Because the sub stays in a loop, I can't close Excel or save the file.
The issue is linked to Sheets(k).Activate as if I use Sheets(1) for example, it won't go into a loop. Also, at the end of the sub, if I change sheet manually by clicking on one of them, it will exit the loop and allow me to close/save Excel.
As this file is for a client, I wan't it to be user friendly and avoid annoying bugs like this one.
Right now, I have tried to add an exit sub, end at the end but nothing happened I have also tried to create an If loop saying that if Sheets(k) is activate then vba need to stop the Sub.
Private Sub CommandButton1_Click() Dim i As Integer Dim k As Integer ActiveSheet.Range("A1:G5").Select 'Selects yellow cells Selection.Copy 'Copies yellow cells For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) = True Then 'If the value is selected k = ListBox1.List(i) 'k becomes the selected value Sheets(k).Activate 'Activates the selected value sheet Range("A1:G5").Select 'Selects yellow cells ActiveSheet.Paste 'Pastes initial data End If Next i ''***** Selects cell A15 in all sheets (Visual purpose) ******************************* For i = 1 To k Sheets(i).Activate Range("A15").Select Next i Sheets(k).Activate 'Go to the last duplicated sheet Application.CutCopyMode = False End Sub