PDA

View Full Version : Copying to next empty row



Anomandaris
11-05-2009, 02:53 AM
Hi guys,
I've got this code to copy certain rows from one sheet to another. But I need it to copy the info into the next empty row on the sheet. Currently it is overwriting existing data. (existing data should not be deleted)

Would be great if someone could help with the adjustment.

Thanks



Sub Allocation()

Dim ws As Worksheet, rData As Range, n As Long

Application.ScreenUpdating = False
With Sheets("Raw")
Set rData = .Range("A2", .Range("AG" & Rows.Count).End(xlUp))
End With
For Each ws In Worksheets
If ws.Name Like "*01" Then
ws.Cells(3, 1).Value = Sheets("Raw").Cells(2, 3)
ws.Cells(3, 2).Resize(, 11).Formula = _
"=SUMPRODUCT((Raw!" & rData.Columns(1).Address & "=" & ws.Name & ")*(Raw!" & rData.Columns(33).Address & "=B2)*(Raw!" & rData.Columns(8).Address & "))"
End If
Next ws
Application.ScreenUpdating = True

End Sub