vzachin
08-03-2006, 11:24 AM
hi folks,
in this thread,http://www.vbaexpress.com/forum/showthread.php?t=8367&highlight=moving+data
there is this code posted as follows:
Sub Main()
Dim i As Long
For i = TargetRow(ActiveSheet, 1) - 1 To 2 Step -1
If UCase(ActiveSheet.Cells(i, 8).Value) = "WITHDRAWN" Then
ActiveSheet.Rows(i).Copy Sheets("Notices").Cells(TargetRow(Sheets("Notices"), 1), 1)
ActiveSheet.Rows(i).Delete
End If
Next i
End Sub
Function TargetRow(ByRef ws As Worksheet, ByVal col As Long) As Long
'returns index of first empty row from bottom of sheet
'requires worksheet object and column index
TargetRow = ws.Cells(Rows.Count, col).End(xlUp).Row
If IsEmpty(ws.Cells(TargetRow, col)) Then
'in this case the column is empty
TargetRow = 1
Else
TargetRow = TargetRow + 1
End If
End Function
i want to specify the data to be copied to in Sheets("Notices") to begin an "A5". how can i modify this code?
thanks
vzach
Edited 04-Aug-06 by geekgirlau. Reason: insert vba tags
in this thread,http://www.vbaexpress.com/forum/showthread.php?t=8367&highlight=moving+data
there is this code posted as follows:
Sub Main()
Dim i As Long
For i = TargetRow(ActiveSheet, 1) - 1 To 2 Step -1
If UCase(ActiveSheet.Cells(i, 8).Value) = "WITHDRAWN" Then
ActiveSheet.Rows(i).Copy Sheets("Notices").Cells(TargetRow(Sheets("Notices"), 1), 1)
ActiveSheet.Rows(i).Delete
End If
Next i
End Sub
Function TargetRow(ByRef ws As Worksheet, ByVal col As Long) As Long
'returns index of first empty row from bottom of sheet
'requires worksheet object and column index
TargetRow = ws.Cells(Rows.Count, col).End(xlUp).Row
If IsEmpty(ws.Cells(TargetRow, col)) Then
'in this case the column is empty
TargetRow = 1
Else
TargetRow = TargetRow + 1
End If
End Function
i want to specify the data to be copied to in Sheets("Notices") to begin an "A5". how can i modify this code?
thanks
vzach
Edited 04-Aug-06 by geekgirlau. Reason: insert vba tags