Script works in Debugger but doesn't when Macro runs

Hi all,

I'm very new to VB and macro programing and I am trying to do a very simple Macro for merging data. My problem is that the macro works when I am stepping through in debugger, but does not work when I am in excel and I use the shortcut key (crtl + m) to run the macro. What happens when it doesn't work properly is it selects rows 2 and 3 and tries to paste them to the new workbook and I get an error saying the cells are not the correct type. I don't want it to do this because rows 2 and 3 are titles and have nothing to do with the data I am moving. As I said before, when I open VBE and step through the code it works just fine. The error only happens when there is no data on the initial work sheet.

Thanks

Geoff

Application.CutCopyMode = False
    Range("A3").Select
    Do Until ActiveCell.Value = 0
        ActiveCell.Offset(rowOffset:=1).Activate
    Loop
        ActiveCell.Offset(rowOffset:=-1).Activate
    If ActiveCell.Value = ("Inspector") Then Application.Quit
    Range("A3", ActiveCell).EntireRow.Copy
    '
    Workbooks.Open Filename:= _
            "\:confused:\H&S\Construction Program\Material Hoists\Material Hoist History.xls"
    Range("A3").Select
    Do Until ActiveCell.Value = 0
        ActiveCell.Offset(rowOffset:=1).Activate
    Loop
    Selection.PasteSpecial
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    Windows("Material Hoist Personal.xls").Activate
    Do Until ActiveCell.Value = 0
        ActiveCell.Offset(rowOffset:=1).Activate
    Loop
    ActiveCell.Offset(rowOffset:=-1).Activate
    Range("A3", ActiveCell).EntireRow.Delete
    ActiveWorkbook.Save
    Application.Quit

Hi Leith,

Thanks for your help on this. I posted the majority of the macro. I left out the Sub and End Sub, but I will post the whole thing again just to be sure. I'll give a more detailed explination of what I am trying to do. There will be 2 possibilities. If the macro is run and there is no data on the sheet (aside from the titles) and if the macro is run and there is data on the sheet. Right now the macro works just fine if there is data on the sheet and it works just fine in the debugger if there is no data on the sheet, but screws up when the macro is run with no data on the sheet. Basically what it is supposed to do is start, then do a search until it comes across the first empty cell in the first row and then step up one cell. If the active cell says "inspector", because there is no data on the sheet then it is to close (Inspector is in a title bar and the empty cell search will end up here if there is no data on the sheet). If it says anything else, it means there was data on the sheet and it is to run the rest of the script, which is basically selecting the rows and pasting them onto another workbook. It has to determine in the new workbook where the first blank line is so it does a search for that. What screws up when I run the macro in the blank sheet is that the sheet doesn't close, it opens the workbook on the network drive and tries to paste the title bars onto that file which gives me an error "run time error - 13" and then "type mismatch" beneath that. I'm pretty sure that the error is becuase it is trying to paste text into a column that is reserved for a date. However, I don't want the macro to get this far, it should have close the original workbook and not even opened up the one on the network drive. It is ignoring the application.quit command and I can't figure out why. I will post the xls file if you need me to but it refers to another workbook on my network so you won't be able to see the whole thing in action anyways. Here is the complete code. Thanks again for the help.

Sub MaterialHoistWorking()
'
' Macro recorded 25/11/2009 by HowardGe
'
    Application.CutCopyMode = False
    Range("A3").Select
    Do Until ActiveCell.Value = 0
        ActiveCell.Offset(rowOffset:=1).Activate
    Loop
        ActiveCell.Offset(rowOffset:=-1).Activate
    If ActiveCell.Value = ("Inspector") Then Application.Quit
    Range("A3", ActiveCell).EntireRow.Copy
    '
    Workbooks.Open Filename:= _
            "\\H&S\Construction Program\Material Hoists\Material Hoist History.xls"
     Range("A3").Select
    Do Until ActiveCell.Value = 0
        ActiveCell.Offset(rowOffset:=1).Activate
    Loop
    Selection.PasteSpecial
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    Windows("Material Hoist Personal.xls").Activate
    Do Until ActiveCell.Value = 0
        ActiveCell.Offset(rowOffset:=1).Activate
    Loop
    ActiveCell.Offset(rowOffset:=-1).Activate
    Range("A3", ActiveCell).EntireRow.Delete
    ActiveWorkbook.Save
    Application.Quit
End Sub

Try to add some error handling. The simplest would be to add:

On Error Resume Next

at the beginning of the sub. That may allow you to bypass the error that is being generated.

jrdnoland and romperstomper, thanks for your help.

jrdnoland - I tried this and it stopped the error from happening but it also cut out the titles on the original sheet which I need to be there so it is still not working perfectly

romperstomper - Your suggestion stopped the moving of the title bars when the sheet is empty but the application did not exit. I understand why this is, my appliction.quit command occurs before my End Sub. I'm going to try to move it out of the Sub/End Sub section and see if this works. If it doesn't, do you have any suggestions on how to still have the application quit at the end? I was trying to get the GoTo function to go to a line of code but I don't think that is what it is supposed to do.

Thanks again all for the help

This looks like one of those problems with recoreded VBA codes and edits / adding codes

Your at work on a network, running UNC, i find this can trip up best is to test the file exists in the location and error handel (just my opinion)

Teh code runs arround Cells A3 in two workBooks - this seems to be your problem, and needs addressing

Hi Jack,

I didn't really understand a lot of what you meant here, having said that, I don't know if the network is the problem because the code works fine when it is actually coping/pasting data across the network. It doesn't work when it is not supposed to do that and just exit out of the sheet/excel.

Originally Posted by romperstomper

If you want it to quit if A3 is empty, then change the second line to:

  If Len(Range("A3").Value) = 0 Then Application.Quit

I tried this and it would still run the rest of the code for some reason. But your If Len statement was the help I needed to get it running. Thanks!

Hi all,

Got it working! Thanks to everyone for their help. Here is the final product that does what I need it to if anyone is interested.

Sub MaterialHoistWorking()
'
' Macro recorded 25/11/2009 by HowardGe
'
    If Len(Range("A3").Value) = 0 Then GoTo 24
    Application.CutCopyMode = False
    Range("A3").Select
    Do Until ActiveCell.Value = 0
        ActiveCell.Offset(rowOffset:=1).Activate
    Loop
    ActiveCell.Offset(rowOffset:=-1).Activate
    Range("A3", ActiveCell).EntireRow.Copy
    '
    Workbooks.Open Filename:= _
            "\\H&S\Construction Program\Material Hoists\Material Hoist History.xls"
     Range("A3").Select
    Do Until ActiveCell.Value = 0
        ActiveCell.Offset(rowOffset:=1).Activate
    Loop
    Selection.PasteSpecial
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    Windows("Material Hoist Personal.xls").Activate
    Do Until ActiveCell.Value = 0
        ActiveCell.Offset(rowOffset:=1).Activate
    Loop
    ActiveCell.Offset(rowOffset:=-1).Activate
    Range("A3", ActiveCell).EntireRow.Delete
    ActiveWorkbook.Save
24
    Application.Quit
End Sub