Getting a macro that when it detects a blank cell to run 2 other macros and then end.

[bracken752]

Hey all,

I have a macro that when the sheet opens, it will automatically go to a website and pull 20-30 (This number can change each time, it depends on how much data have been put on in any given week) tables worth of data.

I have this Macro working however at the end of the macro I want it to remove some of the sheets that are no longer need (The data sheet used to pull the data in the first place and a 'Welcome' Sheet) again, I have a working macro for this (but cant get it to worth in one macro) and after this macro to save the file and publish it.

The macros are as follows:

Macro to go and fetch all of the data, using concatenated URLs to go and fetch the data from different cells D28:D63 (but this can change depending on factors) so I have a If blank cell on there)

Private Sub Workbook_Open()

For Each Cell In Range("InputSheet!D28:D100")
If IsEmpty(Cell) Then Exit For


Application.ScreenUpdating = False
ActiveWorkbook.Worksheets.Add
Selection.Copy
Application.CutCopyMode = False
Range("D" & Rows.Count).End(xlUp).Offset(1).Select
 With ActiveSheet.QueryTables.Add(Connection:="URL;" & Cell.Value, Destination:=Range("A1"))
.Name = "names"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
 End With
    ActiveSheet.Name = Range("B10").Value & " " & Range("C10").Value
    removeheaders
      
Next Cell


Clearupend
Save


End Sub

Macro to clear up the now useless sheets (only needed to get the information in the first place).

Sub Clearupend()

Application.DisplayAlerts = False
Worksheets("InputSheet").Delete
Worksheets("Data").Delete
Application.DisplayAlerts = True


End Sub

Macro to Save and Publish the file to a location on one of our servers to be viewed as html file (or as .xlm if they prefer hence the save and publish).

Sub Save()ActiveWorkbook.SaveCopyAs Filename:="DIRECTORYREMOVED" & ActiveWorkbook.Name


With ActiveWorkbook.PublishObjects.Add(xlSourceWorkbook, _
        "DIRECTORYREMOVED\Workbookname.htm", , , xlHtmlStatic, _
        "Workbookname_15399", "")
        .Publish (True)
        .AutoRepublish = False
    End With
    ChDir "DIRECTORYREMOVED"
End Sub

Any suggestions would be great <3 you guys in advanced.

 

[bracken752]

Any ideas? Spent all weekend trying to get my head around this but no joy .

 

[bracken752]

shameless bump :D

 

[bracken752]

.

 

[craig.penny]

This probably won't make a difference but you could try it (See my last note below about this name if you want to try this method):

Application.Run "Clearupend"
Applicatin.Run "Save"

Perhaps a more straight forward approach would be to put all the code in one sub:

[Private Sub Workbook_Open()

For Each Cell In Range("InputSheet!D28:D100")
If IsEmpty(Cell) Then Exit For


Application.ScreenUpdating = False
ActiveWorkbook.Worksheets.Add
Selection.Copy
Application.CutCopyMode = False
Range("D" & Rows.Count).End(xlUp).Offset(1).Select
 With ActiveSheet.QueryTables.Add(Connection:="URL;" & Cell.Value, Destination:=Range("A1"))
.Name = "names"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
 End With
    ActiveSheet.Name = Range("B10").Value & " " & Range("C10").Value
    removeheaders
      
Next Cell


Application.DisplayAlerts = False
Worksheets("InputSheet").Delete
Worksheets("Data").Delete
Application.DisplayAlerts = True

With ActiveWorkbook.PublishObjects.Add(xlSourceWorkbook, _
        "DIRECTORYREMOVED\Workbookname.htm", , , xlHtmlStatic, _
        "Workbookname_15399", "")
        .Publish (True)
        .AutoRepublish = False
End With
ChDir "DIRECTORYREMOVED"


End Sub

Personally, I avoid using a name like Save for a sub or Cell for a variable. "SaveSub", "SavePublish", "rCell", "Cell1", etc. or something else to alter the word slightly could help to avoid confusion. Take that with a grain of salt.