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
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