Emoncada
06-04-2012, 05:33 AM
I have the following code.
Sub TotalsReport()
Dim w As Worksheet
Dim Ash As Worksheet
Dim Ws2 As Worksheet
Dim Dest As Range
Dim LastRow As Integer
Set Ash = ActiveSheet
Set Ws2 = Sheets("Totals")
For Each w In ThisWorkbook.Worksheets
If w.Name <> Ws2.Name Then
w.Range("Y2").Resize(w.Range("Y2").End(xlDown).Row - 1, 2).Copy
Set Dest = Ws2.Range("a" & Rows.Count).End(xlUp).Offset(1)
Dest.PasteSpecial Paste:=xlPasteValues
End If
Next w
Ash.Select
Application.CutCopyMode = False
Set w = Nothing
End Sub
It works fine, but I am getting a run-time error '1004', I believe it happens when there is no values beyond Y2 and when there is no values at all in Y2 or beyond.
How can i modify this code so in the case if Y2 is has a value but no other values in Y to just copy Y2. Also if Y2 has no value to skip that sheet and continue with code.
Sub TotalsReport()
Dim w As Worksheet
Dim Ash As Worksheet
Dim Ws2 As Worksheet
Dim Dest As Range
Dim LastRow As Integer
Set Ash = ActiveSheet
Set Ws2 = Sheets("Totals")
For Each w In ThisWorkbook.Worksheets
If w.Name <> Ws2.Name Then
w.Range("Y2").Resize(w.Range("Y2").End(xlDown).Row - 1, 2).Copy
Set Dest = Ws2.Range("a" & Rows.Count).End(xlUp).Offset(1)
Dest.PasteSpecial Paste:=xlPasteValues
End If
Next w
Ash.Select
Application.CutCopyMode = False
Set w = Nothing
End Sub
It works fine, but I am getting a run-time error '1004', I believe it happens when there is no values beyond Y2 and when there is no values at all in Y2 or beyond.
How can i modify this code so in the case if Y2 is has a value but no other values in Y to just copy Y2. Also if Y2 has no value to skip that sheet and continue with code.