Sub Myloop() 'Loop is a Keyword and cannot be used otherwise
Dim a As Integer
Dim c As String
Dim d As Integer
Dim e As Integer
Dim Cel As Range
For a = 1 To Workbooks("update").Worksheets("update").Range("AY2").Value
c = a 'c is a String, a is an Integer. If Range("AY2") is not a number
'how is the line above working? Use c = CStr(a)
e = a * 3
On Error GoTo label1:
For d = 2 To 1000
'Windows(Workbooks("update").Worksheets("update").Range("E7").Value).Activate
'The next two lines negates the last part of the above line
'Sheets("LSCH2").Select
'Columns("A:A").Select
'This line replaces the three above
Workbooks("update").Sheets("LSCH2").Range("A:A").Select
'Do you have worksheets whose names are a simple number? c = a = Integer
Set Cel = Selection.Find(What:=Sheets(c).Cells(d, 1).Value)
'Probably not needed:
', After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
, SearchFormat:=False
'ActiveCell.Select 'Not needed
If Not Cel Is Nothing Then
Cel.Offset(0, 13 + e).Value = Sheets(c).Cells(d, 2).Value
Cel.Offset(0, 14 + e).Value = Sheets(c).Cells(d, 3).Value
Cel.Offset(0, 15 + e).Value = Sheets(c).Cells(d, 4).Value
End If
Next d
label1:
Next a
End Sub
If c is a Sheets index number, find another way to refer to the sheet, because the index number of any sheet can change without notice.
Sub Myloop() 'Loop is a Keyword and cannot be used otherwise
Dim a As Integer
Dim c As String
Dim d As Integer
Dim e As Integer
Dim Cel As Range
With Workbooks("update")
For a = 1 To .Sheets("update").Range("AY2").Value
c = CStr(a)
e = a * 3
'For d = 2 to last used cell in column "A"
For d = 2 To .Sheets("LSCH2").Cells(Rows.Count, 1).End(xlUp).Row
Set Cel = .Sheets("LSCH2").Range("A:A").Find(What:=Sheets(c).Cells(d, 1).Value)
If Not Cel Is Nothing Then
Cel.Offset(0, 13 + e).Value = Sheets(c).Cells(d, 2).Value
Cel.Offset(0, 14 + e).Value = Sheets(c).Cells(d, 3).Value
Cel.Offset(0, 15 + e).Value = Sheets(c).Cells(d, 4).Value
End If
Next d
Next a
End Sub