I believe this code will do the job for you. Just to be safe, test it on a
copy of your workbook first. To put it into your workbook, press [Alt]+[F11]
to enter the VB Editor.
Choose Insert --> Module then copy the code and paste it into the code module
and then edit as needed (I don't think it needs any, but...) . Run it from
Tools --> Macro --> Macros (pre-Excel 2003) or from the [Developer] tab (2007)
Sub FillNotComplete()
'change these Const values
'as required
'name of sheet with short list
Const sheet1Name = "main"
'column short list is in
Const mainListCol = "I"
'first row with list data
Const firstMainRow = 2
'name of sheet with long list
Const sheet2Name = "not complete"
'columns to move
Const moveCol1 = "A"
Const moveCol2 = "B"
Const moveCol3 = "F"
Const moveCol4 = "G"
Dim lastRow As Long
Dim mainSheet As Worksheet
Dim mainList As Range
Dim anyMainEntry As Range
Dim NC_Sheet As Worksheet
Set mainSheet = Worksheets(sheet1Name)
Set mainList = mainSheet.Range(mainListCol & _
firstMainRow & ":" & _
mainSheet.Range(mainListCol & Rows.Count). _
End(xlUp).Address)
Set NC_Sheet = Worksheets(sheet2Name)
'to improve performance
Application.ScreenUpdating = False
'do the real work
For Each anyMainEntry In mainList
If anyMainEntry = 1 And _
(IsEmpty(anyMainEntry.Offset(0, 1)) Or _
anyMainEntry.Offset(0, 1) = "") Then
'do the move
lastRow = NC_Sheet.Range(moveCol1 & _
Rows.Count).End(xlUp).Row + 1
'copy from A to A
NC_Sheet.Range(moveCol1 & lastRow) = _
mainSheet.Range(moveCol1 & anyMainEntry.Row)
'copy from B to B
NC_Sheet.Range(moveCol2 & lastRow) = _
mainSheet.Range(moveCol2 & anyMainEntry.Row)
'copy from F to F
NC_Sheet.Range(moveCol3 & lastRow) = _
mainSheet.Range(moveCol3 & anyMainEntry.Row)
'copy from G to G
NC_Sheet.Range(moveCol4 & lastRow) = _
mainSheet.Range(moveCol4 & anyMainEntry.Row)
'mark as completed
anyMainEntry.Offset(0, 1) = 1
End If
Next
'housekeeping
Set mainList = Nothing
Set mainSheet = Nothing
Set NC_Sheet = Nothing
'announce completion
MsgBox "Task Completed"
End Sub
Topher said:
I really am having a bad week...
I have a workbook with two sheets; 'main' & 'not complete'. On the main
sheet I have 10 columns of data, row 2 to 20 (More rows can be added so may
be more).
What I need to do is: for each row, if column I = 1 and J = "" then I need
to copy some of the data from that row (column A,B,F & G) to the sheet called
'not complete'. the cell in column J will then be changed to 1. Repeat for
each row making sure the sheet called 'not complete' does not have any blank
rows.
Hope that makes sense and you are having a better week than mine and can
help
Thanks
