Running all Subs via Call as one, vs. Running manually creates an Error - why ???

S

sschwant

Board Regular
Joined
Jul 31, 2008
Messages
66
I am completely baffled by this and can not seem to isolate the issue in debugging mode using "run to cursor" Ctrl F8.

If I run the whole sequence from start to finish, it breaks down mid way at the point where it begins to copy AP data onto my Actuals Consol tab, and then copy in & append my JE Table to my Acutals Consol tab. Next step here is to insert a column (E), shifting over 1, and rename new Col E as "FCST". Then I copy all this data to an Actuals & FCST Consol tab, and then copy / append to that tab again with my FC Details. This tab b/comes the source data for my pivot. Some how if I string it all together - - I get 1) an extra FC col in the Actuals Consol tab, and 2) the FC total is over stated (extra rows get inserted as well I guess ... maybe some kind of lopping issue?).

Any way ... at best ... someone might be able to easily isolate my error here which would be great. At worst I set this up as a two stage process instead of one. B/c if I insert multiple breaks before the Actuals Consol steps and then run those macros manually one by one, or even just let them run on from there w/ a single break at that key point ... it works just fine!

Cross posted here w/ a link to my zipped file on OneDrive: http://answers.microsoft.com/en-us/o...6-7a6aa9acd136.

and here: Stringing a bunch of Subs together creates an error But running separately does not

Here's the direct link to oneDrive: https://1drv.ms/u/s!Agy4YBOwP78vgnjFnThVwIwgldQl
It's the "Month End" file ...

Here's the VBA for the whole 'build pivot process':
Code: 
[/I]Sub Stage_AP_data_step_1()
'
' AP_data Macro
' After running Qry_Mrg in NCT database, copy paste values into the "AP Qry Dataset" tab onto cell A1.
' This section rearranges the columns prior to merging AP & JE data on "Actuals Consol" tab.


    Application.Calculation = xlManual
    Application.ScreenUpdating = False
    Sheets("AP Qry Dataset").Select
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Cells.Select
    Selection.Font.Size = 10
    Rows("1:1").Select
        With Selection
            .HorizontalAlignment = xlGeneral
            .VerticalAlignment = xlBottom
            .WrapText = True
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
    Selection.Font.Bold = True
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = True
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
    Range("A2").Select
    ActiveWindow.FreezePanes = False
    ActiveWindow.FreezePanes = True
    Cells.Select
    Cells.EntireColumn.AutoFit
    Columns("E:E").Select
    Selection.Cut
    Range("R1").Select
    ActiveSheet.Paste
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "Div"
    Columns("N:O").Select
    Selection.Cut
    Range("T1").Select
    Selection.Insert Shift:=xlToRight
    Range("O1:S1").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    Call Stage_JE_raw_Data_Step2
End Sub
''' following macros inserted from Staging JE Data only_v4.xlsm dev workbook:


Sub Stage_JE_raw_Data_Step2()
'
    Application.ScreenUpdating = False
    Sheets("Stage raw JE data").Select
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Cells.Select
        With Selection.Font
            .Name = "Calibri"
            .Size = 10
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .ThemeFont = xlThemeFontMinor
        End With
    Rows("1:1").Select
    Selection.RowHeight = 24.75
    Rows("1:1").Select
    Selection.Font.Bold = True
        With Selection
            .HorizontalAlignment = xlGeneral
            .VerticalAlignment = xlBottom
            .WrapText = True
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlBottom
                .WrapText = True
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .MergeCells = False
            End With
    ActiveWindow.Zoom = 90
' Stage_JE_raw_data Macro - part 2
' Rearrange columns on worksheet after copying and pasting values onto blank sheet "Stage raw JE data"


'   Sheets("Stage raw JE data").Select
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "GroupID"
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Division"
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "VendorName"
    Columns("P:Q").Select
    Selection.Cut
    Columns("H:H").Select
    Selection.Insert Shift:=xlToRight
    Range("H1").Select
    Cells.EntireColumn.AutoFit
    Range("A2").Select
    Call SORT_JE
End Sub


Sub SORT_JE()
'
' SORT_JE Macro
    
    Application.ScreenUpdating = False
    Sheets("Stage raw JE data").Select
    Columns("A:R").Select
    ActiveWorkbook.Worksheets("Stage raw JE data").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Stage raw JE data").Sort.SortFields.Add Key:=Range _
        ("Q2:Q4623"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
            With ActiveWorkbook.Worksheets("Stage raw JE data").Sort
                .SetRange Range("A1:R4623")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
   Call Move_AP_Out
End Sub


Sub Move_AP_Out()
'
    Application.ScreenUpdating = False
    Sheets("Stage raw JE data").Select
    Range("Q1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$R$4622").AutoFilter Field:=17, Criteria1:= _
    "AP Accruals"
    ActiveCell.Offset(1, -16).Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    
'   Switch to AP Accls tab
    Sheets("AP Accls").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


'   Switch back to Stage raw data tab


    Sheets("Stage raw JE data").Select
    Application.CutCopyMode = False
    Selection.EntireRow.Delete
    ActiveSheet.AutoFilterMode = False
    Range("A2").Select
   Call Move_Xfers_Out
   End Sub


Sub Move_Xfers_Out()
    
    Application.ScreenUpdating = False
    Sheets("Stage raw JE data").Select
    
''   Ensure AutoFilter off before AutoFilter line
    
    ActiveSheet.AutoFilterMode = False
    Range("Q1").Select
    
''   Next line toggles AutoFilter On and Off so ensure Off before this line_
''   (as per previous comment)
    
    Selection.AutoFilter


'   ActiveSheet.Range("$A$1:$R$4062").AutoFilter Field:=17, Criteria1:= _
        "=*Xfers*", Operator:=xlAnd
        
''   Next line "AutoFilter.Range" is generic and don't need to know exact range
    
    ActiveSheet.AutoFilter.Range.AutoFilter Field:=17, Criteria1:= _
        "=*Xfers*", Operator:=xlAnd
        
        With ActiveSheet.AutoFilter.Range
            If .Columns(17).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
           
                ActiveCell.Offset(1, -16).Range("A1").Select
                Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
                Selection.Copy
            
        '   Switch to IC Transfers tab and copy paste range selection
        
                Sheets("IC Transfers").Select
                Range("A2").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
                
    '       Switch back to JE raw data tab
    
                Sheets("Stage raw JE data").Select
                Application.CutCopyMode = False
                Selection.EntireRow.Delete
    '            Selection.AutoFilter = False
                ActiveSheet.AutoFilterMode = False
                
    '' If month w/ Xfers macro appends last 3 cols at this point (or it has in past iterations)
                Else: Call Copy_Stgd_JE_data_to_TblJE
            End If
        End With
    Call Copy_Stgd_JE_data_to_TblJE
End Sub


Sub Copy_Stgd_JE_data_to_TblJE()
'
    Application.ScreenUpdating = False
    Sheets("Stage raw JE data").Select
    ActiveSheet.AutoFilterMode = False
    Range("A2:R2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
'   Switch sheets
    Sheets("tblJE").Select
'   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Call tblJE_Fill_down
 
 End Sub
        
Sub tblJE_Fill_down()


' Copy formulas down
Dim lR As Long
lR = Range("A:R").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("S2:AH" & lR).FillDown


Call Insert_Group_ID_in_tblJE


End Sub


Sub Insert_Group_ID_in_tblJE()


    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=RC[29]"
    Range("E2").Select
Dim LastRow As Long
LastRow = Range("D:E").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("E2:E" & LastRow).FillDown


Call Copy_AP_Data_Onto_Actuals_Consol_tab


End Sub




Sub Copy_AP_Data_Onto_Actuals_Consol_tab()


'   Copies staged AP Qry data, then calls new_Copy_JE macro to copy from TblJE


    Application.ScreenUpdating = False
    Sheets("AP Qry Dataset").Select
    Range("O2:S2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Actuals Consol").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Call new_copy_tbl_JE
        
End Sub
Sub new_copy_tbl_JE()


    Application.ScreenUpdating = False
    Sheets("tblJE").Select
    Range("E2:I2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    
''  Switch to Actuals Consol tab - - copy and append TblJE to APQry dataset at bottom row


    Sheets("Actuals Consol").Select
''  insert newly tested code
    Dim LastRow As Long
LastRow = Range("A:E").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("A" & LastRow).Select
Selection.Offset(1, 0).Range("A1").Select


    
''    Range("A2").Select
''    Range(Selection, Selection.End(xlDown)).Select
''    Range("A3").Select
''    Selection.End(xlDown).Select
''    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Call Insert_1_Col_for_FCST
End Sub


Sub Insert_1_Col_for_FCST()


    Sheets("Actuals Consol").Select
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "FCST"
'    Range("A1").Select


    Call Copy_Actuals_Consol_to_Actuals_FC_Consol
End Sub




Sub Copy_Actuals_Consol_to_Actuals_FC_Consol()
'
' Copy_Actuals_Consol_to_Actuals_FC_Consol Macro
'


    Application.ScreenUpdating = False
    Sheets("Actuals Consol").Select
    Range("A2:F2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Actuals and FC Consol").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Call Copy_FC_Details_to_Actuals_FC_Consol


End Sub




Sub Copy_FC_Details_to_Actuals_FC_Consol()
'
' Copy_FC_to_Consol Macro
' Forecast data will NOT always have a Group ID (there may be blanks) so pulling in Actuals first
' macro right above:  Sub Copy_Actuals_Consol_to_Actuals_FC_Consol()


    Application.ScreenUpdating = False
    Sheets("FC Details").Select
    Range("A2:E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
'   Switch back and paste values (this section copied in from new copy tableJE)to replace code below


    Sheets("Actuals and FC Consol").Select
    
'   inserting new test code
    
        Dim LastRow1 As Long
LastRow1 = Range("A:F").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("A" & LastRow1).Select
Selection.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
'    Range("A2").Select
'    Range(Selection, Selection.End(xlDown)).Select
'    Range("A3").Select
'    Selection.End(xlDown).Select
'    ActiveCell.Offset(1, 0).Range("A1").Select
'    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
    
'    Range("A2").Select
'    ActiveCell.Offset(1, 0).Range("A1").Select
'    Range(Selection, Selection.End(xlDown)).Select
'    Selection.End(xlDown).Select
'    ActiveCell.Offset(1, 0).Range("A1").Select
'    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    


    
' Copy down formula for Vlookup of Division name
    
'    Range("C2").Select
'    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'Dept look-up '!C[-1]:C[1],3,0)"
'    Range("C2").Select
'    Selection.AutoFill Destination:=Range("C2:C8959")
'    Range("C2:C8959").Select
'    Range("A1").Select
    
''' New Copy Down formula


    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'Dept look-up '!C[-1]:C[1],3,0)"
    Range("C2").Select
Dim Division_LastRow As Long
Division_LastRow = Range("A:B").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("C2:C" & Division_LastRow).FillDown


    Columns("E:F").Select
    Selection.Style = "Comma"
    Call Refresh_Pivot


End Sub


Sub Refresh_Pivot()
    Sheets("PT").Select
    Range("A5").Select
    Application.Calculation = xlAutomatic
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    MsgBox "Done"
End Sub
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
I don't have Excel at the moment, but a good start would be to clean up all the Select.Selection lines
In most cases a Sheet / cell doesn't need to be selected to work with it !

1 example...

Code: 
Columns("E:F").Select
    Selection.Style = "Comma"

can be


Columns("E:F").Style = "Comma"
 
Upvote 0
This line in the Sub Move_Xfers_Out() macro, doesn't have a data range.
ADjusting it may help !!

Code: 
ActiveSheet.AutoFilter.Range.AutoFilter Field:=17, Criteria1:= _
        "=*Xfers*", Operator:=xlAnd

Let me know whether it helps and if not, provide the range it encompasses
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
159
shknbk2
S
N
  • Question
VBA Questions - Copying dynamic data from one tab to another and deleting filtered rows
Replies
1
Views
169
DanteAmor
DanteAmor
P
  • Solved
Counter in For Each loop
Replies
2
Views
186
Possum17
P
P
  • Solved
Recorded Macro Ammendments
Replies
3
Views
336
Joe4
Joe4
C
  • Question
Calculations require application restart
Replies
2
Views
144
cpupartsplz
C

Forum statistics

Threads
1,214,651
Messages
6,120,742
Members
448,989
Latest member
mariah3

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