PDA

View Full Version : Copy to another worksheet



Chente28
04-05-2006, 10:29 AM
I need help creating a macro that will copy certain cells from one worksheet and paste them to another. In the example I included I marked the cells i need to copy with blue.

I get this list every week. The problem I have is that sometimes the number of people in each group varies. Like this week for example there are 14 people in GROUP1 and next week there will be 13. So my macro wouldn't work each time. Can anyone help me? :dunno

Thanks,

Alex

jindon
04-05-2006, 07:08 PM
Hi
try


Sub test()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim r As Range, ff As String
Set ws1 = Sheets("sheet1")
Set ws2 = Sheets("sheet2")
Set r = ws1.Columns("a").Find(1, , , xlWhole)
If Not r Is Nothing Then
ff = r.Address
Do
With ws1.Range(r, r.End(xlDown)).Offset(, 1)
Union(.Resize(, 2), .Offset(, 6)).Copy
ws2.Range("a" & Rows.Count).End(xlUp).Offset(1). _
PasteSpecial xlPasteValues
End With
Set r = ws1.Columns("a").FindNext(r)
Loop Until ff = r.Address
End If
End Sub

Chente28
04-05-2006, 09:15 PM
jindon,

Thanks :) It's perfect.