Macros run fine separately, but not all together?

T

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?
 
Sort by date Sort by votes
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.
 
Upvote 0
HOTPEPPER said:
It would help a lot if you could post your code.
Click to expand...

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?
 
Upvote 0
James_B said:
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.
Click to expand...

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...
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Opening files from SharePoint using VBA breaking/running incredibly slowly
Replies
0
Views
36
Moosles
M
J
  • Question Question
Macro, SaveAs in New Instance of Excel, PasteValues, Close, return to Original Workbook
Replies
1
Views
55
StephenCrump
S
R
  • Solved
How to run the same macro separately in all worksheets
Replies
6
Views
358
Alex Blakenburg
A
A
  • Solved
Pulling Specific Data from multiple separate excel files into one Master File
2 3
Replies
21
Views
548
mumps
mumps
J
  • Question Question
Filter applied to cell not working
Replies
1
Views
121
RoryA
RoryA

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top