Copying specific lines of data from one sheet to another

[Juggernaut]

Right, this is my problem:

• I need to scan through a spreadsheet, row by row.
• If cell F(x) is blank, then it will move on to check the next row.
• If cell Fx was NOT blank, and cell Ex IS blank, it will copy that line (Cells Ax to Hx)
• It then needs to open the corresponding sheet in another workbook and paste that data into the first available (blank) line.
• It then returns to check the next line until the first 100 lines of the original sheet have all been checked and copied if appropriate.
• As neither of those 2 sheets can contain macros, this needs to be run from a 3rd workbook, the sole purpose of which is to run this macro.

At the moment I'm getting a variety of errors generally when it comes to the pasting stage, and can't get my head around the problem.

Any suggestions?
For the record, am using Excel 97 SR2 (can't upgrade - work system) and this is my code so far:

********************************************

Private Sub CommandButton1_Click()

'Prepare Excel:
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False

'Open fault sheet
Workbooks.Open FileName:="W:Monthly Fault SheetFault Sheet (MAY onwards) 2002.xls"
Workbooks.Open FileName:="W:Monthly Fault SheetDaily Fault Handoff.xls"


' ***** GENERAL NETWORK *****

Workbooks("Daily Fault Handoff.xls").Worksheets("General Network").Range("A4:H100").ClearContents

For GenNet = 4 To 100
If Workbooks("Fault Sheet (MAY onwards) 2002.xls").Worksheets("General network ").Cells(GenNet, 6) = "" Then GoTo GenNetSkip
If Workbooks("Fault Sheet (MAY onwards) 2002.xls").Worksheets("General network ").Cells(GenNet, 5) = "" Then Workbooks("Fault Sheet (MAY onwards) 2002.xls").Worksheets("General network ").Range.Cells(GenNet, 1).Cells(GenNet, 8).Copy


For GenNet2 = 4 To 100
If Workbooks("Daily Fault Handoff.xls").Worksheets("General Network").Cells(GenNet2, 1) = "" Then GoTo GenNetPaster
Next GenNet2
GoTo GenNetSkip

GenNetPaster:
Workbooks("Daily Fault Handoff.xls").Worksheets("General Network").Range("A" & GenNet2, "H" & GenNet2).Select
Selection.PasteSpecial (x1PasteAll)


GenNetSkip:
Next GenNet

'Re-set Excel
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub



[ This Message was edited by: Juggernaut on 2002-05-23 02:00 ]

[Juggernaut]

Ok, I've got a little further now - I can see where it's going wrong. Basically it's not getting the information into the clipboard properly, so when it comes to paste it has nothing to paste.

If you already have something in the clipboard, it pastes that.

The code is now:

*********************************************

Private Sub CommandButton1_Click()

'Prepare Excel:
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False

'Open fault sheet
Workbooks.Open FileName:="W:Monthly Fault SheetFault Sheet (MAY onwards) 2002.xls"
Workbooks.Open FileName:="W:Monthly Fault SheetDaily Fault Handoff.xls"


' ***** GENERAL NETWORK *****

Workbooks("Daily Fault Handoff.xls").Worksheets("General Network").Range("A4:H100").ClearContents

For GenNet = 4 To 100
Workbooks("Fault Sheet (MAY onwards) 2002.xls").Activate
If Workbooks("Fault Sheet (MAY onwards) 2002.xls").Worksheets("General network ").Cells(GenNet, 6) = "" Then GoTo GenNetSkip
If Workbooks("Fault Sheet (MAY onwards) 2002.xls").Worksheets("General network ").Cells(GenNet, 5) = "" Then Workbooks("Fault Sheet (MAY onwards) 2002.xls").Worksheets("General network ").Range("A" & GenNet, "H" & GenNet).Select
Selection.Copy

For GenNet2 = 4 To 100
If Workbooks("Daily Fault Handoff.xls").Worksheets("General Network").Cells(GenNet2, 1) = "" Then GoTo GenNetPaster
Next GenNet2
GoTo GenNetSkip

GenNetPaster:
Workbooks("Daily Fault Handoff.xls").Activate
Workbooks("Daily Fault Handoff.xls").Worksheets("General Network").Cells(GenNet, 2).Select
Selection.PasteSpecial (x1PasteAll)


GenNetSkip:
Next GenNet

Workbooks("Daily Fault Handoff.xls").Activate

'Re-set Excel
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

*********************************************

Where am I going wrong?!