Code freezing when running loops

[dreen]

I have two workbooks, one with the Code INSIDE of it Sheet1 (which is the codename of the sheet I want to copy values from) and another workbook defined as "y" in this example. I want to copy over the values of cell "K1-K10" in Sheet1 into cell ("F1-F10") of workbook y (For example, I want F1.Value=K1.Value, F2.Value = K2.Value, etc...). Both workbooks are protected with a password, I am only trying to copy values from Sheet1 (that is why I don't unprotect it), but I want to save, close and protect workbook "y" once all of the values copy and paste into it.

When I hit the Activex Commanbutton inside Sheet1, the code is freezing up my workbook. I have double checked the file paths and the sheet names as well, they are correct.

I have also posted screenshots of the code and both workbooks below:

Private Sub Commandbutton1_Click()
Dim y As Workbook

Set y = Workbooks.Open(Filename:="???............\Databases\Test 2.xlsm", Password:="Swarf")

With y
  For i=1 to 10
    Do While cells(i,11).Value<>""

   .Sheets("Mytest2").Unprotect "Swarf"
   .Sheets("Mytest2").Cells(i,6).Value = Sheet1.Cells(i,11).Value

    Loop
  Next i

   .Password = "Swarf"
   .Save
   .Close False
End With
End Sub

Here is a Screenshot of the code:

Screenshot of Workbook where the code is written inside (codename Sheet1 = Mytest1)

Screenshot of workbook "y" (Where I want to paste the values)

 

[dmt32]

Untested - Try following update to your code

Private Sub Commandbutton1_Click()
    Dim y As Workbook
    Dim CopyRange As Range
    
    Set CopyRange = Sheet1.Range("K1:K10")
    
    Set y = Workbooks.Open(Filename:="???............\Databases\Test 2.xlsm", Password:="Swarf")
    
    With y
       With .Sheets("Mytest2")
            .Unprotect "Swarf"
             .Range("F1:F10").Value = CopyRange.Value
            .Password = "Swarf"
        End With
       .Close True
    End With
End Sub

Dave

 

[DanteAmor]

The code freezes because in While statement you are not increasing i
In fact, While statement is not necessary, the For statement increases automatically

Try this

Private Sub Commandbutton1_Click()
  Dim y As Workbook
  Set y = Workbooks.Open(Filename:="???............\Databases\Test 2.xlsm", Password:="Swarf")

  With y
    .Sheets("Mytest2").Unprotect "Swarf"
    For i = 1 To 10
      If Sheet1.Cells(i, 11).Value <> "" Then
        .Sheets("Mytest2").Cells(i, 6).Value = Sheet1.Cells(i, 11).Value
      End If
    Next i
   .Sheets("Mytest2").Protect "Swarf"
    .Save
    .Close False
  End With
End Sub

Nor is the For statement necessary, you just need to pass the data from one sheet to another, try this:

Private Sub Commandbutton1_Click()
  Dim y As Workbook
  Set y = Workbooks.Open(Filename:="???............\Databases\Test 2.xlsm", Password:="Swarf")
  y.Sheets("Mytest2").Unprotect "Swarf"
  y.Sheets("Mytest2").Range("F1:F10").Value = Sheet1.Range("K1:F10").Value
  y.Sheets("Mytest2").Protect "Swarf"
  y.Save
  y.Close False
End Sub

 

[dreen]

Untested - Try following update to your code

Private Sub Commandbutton1_Click()
    Dim y As Workbook
    Dim CopyRange As Range
   
    Set CopyRange = Sheet1.Range("K1:K10")
   
    Set y = Workbooks.Open(Filename:="???............\Databases\Test 2.xlsm", Password:="Swarf")
   
    With y
       With .Sheets("Mytest2")
            .Unprotect "Swarf"
             .Range("F1:F10").Value = CopyRange.Value
            .Password = "Swarf"
        End With
       .Close True
    End With
End Sub

Dave

Didn't quite work, but it helped me understand where else I could go with my code, thank you!

 

[dreen]

The code freezes because in While statement you are not increasing i
In fact, While statement is not necessary, the For statement increases automatically

Try this

Private Sub Commandbutton1_Click()
  Dim y As Workbook
  Set y = Workbooks.Open(Filename:="???............\Databases\Test 2.xlsm", Password:="Swarf")

  With y
    .Sheets("Mytest2").Unprotect "Swarf"
    For i = 1 To 10
      If Sheet1.Cells(i, 11).Value <> "" Then
        .Sheets("Mytest2").Cells(i, 6).Value = Sheet1.Cells(i, 11).Value
      End If
    Next i
   .Sheets("Mytest2").Protect "Swarf"
    .Save
    .Close False
  End With
End Sub

Nor is the For statement necessary, you just need to pass the data from one sheet to another, try this:

Private Sub Commandbutton1_Click()
  Dim y As Workbook
  Set y = Workbooks.Open(Filename:="???............\Databases\Test 2.xlsm", Password:="Swarf")
  y.Sheets("Mytest2").Unprotect "Swarf"
  y.Sheets("Mytest2").Range("F1:F10").Value = Sheet1.Range("K1:F10").Value
  y.Sheets("Mytest2").Protect "Swarf"
  y.Save
  y.Close False
End Sub

Thank you! I used the code and it ended up working well. Alternatively, I can also use this, I will leave it here incase anyone uses this thread in the future:

Private Sub CommandButton1_Click()

Dim y As Workbook
Dim i As Integer

Set y = Workbooks.Open(Filename:="\\FILEPATH\Test 2.xlsm", Password:="Swarf")


    With y

        For i = 1 To 10

            If (Cells(i, 11).Value <> "") Then

                .Sheets("MyTest2").Unprotect "Swarf"
                .Sheets("Mytest2").Cells(i, 6).Value = Sheet1.Cells(i, 11).Value

            End If

        Next i

        .Password = "Swarf"
        .Save
        .Close False

    End With

    End Sub

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[dreen]

I'm glad to help you. Thanks for the feedback.

Thank you for taking the time to write it out for me as opposed to simply explaining what I should and shouldn't do,

I grasp things much better by example than simply reading what to do!​

!