PDA

View Full Version : Solved: Modify Copy to spreadsheet



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.

shrivallabha
06-04-2012, 06:44 AM
Hi,

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

Emoncada
06-04-2012, 09:51 AM
Ok, but my copy code
w.Range("Y2").Resize(w.Range("Y2").End(xlDown).Row - 1, 2).Copy

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

shrivallabha
06-04-2012, 10:45 AM
Untested:
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

Emoncada
06-04-2012, 11:34 AM
That seemed to work.

Thanks again Shrivallabha.