Do Until Loop to copy cells to another sheet

Hi,

I have a workbook with multiple sheets and one main sheet called "Low Level Data". I want to paste certain cells from a worksheet to the next blank row in the "Low Level Data" sheet. The macro seems to be copying the cells but not pasting them anywhere!

My code is below, can anyone help?

Sub update_lowleveldata()


'Set names Scheduled dates

Dim EngineNo As String
Dim SCEngineStrip, SCModuleStrip, SCModuleSI, SCEngineSI, SCModuleBuild, SCEngineBuild, SCDespatch As String
Dim ACEngineStrip, ACModuleStrip, ACModuleSI, ACEngineSI, ACModuleBuild, ACEngineBuild, ACDespatch As String


'Declare name for Scheduled Dates & EngineNo

SCEngineNo = ActiveSheet.Range("A1")
SCEngineStrip = ActiveSheet.Range("K3")
SCModuleStrip = ActiveSheet.Range("K4")
SCModuleSI = ActiveSheet.Range("K5")
SCEngineSI = ActiveSheet.Range("K6")
SCModuleBuild = ActiveSheet.Range("K7")
SCEngineBuild = ActiveSheet.Range("K8")
SCDespatch = ActiveSheet.Range("K9")

'Declare name for Actual Dates

ACEngineStrip = ActiveSheet.Range("O3")
ACModuleStrip = ActiveSheet.Range("O4")
ACModuleSI = ActiveSheet.Range("O5")
ACEngineSI = ActiveSheet.Range("O6")
ACModuleBuild = ActiveSheet.Range("O7")
ACEngineBuild = ActiveSheet.Range("O8")
ACDespatch = ActiveSheet.Range("O9")

'search for empty row or update 'low level data' and insert all data

Sheets("Low Level Data").Select
Range("A5").Select

'If data is already entered for that engine then update
Do Until ActiveCell.Value = ""
If ActiveCell.Text = EngineNo Then

ActiveCell.Offset(0, 4).Value = SCEngineStrip
ActiveCell.Offset(0, 7).Value = SCModuleStrip
ActiveCell.Offset(0, 10).Value = SCModuleSI
ActiveCell.Offset(0, 13).Value = SCEngineSI
ActiveCell.Offset(0, 16).Value = SCModuleBuild
ActiveCell.Offset(0, 19).Value = SCEngineBuild
ActiveCell.Offset(0, 22).Value = SCDespatch

End If

'find next blank row to enter data

ActiveCell.Offset(1, 0).Select
If ActiveCell.Text = "" Then

ActiveCell.Value = EngineNo

ActiveCell.Offset(0, 4).Value = SCEngineStrip
ActiveCell.Offset(0, 7).Value = SCModuleStrip
ActiveCell.Offset(0, 10).Value = SCModuleSI
ActiveCell.Offset(0, 13).Value = SCEngineSI
ActiveCell.Offset(0, 16).Value = SCModuleBuild
ActiveCell.Offset(0, 19).Value = SCEngineBuild
ActiveCell.Offset(0, 22).Value = SCDespatch

End If

Loop

MsgBox ("Engine data has now been updated.")

End Sub

Help would be Much Appreciated!

The first problem was that you were checking against the wrong variable for the engine number. You assigned the value to SCEngineNo but the If statement checked a variable called EngineNo.

There also was no mechanism to stop the looping once the engine number or blank was found. You would fill your sheet with the same info in each row.

I've highlighted the changes to your macro.

Option Explicit

Sub update_lowleveldata()


'Set names Scheduled dates

    Dim EngineNo As String  'variable not need
    Dim SCEngineStrip, SCModuleStrip, SCModuleSI, SCEngineSI, SCModuleBuild, SCEngineBuild, SCDespatch As String
    Dim ACEngineStrip, ACModuleStrip, ACModuleSI, ACEngineSI, ACModuleBuild, ACEngineBuild, ACDespatch As String
    Dim SCEngineNo 'variable wasn't declared

    'Declare name for Scheduled Dates & EngineNo

    SCEngineNo = ActiveSheet.Range("A1")
    SCEngineStrip = ActiveSheet.Range("K3")
    SCModuleStrip = ActiveSheet.Range("K4")
    SCModuleSI = ActiveSheet.Range("K5")
    SCEngineSI = ActiveSheet.Range("K6")
    SCModuleBuild = ActiveSheet.Range("K7")
    SCEngineBuild = ActiveSheet.Range("K8")
    SCDespatch = ActiveSheet.Range("K9")

    'Declare name for Actual Dates

    ACEngineStrip = ActiveSheet.Range("O3")
    ACModuleStrip = ActiveSheet.Range("O4")
    ACModuleSI = ActiveSheet.Range("O5")
    ACEngineSI = ActiveSheet.Range("O6")
    ACModuleBuild = ActiveSheet.Range("O7")
    ACEngineBuild = ActiveSheet.Range("O8")
    ACDespatch = ActiveSheet.Range("O9")

    'search for empty row or update 'low level data' and insert all data

    Sheets("Low Level Data").Select
    Range("A5").Select

    'If data is already entered for that engine then update
    Do Until ActiveCell.Value = ""
        If ActiveCell.Text = SCEngineNo Then

            ActiveCell.Offset(0, 4).Value = SCEngineStrip
            ActiveCell.Offset(0, 7).Value = SCModuleStrip
            ActiveCell.Offset(0, 10).Value = SCModuleSI
            ActiveCell.Offset(0, 13).Value = SCEngineSI
            ActiveCell.Offset(0, 16).Value = SCModuleBuild
            ActiveCell.Offset(0, 19).Value = SCEngineBuild
            ActiveCell.Offset(0, 22).Value = SCDespatch
            GoTo endit
        End If

        'find next blank row to enter data

        ActiveCell.Offset(1, 0).Select
        If ActiveCell.Text = "" Then

            ActiveCell.Value = SCEngineNo

            ActiveCell.Offset(0, 4).Value = SCEngineStrip
            ActiveCell.Offset(0, 7).Value = SCModuleStrip
            ActiveCell.Offset(0, 10).Value = SCModuleSI
            ActiveCell.Offset(0, 13).Value = SCEngineSI
            ActiveCell.Offset(0, 16).Value = SCModuleBuild
            ActiveCell.Offset(0, 19).Value = SCEngineBuild
            ActiveCell.Offset(0, 22).Value = SCDespatch
            GoTo endit
        End If

    Loop
endit:
    MsgBox ("Engine data has now been updated.")

End Sub