VBA: Subs do nothing unless file was manually opened

Question

I have the following Macro:

Sub Remove_Junk_Data()

Call Open_Workbook
Call Scrub_Master
Call Scrub_Change_History
Call Scrub_Update
Call Scrub_ExistingOwnership
Call Save_Scrubbed

End Sub

Sub Open_Workbook()

Workbooks.Open "https://company.sharepoint.com/sites/project/subproject/subsubproject/subsubprojecttool/tooloutput/tooloutput.xlsx"

Workbooks("tooloutput.xlsx").Activate

End Sub

Sub Scrub_Master()

Dim myValue As String
Dim LastRow As Long
Dim i As Long
Dim r As Range

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
myValue = ""

Sheets("Master").Select

For i = LastRow To 1 Step -1
Set r = Cells(i, 1)
If r.Value = myValue Then r.EntireRow.Delete
Next i

End Sub

Sub Scrub_Change_History()

Dim myValue As String
Dim LastRow As Long
Dim i As Long
Dim r As Range

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
myValue = ""

Sheets("Change History").Select

For i = LastRow To 1 Step -1
Set r = Cells(i, 1)
If r.Value = myValue Then r.EntireRow.Delete
Next i

End Sub

Sub Scrub_Update()

Dim myValue As String
Dim LastRow As Long
Dim i As Long
Dim r As Range

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
myValue = ""

Sheets("Update").Select

For i = LastRow To 1 Step -1
Set r = Cells(i, 1)
If r.Value = myValue Then r.EntireRow.Delete
Next i

End Sub

Sub Scrub_ExistingOwnership()

Dim myValue As String
Dim LastRow As Long
Dim i As Long
Dim r As Range

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
myValue = ""

Sheets("ExistingOwnership").Select

For i = LastRow To 1 Step -1
Set r = Cells(i, 1)
If r.Value = myValue Then r.EntireRow.Delete
Next i

End Sub

Sub Save_Scrubbed()

Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:= _
    "https://company.sharepoint.com/sites/project/subproject/subsubproject/subsubprojecttool/tooloutput/tooloutput.xlsx", _
    FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Application.DisplayAlerts = False

Workbooks("Master_FM_Update.xlsx").Close

End Sub

If I comment out the "Open_Workbook" sub and manually open the workbook then run the remainder of Remove_Junk_Data(), it works perfectly.

If I try to run Remove_Junk_Data with Open_Workbook active, then no errors are thrown, but the 4 middle subs dont do anything...

Has anyone ran into anything like this? Did you find a resolution? I want to click a button and have all 6 subs do their thing correctly...

Edit: With input, new macro, and it works! Thanks guys!:

Sub Remove_Junk_Data()

Workbooks.Open "https://company.sharepoint.com/sites/project/subproject/subsubproject/Subsubprojecttool/tooloutput/tooloutput.xlsx"

Dim myValue As String
Dim LastRow As Long
Dim i As Long
Dim r As Range



With Workbooks("tooloutput.xlsx").Sheets("Master")
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    myValue = ""    
    For i = LastRow To 1 Step -1
    Set r = .Cells(i, 1)
    If r.Value = myValue Then r.EntireRow.Delete
    Next i
End With

With Workbooks("tooloutput.xlsx").Sheets("Change History")
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    myValue = ""    
    For i = LastRow To 1 Step -1
    Set r = .Cells(i, 1)
    If r.Value = myValue Then r.EntireRow.Delete
    Next i
End With

With Workbooks("tooloutput.xlsx").Sheets("Update")
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    myValue = ""    
    For i = LastRow To 1 Step -1
    Set r = .Cells(i, 1)
    If r.Value = myValue Then r.EntireRow.Delete
    Next i
End With

With Workbooks("tooloutput.xlsx").Sheets("ExistingOwnership")
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    myValue = ""    
    For i = LastRow To 1 Step -1
    Set r = .Cells(i, 1)
    If r.Value = myValue Then r.EntireRow.Delete
    Next i
End With

End Sub

you will need to qualify the exact workbook and worksheet the macro should act upon for each sub. The way it works now is that the code will run against the workbook running the code. (except for the `workbook_open` sub ... so for example, `LastRow` will always find the last row in the sheet that is active when the macro is run. If that sheet has no data, your row will be 1 and those no loop will ever occur. — Scott Holtzman, Nov 17 '15 at 20:14
"The 4 middle subs don't do anything" - have you run through the code line by line to see what the code is doing [in the VBA module, press f8 one line at a time]? At which line does it no longer operate as intended / expected? — Grade 'Eh' Bacon, Nov 17 '15 at 20:15
@Scott Holtzman So I would qualify the workbook with a "Workbooks("tooloutput.xlsx").Activate" at the start of each sub instead at the end of the first sub? — user2989297, Nov 17 '15 at 20:17
@user2989297 - or `With Workbooks("tooloutput.xlsx") ... End With` that would be better. Or even better `Dim wb as Workbook | Set wb = Workbooks("tooloutput.xlsx") | With wb.` (pipes mean new line). Look up `With Blocks` if you don't understand what that is. It's a powerful way to work directly with objects in VBA that will speed up your code and and make it more readable / editable. — Scott Holtzman, Nov 17 '15 at 20:19
Or just look [one question down](http://stackoverflow.com/questions/33765258/how-to-find-a-specific-cell-value-in-separate-worksheet) ^_^; — findwindow, Nov 17 '15 at 20:22
you also have all those subs that do the same thing. why not create 1 sub and pass the workbook and sheet name as a parameter for each sheet you want to `scrub` in the main sub that calls all those subs? — Scott Holtzman, Nov 17 '15 at 20:25
@findwindow I am slowly figuring out how they are the same problem. Never would have figured that out alone. — user2989297, Nov 17 '15 at 20:28
@Scott Holtzman Initially I had them all in one sub. When it didnt work, I broke it into 4 as a shot in the dark to get it working. I'm 3 months or so into using VBA. Most of what I do is based on if it was generated by "record macro", what I found from google, or complete inelegant chaos. — user2989297, Nov 17 '15 at 20:29
your doing great @user2989297 - we all learn as we go, continuously. — Scott Holtzman, Nov 17 '15 at 20:30
@Scott Holtzman Not great enough... still not working. Same result. If I open the book using VBA, it skips over all the rest of the logic. If I open the workbook manually, the rest of the logic works perfectly. — user2989297, Nov 17 '15 at 21:03
You didn't qualify `LastRow` and study the link above again. (Hint: you're missing some periods infront of `cells`) — findwindow, Nov 17 '15 at 21:11

score 2 · Answer 1 · answered Nov 17 '15 at 21:38

I have done some changes to your code, the comments in the procedure intent to explain the changes. My advice is that besides what you are getting from sites like Stackoverflow you should also read the corresponding documentation to achieve a deeper understanding of the concepts and resources used. Nevertheless, do not hesitate to ask question as you go forward in developing your programing skills.

The code below consolidates all what you are trying to do in one procedure, there you'll see how to run repetitive code for a series of values (i.e. worksheets in this case)

Suggest to visit the following pages:

Variables & Constants, Application Object (Excel), Excel Objects

With Statement, For...Next Statement, For Each...Next Statement, If...Then...Else Statement

Worksheets Object (Excel), Worksheet Object (Excel), Range Object (Excel)

Sub Remove_Junk_Data()
Rem Use an Array Variable to List all the worksheets you want to work with
Dim aWsh As Variant, vItm As Variant
aWsh = Array("Master", "Change History", "Update", "ExistingOwnership")

Rem Declare Object Variables
Dim Wbk As Workbook
Dim Wsh As Worksheet

Dim lRowLst As Long
Dim lRow As Long

    Rem Open Workbook & Set Workbook Object Variable
    Set Wbk = Workbooks.Open("https://company.sharepoint.com/sites/project/subproject/subsubproject/subsubprojecttool/tooloutput/tooloutput.xlsx")

    Rem Loop throught the worksheet list and process each one
    For Each vItm In aWsh

        Rem Set Worksheet Object Variable
        Set Wsh = Wbk.Worksheets(vItm)

        With Wsh
            lRowLst = Cells(Rows.Count, "A").End(xlUp).Row
            For lRow = lRowLst To 1 Step -1
                With .Cells(lRow, 1)
                    If .Value2 = Empty And Not (.HasFormula) Then .EntireRow.Delete

    End With: Next: End With: Next

    Application.DisplayAlerts = False
    Wbk.Save
    Application.DisplayAlerts = True

    Workbooks("Master_FM_Update.xlsx").Close

End Sub

VBA: Subs do nothing unless file was manually opened

1 Answers1