Macros run fine separately, but not all together?

Trikageon

Board Regular
Joined
Apr 29, 2010
Messages
51
Hey all! I have 3 separate macros that, if run separately they run great! The problem is, however, that if I run them as part of a master macro (i.e. - 1 macro that sequentially calls them all), excel seems to freeze after the first one! What's up with that?! Any suggestions?
 

James_B

Active Member
Joined
Jun 11, 2009
Messages
447
When macros' running depends on things like order of running, it's normally (in my opinion) because sheets and books haven't been fully referenced, i.e. because the macros are relying on certain things being active at kick-off point.
 

Trikageon

Board Regular
Joined
Apr 29, 2010
Messages
51
It would help a lot if you could post your code.
I would, but it's really long and I didn't want to subject you all to that...I was just wondering if there was a theoretical answer. If you want to have a peak, though, you're welcome to. How do I make it come up in that cool little box I see everyone using, though?
 

Trikageon

Board Regular
Joined
Apr 29, 2010
Messages
51
When macros' running depends on things like order of running, it's normally (in my opinion) because sheets and books haven't been fully referenced, i.e. because the macros are relying on certain things being active at kick-off point.
I wonder why they're not staying active like they do when I run them individually, though. I mean, in theory, the master macro is doing the same thing as I was when I recorded it (by selecting each of the sub macros individually) and it worked. Hmm...
 

James_B

Active Member
Joined
Jun 11, 2009
Messages
447
It's not so much about 'staying active' as such. But anyway...as HotPepper suggested, please post your code. Just surround it in [CXDE]...[/CXDE] tags (though replace the X with an O: if I type CODE, the board will put my '...' in code!
 

Trikageon

Board Regular
Joined
Apr 29, 2010
Messages
51
There are 3 macros I'm combining here:

Macro 1:
Rich (BB code):
Sub PD296Detail1()
'
' PD296Detail1
' Macro recorded 5/10/2010 by Derek Scranton
'
'
Application.DisplayAlerts = False
        Application.ScreenUpdating = False
  'Initial Setup
    Workbooks.OpenText Filename:= _
        "\\Cisora12\it-windev_dscranton\pd296\1\PD296.prt", Origin:=437, StartRow:= _
        1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(33, 1), Array(49 _
        , 1), Array(65, 1), Array(80, 1), Array(93, 1)), TrailingMinusNumbers:=True
 
    'Format Page
    Columns("A:F").Select
    Columns("A:F").EntireColumn.AutoFit
    ActiveWindow.SmallScroll Down:=3
    Rows("1:25").Select
    Range("A25").Activate
    Selection.Delete Shift:=xlUp
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "=RC[1]&RC[2]"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "=RC[1]&RC[2]"
    Range("A4").Select
    Selection.ClearContents
    Range("D4").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],m/dd/yy)"
    Range("D4").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""m/dd/yy"")"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "=RC[1]&RC[3]"
    Range("A7").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(R[-1]C,"": "",R[-1]C[1],"" "",R[-1]C[2])"
    Range("A7").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A7").Cut Destination:=Range("A6")
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C[4]&R[-1]C[5]"
    Range("A1:A7").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B1:E7").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("F6").Select
    Selection.ClearContents
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Based on Report Run:"
    Range("D1").Select
    Selection.Cut Destination:=Range("E1")
    Range("E1").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "at:"
    Range("E2").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("E1:F2").Select
    Selection.Cut Destination:=Range("H1:I2")
    Columns("E:F").Select
    Selection.Delete Shift:=xlToLeft
    Range("E9").Select
    ActiveCell.FormulaR1C1 = "Increase"
    Range("F9").Select
    ActiveCell.FormulaR1C1 = "Decrease"
    Range("G8").Select
    ActiveCell.FormulaR1C1 = "Revised"
    Range("G9").Select
    ActiveCell.FormulaR1C1 = "YTD"
    Range("D10").Select
    Selection.AutoFill Destination:=Range("D10:G10"), Type:=xlFillDefault
    Range("D10:G10").Select
    Range("H14").Select
    Columns("B:B").EntireColumn.AutoFit
    Columns("B:G").Select
    Columns("B:G").EntireColumn.AutoFit
    Range("H8").Select
    Columns("B:B").ColumnWidth = 12
    Columns("B:G").Select
    Selection.ColumnWidth = 12
    Range("H9").Select
    ActiveCell.FormulaR1C1 = "Explanation"
    Range("G10").Select
    Selection.AutoFill Destination:=Range("G10:H10"), Type:=xlFillDefault
    Range("G10:H10").Select
    Columns("H:H").ColumnWidth = 17.57
    Range("H13").Select
    Columns("H:H").ColumnWidth = 26.71
    Range("F1:G2").Select
    Selection.Cut Destination:=Range("G1:H2")
    Range("H4").Select
    ActiveWindow.SmallScroll Down:=-12
 
    'Delete Headers
    Columns("A:B").Select
    Selection.Insert Shift:=xlToRight
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("A1:A2").Select
    Selection.AutoFill Destination:=Range("A1:A1054"), Type:=xlFillDefault
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Line"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Keep"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Desc"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Prior YTD"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Current Yr"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Current YTD"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Increase"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "Decrease"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "YTD"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Explanation"
    Range("B15").Select
    ActiveCell.Formula = "=IF(OR(RIGHT(C5,5)=""PD296"",RIGHT(C6,5)=""PD296"",RIGHT(C7,5)=""PD296"",RIGHT(C8,5)=""PD296"",RIGHT(C9,5)=""PD296"",RIGHT(C10,5)=""PD296"",RIGHT(C11,5)=""PD296"",RIGHT(C12,5)=""PD296"",RIGHT(C13,5)=""PD296"",RIGHT(C14,5)=""PD296"",RIGHT(C15,5)=""PD296""),""Delete"","""")"
    Selection.Copy
    Range("B16").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Columns("B:B").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1:J1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Selection.AutoFilter Field:=2, Criteria1:="Delete"
    With ActiveSheet.AutoFilter.Range
        .Offset(1, 0).Resize(.Rows.Count - 1, 20).SpecialCells(xlCellTypeVisible).Delete
    End With
    Selection.AutoFilter Field:=2
 
    'Remove Superfluous Rows
    Range("B2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(OR(R[-1]C[1]=""Cost Per Stop"",R[-1]C=""Delete""),""Delete"","""")"
    Range("B2").Select
    Selection.Copy
    Range("B3:B956").Select
    ActiveSheet.Paste
    Selection.AutoFilter Field:=2, Criteria1:="Delete"
    With ActiveSheet.AutoFilter.Range
        .Offset(1, 0).Resize(.Rows.Count - 1, 20).SpecialCells(xlCellTypeVisible).Delete
    End With
    Selection.AutoFilter Field:=2
 
    'Add x's
    Range("c1000").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveCell.Offset(0, 4).Select
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "x"
 
 
    'Set Print Area
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$9:$11"
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.25)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
    End With
    ActiveWindow.View = xlPageBreakPreview
    ActiveWindow.SmallScroll Down:=-78
    ActiveWindow.SmallScroll Down:=-57
    ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
    ActiveWindow.View = xlNormalView
 
 
    'Determine the nature of cells in increase and decrease columns
    Range("G12").Select
    ActiveCell.Formula = "=IF($C12=""Sub-total"",SUMIF($M:$M,""Sum""&KK11,G:G),IF(AND(ISTEXT($F11),ISBLANK($C11)),"""",IF(AND(ISBLANK($F10),ISTEXT($F11)),""Box"",IF(ISTEXT($F11),""Total"",IF(ISBLANK($F12),"""",IF(ISTEXT($F12),$F12,""Box""))))))"
    Selection.Copy
    Range("G13").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Range("h12").Select
    ActiveCell.Formula = "=IF($C12=""Sub-total"",SUMIF($M:$M,""Sum""&KK11,G:G),IF(AND(ISTEXT($F11),ISBLANK($C12)),"""",IF(AND(ISBLANK($F10),ISTEXT($F11)),""Box"",IF(ISTEXT($F11),""Total"",IF(ISBLANK($F12),"""",IF(ISTEXT($F12),$F12,""Box""))))))"
    Selection.Copy
    Range("h13").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
 
    'Create Sumif Data
    Range("k12").Select
    ActiveCell.Formula = "1"
    Range("k13").Select
    ActiveCell.Formula = "=IF(c13=""Sub-total"",PD296!K12+1,PD296!K12)"
    Selection.Copy
    Range("k14").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Range("L12").Select
    ActiveCell.Formula = "=if($h12=""Total"",""Sum"","""")"
    Selection.Copy
    Range("L13").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Columns("L:L").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("M12").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",CONCATENATE(RC[-1],RC[-2]))"
    Range("M12").Select
    Selection.Copy
    Range("M13").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=-9
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.SmallScroll ToRight:=3
 
    'Create Autosums
    Range("N12").Select
    ActiveCell.Formula = "1"
    Range("N13").Select
    ActiveCell.Formula = "=IF(F13=""---------------"",N12+1,N12)"
    Selection.Copy
    Range("N14").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Range("O12").Select
    ActiveCell.Formula = "=IF($L12=""Sum"","""",IF(AND($L12="""",ISNUMBER($F12)),CONCATENATE(""Total"",$N12),""""))"
    Selection.Copy
    Range("O13").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
 
    'YTD Formulas
    Range("I12").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF(OR(ISTEXT(RC[-1]),RC[-1]=""""),"""",RC[-3]+RC[-2]-RC[-1])"
    Range("I12").Select
    Selection.Copy
    Range("I13").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Columns("G:H").Select
    Application.CutCopyMode = False
    Selection.FormatConditions.Delete
 
    'Place Red Boxes
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""box"""
    With Selection.FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 3
    End With
    With Selection.FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 3
    End With
    With Selection.FormatConditions(1).Borders(xlTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 3
    End With
    With Selection.FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 3
    End With
    'Revised YTD Totals
    Range("I12").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF(ISTEXT(RC[-3]),RC[-3],IF(RC[-3]="""","""",RC[-3]+RC[-2]-RC[-1]))"
    Range("I12").Select
    Selection.Copy
    Range("I13").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Range("G13").Select
    Application.CutCopyMode = False
    Range("J12").Select
    ActiveWindow.SmallScroll Down:=-6
    Range("J13").Select
    Application.FindFormat.Borders(xlDiagonalDown).LineStyle = xlNone
    Application.FindFormat.Borders(xlDiagonalUp).LineStyle = xlNone
    With Application.FindFormat.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 3
    End With
    With Application.FindFormat.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 3
    End With
    With Application.FindFormat.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 3
    End With
    With Application.FindFormat.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 3
    End With
 
   'Hard Code Red Boxes
    Dim AppWord         As Word.Application
 
    Set AppWord = CreateObject("Word.Application")
    AppWord.Visible = True
 
    Application.Range("H13:H300").Select
    Application.Selection.Copy
    AppWord.Documents.Add
    AppWord.Selection.Paste
    AppWord.ActiveDocument.Tables(1).Range.Select
    AppWord.Selection.Copy
    AppWord.Quit
 
    Application.CutCopyMode = False
 
    Set AppWord = Nothing
 
    Range("G13").Select
    ActiveSheet.Paste
    Range("H13").Select
    ActiveSheet.Paste
 
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
 
End Sub
Macro 2:
Rich (BB code):
Sub PD296Detail2()
'
' PD296Detail2 Macro
' Macro recorded 5/26/2010 by Derek Scranton
'
'
Dim rng As Range
 
'With ActiveSheet.Range("g12:g300")
With ActiveSheet.Range("g12", ActiveSheet.Range("g65536").End(xlUp))
  Set rng = .Find(What:="Total", LookAt:=xlWhole, LookIn:=xlValues)
 
  If Not rng Is Nothing Then
    Do
      rng.FormulaR1C1 = "=SUMIF(C15,""Total""&R[-2]C[7],C7)"
      Set rng = .FindNext(rng)
    Loop While Not rng Is Nothing
  End If
End With
With ActiveSheet.Range("h12", Range("g65536").End(xlUp))
  Set rng = .Find(What:="Total", LookAt:=xlWhole, LookIn:=xlValues)
 
  If Not rng Is Nothing Then
    Do
      rng.FormulaR1C1 = "=SUMIF(C15,""Total""&R[-2]C[6],C8)"
      Set rng = .FindNext(rng)
    Loop While Not rng Is Nothing
  End If
End With
Columns("k:o").Select
    Selection.EntireColumn.Hidden = True
End Sub
Macro 3:
Rich (BB code):
Sub PD296Detail3()
'
' PD296Detail3 Macro
' Macro recorded 5/26/2010 by Derek Scranton
'
 Application.ScreenUpdating = False
 
  'Remove "Box"
    Range("G13:H300").Select
    Application.FindFormat.Clear
    Application.FindFormat.NumberFormat = "General"
    With Application.FindFormat
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    With Application.FindFormat.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    With Application.FindFormat.Borders(xlLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = 3
    End With
    With Application.FindFormat.Borders(xlRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = 3
    End With
    With Application.FindFormat.Borders(xlTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = 3
    End With
    With Application.FindFormat.Borders(xlBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = 3
    End With
    Application.FindFormat.Borders(xlDiagonalDown).LineStyle = xlNone
    Application.FindFormat.Borders(xlDiagonalUp).LineStyle = xlNone
    Application.FindFormat.Interior.ColorIndex = xlNone
    Application.FindFormat.Locked = True
    Application.FindFormat.FormulaHidden = False
    Application.CutCopyMode = False
    Cells.Replace What:="box", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=False
 
    Application.ScreenUpdating = True
End Sub
The one that unites them is this:
Rich (BB code):
Sub PD296Detail()
'
' PD296Detail Macro
' Macro recorded  by Derek Scranton
'
'
    Application.Run "PERSONAL.XLS!PD296Detail1"
    Application.Run "PERSONAL.XLS!PD296Detail2"
    Application.Run "PERSONAL.XLS!PD296Detail3"
End Sub
 

Trikageon

Board Regular
Joined
Apr 29, 2010
Messages
51
FYI, Upon further testing, I've discovered that it can run Macro 1&2 together just fine...it's the 3rd that seems to have the problem being run in tandem
 

Forum statistics

Threads
1,081,845
Messages
5,361,663
Members
400,643
Latest member
RockStar89

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top