Solved: Modify Copy to spreadsheet

[Emoncada]

I have the following code.

[vba]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
[/vba]

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.

[shrivallabha]

Hi,

Checking downwards is little unsafe instead you can try the opposite:
[VBA]Dim lLastRow as Long
lLastRow = Range("Y" & Rows.Count).End(xlUp).Row
If lLastRow >= 2 then
'Write Copy Code Here
End if[/VBA]

[Emoncada]

Ok, but my copy code
[vba] w.Range("Y2").Resize(w.Range("Y2").End(xlDown).Row - 1, 2).Copy[/vba]

Would still give an error correct?
I think I would need new copy code.

[shrivallabha]

Untested:
[VBA]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
LastRow = w.Range("Y" & Rows.Count).End(xlUp).Row
If LastRow >= 2 Then
w.Range("Y2").Resize(LastRow - 1, 2).Copy
Set Dest = Ws2.Range("a" & Rows.Count).End(xlUp).Offset(1)
Dest.PasteSpecial Paste:=xlPasteValues
End If
End If
Next w

Ash.Select
Application.CutCopyMode = False
Set w = Nothing
Set Ash = Nothing
Set Dest = Nothing
End Sub[/VBA]

[Emoncada]

That seemed to work.

Thanks again Shrivallabha.