SBrooky
09-20-2012, 06:11 AM
The issue is within my If statement on these lines:
ActiveWorkbook.Sheets("Data Review").Range("F5:I5").End(xlDown).Offset(1, 0) = _
ActiveWorkbook.Sheets(TLName(c)).Range("A2:D2").Value
Here is the full code:
Option Explicit
Sub dataupdate()
Dim ws As Worksheet
Dim TLName(20) As String
Dim i, c, m, l As Integer
i = 1
l = 0
m = 0
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Summary" Or _
ws.Name = "Actions Review" Or _
ws.Name = "Statistics" Or _
ws.Name = "Report" Or _
ws.Name = "TODO" Or _
ws.Name = "Data Review" _
Then i = i _
Else: TLName(i) = ws.Name
If ws.Name = "Summary" Or _
ws.Name = "Actions Review" Or _
ws.Name = "Statistics" Or _
ws.Name = "Report" Or _
ws.Name = "TODO" Or _
ws.Name = "Data Review" _
Then i = i _
Else: i = i + 1
Next ws
For c = 1 To i - 1
MsgBox TLName(c)
If ActiveWorkbook.Sheets(TLName(c)).Range("A2").Offset(l, m) Is Nothing Then
l = 0
m = m + 10
Else
ActiveWorkbook.Sheets("Data Review").Range("F5:I5").End(xlDown).Offset(1, 0) = _
ActiveWorkbook.Sheets(TLName(c)).Range("A2:D2").Value
l = l + 1
End If
Next c
End Sub
Any help?
ActiveWorkbook.Sheets("Data Review").Range("F5:I5").End(xlDown).Offset(1, 0) = _
ActiveWorkbook.Sheets(TLName(c)).Range("A2:D2").Value
Here is the full code:
Option Explicit
Sub dataupdate()
Dim ws As Worksheet
Dim TLName(20) As String
Dim i, c, m, l As Integer
i = 1
l = 0
m = 0
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Summary" Or _
ws.Name = "Actions Review" Or _
ws.Name = "Statistics" Or _
ws.Name = "Report" Or _
ws.Name = "TODO" Or _
ws.Name = "Data Review" _
Then i = i _
Else: TLName(i) = ws.Name
If ws.Name = "Summary" Or _
ws.Name = "Actions Review" Or _
ws.Name = "Statistics" Or _
ws.Name = "Report" Or _
ws.Name = "TODO" Or _
ws.Name = "Data Review" _
Then i = i _
Else: i = i + 1
Next ws
For c = 1 To i - 1
MsgBox TLName(c)
If ActiveWorkbook.Sheets(TLName(c)).Range("A2").Offset(l, m) Is Nothing Then
l = 0
m = m + 10
Else
ActiveWorkbook.Sheets("Data Review").Range("F5:I5").End(xlDown).Offset(1, 0) = _
ActiveWorkbook.Sheets(TLName(c)).Range("A2:D2").Value
l = l + 1
End If
Next c
End Sub
Any help?