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

0 Answers