AKK
07-28-2009, 09:40 AM
I am writing a macro that copies data into cells from the same cells in a different workbook & worksheets with the same names. Originally, I wrote a macro that did exactly that & worked perfectly:
Private Sub CommandButton1_Click()
Dim strSheet As String
Dim ix As Integer
Dim iy As Integer
Dim iz As Integer
For iz = 3 To 5
For iy = 3 To 7
For ix = 1 To 5
strSheet = Sheets(iz).Name
Workbooks("TestWorkBook2.xls").Sheets(strSheet).Cells(iy,
ix)= "=[TestWorkBook1.xls]" & strSheet & "!R" & iy & "C" &
ix
Next ix
Next iy
Next iz
End Sub
Then, I modified it a little, because I only wanted it to copy values to cells that were a certain color and empty:
Private Sub CommandButton1_Click()
Dim strSheet As String
Dim ix As Integer
Dim iy As Integer
Dim iz As Integer
For iz = 3 To 5
For iy = 1 To 100
For ix = 1 To 100
strSheet = Sheets(iz).Name
If Cells(iy, ix).Interior.ColorIndex = 34 And Cells
iy, ix).Value = "" Then
Workbooks("TestWorkBook2.xls").Sheets(strSheet).Cells
iy, ix) = "=[TestWorkBook1.xls]" & strSheet & "!R" &
iy & "C" & ix
End If
Next ix
Next iy
Next iz
End Sub
Somehow, this has caused a problem with looping through the values in the variables. It will execute perfectly on the first sheet, but when it moves to sheet 2 and resets iy to 1, ix moves on to 101 instead of stopping at 100 & restarting at 1. Can anyone see why this is?
Thanks for your help!
Private Sub CommandButton1_Click()
Dim strSheet As String
Dim ix As Integer
Dim iy As Integer
Dim iz As Integer
For iz = 3 To 5
For iy = 3 To 7
For ix = 1 To 5
strSheet = Sheets(iz).Name
Workbooks("TestWorkBook2.xls").Sheets(strSheet).Cells(iy,
ix)= "=[TestWorkBook1.xls]" & strSheet & "!R" & iy & "C" &
ix
Next ix
Next iy
Next iz
End Sub
Then, I modified it a little, because I only wanted it to copy values to cells that were a certain color and empty:
Private Sub CommandButton1_Click()
Dim strSheet As String
Dim ix As Integer
Dim iy As Integer
Dim iz As Integer
For iz = 3 To 5
For iy = 1 To 100
For ix = 1 To 100
strSheet = Sheets(iz).Name
If Cells(iy, ix).Interior.ColorIndex = 34 And Cells
iy, ix).Value = "" Then
Workbooks("TestWorkBook2.xls").Sheets(strSheet).Cells
iy, ix) = "=[TestWorkBook1.xls]" & strSheet & "!R" &
iy & "C" & ix
End If
Next ix
Next iy
Next iz
End Sub
Somehow, this has caused a problem with looping through the values in the variables. It will execute perfectly on the first sheet, but when it moves to sheet 2 and resets iy to 1, ix moves on to 101 instead of stopping at 100 & restarting at 1. Can anyone see why this is?
Thanks for your help!