I currently have workbooks, Test 1 (the name of the first workbook in which the code is written) and Test 2 (where values are inserted in from Test 1). My current code takes the values from Columns K1-K10 of the Test 1 Workbook and paste it in Line 2, columns F-P (columns 6-16, F2-P2) of Test 2 Workbook.
I try to run this code faster than when using it for my other applications. I feel like the loop makes him sluggish and sluggish. I am trying to replace my do while loop with a double (for) loop statement. Please let me know if you have a suggestion, as my Double For Loop will not include any values in it Test 2 Workbook (How do I measure the time needed to run each function)?
Here are both codes and screenshots as a visual aid:
Private Sub CommandButton1_Click()
Dim y As Workbook
Dim i As Integer
Dim j As Integer
i = 6
j = 1
Set y = Workbooks.Open(Filename:="\FILEPATHDatabasesTest 2.xlsm", Password:="Swarf")
With y
Do While j <= 11
If (Cells(j, 11).Value <> "") Then
.Sheets("MyTest2").Unprotect "Swarf"
.Sheets("Mytest2").Cells(2, i).Value = Sheet1.Cells(j, 11).Value
End If
i = i + 1
j = j + 1
Loop
.Password = "Swarf"
.Save
.Close False
End With
End Sub
Here is my attempted code for a double (for) loop:
Private Sub CommandButton1_Click()
Dim y As Workbook
Dim i As Integer
Dim j As Integer
Set y = Workbooks.Open(Filename:="\FILEPATHDatabasesTest 2.xlsm", Password:="Swarf")
With y
For i = 6 To 16
For j = 1 To 10
If (Cells(i, 11).Value <> "") Then
.Sheets("MyTest2").Unprotect "Swarf"
.Sheets("Mytest2").Cells(2, i).Value = Sheet1.Cells(j, 11).Value
End If
Next j
Next i
.Password = "Swarf"
.Save
.Close False
End With
End Sub