Code works when stepping through, but not when running

[RavinSabre]

Hey guys, I've been working with macros for about a week or two, and while google is my friend, I think I need to reach out for some help.

When I step through the code I've posted below, it works perfectly. No errors, everything formats correctly, and I end up with the desired result for the document.

However, when I try to actually RUN the code, I get a 1004 error

runtime error 1004 method name of object _worksheet failed

This error points to the line of code i've colored red below. I've tried using the worksheet codename instead of the sheet name when referencing it, i've even tried setting it as a variable, but still no dice. Again, it works perfectly when stepping through the code with F8, but bombs when I try to run it.

Any hints for the poor sap whose boss expects miracles? lol

Sub RemoveHeaderFromWDSA_Domain_Details()
Application.ScreenUpdating = False




'Removes the column headers from the WDSA Domain Details Report Export, Leaving Only the Data. No further formatting is required for this export.


Sheets("WDSA Domain Details").Select
    Rows("1:2").Select
    Selection.Delete shift:=xlUp
Application.ScreenUpdating = True


Call RemoveHeaderFromWDSA_BP_Steps_and_Sec_Groups


    
End Sub


Sub RemoveHeaderFromWDSA_BP_Steps_and_Sec_Groups()


'Removes the column Headers from the WDSA BP Steps and Security Groups Report Export, leaving only the data. No further formatting is required for the export.
Application.ScreenUpdating = False
Sheets("WDSA BP Steps and Sec Groups").Select
    Rows("1:2").Select
    Selection.Delete shift:=xlUp
    
Application.ScreenUpdating = True




Call WDSA_BP_Security_Policies


End Sub


Sub WDSA_BP_Security_Policies()


'Removes the column headers from the WDSA BP Security Policies report Export, leaving only the data. No further formatting is required for the export
Application.ScreenUpdating = False
Sheets("WDSA BP Security Policies").Select
    Rows("1:2").Select
    Selection.Delete shift:=xlUp
Application.ScreenUpdating = True




Call BP_Initiators


End Sub




Sub BP_Initiators()


'Removes the column Headers from the WDSA BP Initiators report export, leaving only the data. No further formatting is required for the export.


Application.ScreenUpdating = False


Sheets("WDSA BP Initiators").Select
    Rows("1:2").Select
    Selection.Delete shift:=xlUp


Application.ScreenUpdating = True


Call WDSA_BP_ActionSteps


End Sub


Sub WDSA_BP_ActionSteps()


'Removes the column Headers from the WDSA BP Action Steps report export, leaving only the data. No further formatting is required for the export.


Application.ScreenUpdating = False


Sheets("WDSA BP Action Steps").Select
    Rows("1:2").Select
    Selection.Delete shift:=xlUp


Application.ScreenUpdating = True


Call WDSA_BP_SubProcess


End Sub




Sub WDSA_BP_SubProcess()


'Removes the column Headers from the WDSA BP Subprocess Steps report export, leaving only the data. No further formatting is required for the export.


Application.ScreenUpdating = False


Sheets("WDSA BP Subprocess").Select
    Rows("1:2").Select
    Selection.Delete shift:=xlUp
    
Application.ScreenUpdating = True


Call SGreportsAndTasks


End Sub




Sub SGreportsAndTasks()


'Removes the column Headers from the WDSA SG Reports and Tasks report export, leaving only the data.
'Separates the data into two sections, which will be placed into two tabs of the security matrix.


Application.ScreenUpdating = False


Sheets("WDSA SG Reports And Tasks").Select
    Rows("1:2").Select
    Selection.Delete shift:=xlUp
    
    Dim LastRow As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    Range("A1:C" & LastRow).Copy
    Range("H2").PasteSpecial
    Range("H1") = "Paste these three columns of data into sheet Security Groups in the Security Matrix"
    Range("A1:A" & LastRow).Copy
    Range("L2").PasteSpecial
    Range("D1:G" & LastRow).Copy
    Range("M2").PasteSpecial
    Range("L1") = "Paste these 5 columns of data into the SG Reports and Tasks sheet in the Security Matrix"
    Range("A:G").Delete shift:=xlLeft
    
Application.ScreenUpdating = True


Call SGMembers
    
End Sub


Sub SGMembers()


'Removes the column Headers from the WDSA SG Members report export, leaving only the data. No further formatting is required for the export.


Application.ScreenUpdating = False


Sheets("WDSA SG Members").Select
    Rows("1:2").Select
    Selection.Delete shift:=xlUp


Application.ScreenUpdating = True
    
Call WDSA_DomainsModifyView


End Sub




Sub WDSA_DomainsModifyView()


'combines the data from these two report exports in preparation for a Pivot Table conversion


Application.ScreenUpdating = False


Sheets("WDSA All domains - View").Select


        Dim LastRow As Long
        LastRow = Range("A" & Rows.Count).End(xlUp).Row


            Rows("1:2").Delete shift:=xlUp
            Range("A1:E" & LastRow).Copy
    
Sheets("WDSA All Domains - Modify").Select
        Dim last As Long
        last = Range("A" & Rows.Count).End(xlUp).Row
        
Range("A" & last + 1).PasteSpecial


Application.DisplayAlerts = False
Sheets("WDSA All Domains - View").Delete
Application.DisplayAlerts = True




Sheet2.Name = "Master_View_Modify"     <---------------------------------------------------- Error points here


Sheets("Master_View_Modify").Select
Rows("1").Delete shift:=xlUp


Application.ScreenUpdating = True




Call concatAandCinD




End Sub
Sub concatAandCinD()


'adds a concatenation to the Modify-View Worksheet
        
Application.ScreenUpdating = False


    Sheets("Master_View_Modify").Select


        Dim LastRow As Long
        LastRow = Range("A" & Rows.Count).End(xlUp).Row
        
  
    Range("D2").Formula = "=CONCAT(A2,""_"",C2)"
    Dim last As Long
    
    last = Range("E" & Rows.Count).End(xlUp).Row
    
    Range("D2").AutoFill Destination:=Range("D2:D" & last & "")
    
    Range("D2:D" & last).Copy
    Range("D2:D" & last).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Application.CutCopyMode = False
    
Application.ScreenUpdating = True


    Call GetPutCombineData
    
End Sub


Sub GetPutCombineData()




'combines data from the Get and Put worksheets into one worksheet
Application.ScreenUpdating = False


Sheets("WDSA All domains - Get").Select


        Dim LastRow As Long
        LastRow = Range("A" & Rows.Count).End(xlUp).Row


            Rows("1:2").Delete shift:=xlUp
            Range("A1:E" & LastRow).Copy
    
Sheets("WDSA All Domains - Put").Select
        Dim last As Long
        last = Range("A" & Rows.Count).End(xlUp).Row
        
Range("A" & last + 1).PasteSpecial


Application.DisplayAlerts = False


Sheets("WDSA All Domains - Get").Delete


Application.DisplayAlerts = True


Sheets("WDSA All Domains - Put").Name = "Master_Get_Put"


Sheets("Master_Get_Put").Select
Rows("1").Delete shift:=xlUp


Application.ScreenUpdating = True


Call ConcatAandCinDforGetPut


End Sub






Sub ConcatAandCinDforGetPut()


'Adds a concatenation used in the combined get-put file
    
Application.ScreenUpdating = False


    Sheets("Master_Get_Put").Select


        Dim LastRow As Long
        LastRow = Range("A" & Rows.Count).End(xlUp).Row
        
 
    Range("D2").Formula = "=CONCAT(A2,""_"",C2)"
    Dim last As Long
    
    last = Range("E" & Rows.Count).End(xlUp).Row
    
    Range("D2").AutoFill Destination:=Range("D2:D" & last & "")
    
    Range("D2:D" & last).Copy
    Range("D2:D" & last).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Application.CutCopyMode = False
    
Application.ScreenUpdating = True


Call PivotTableView_Modify


End Sub


Sub PivotTableView_Modify()




'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim lastcol As Long


Application.ScreenUpdating = False


'Insert a New Blank Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable_View_Modify").Delete
Sheets.Add before:=ActiveSheet
ActiveSheet.Name = "PivotTable_View_Modify"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable_View_Modify")
Set DSheet = Worksheets("Master_View_Modify")




'Define Data Range
LastRow = DSheet.cells(Rows.Count, 1).End(xlUp).Row
lastcol = DSheet.cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.cells(1, 1).Resize(LastRow, lastcol)




'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.cells(2, 2), _
TableName:="ViewModifyTable")


'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.cells(1, 1), TableName:="ViewModifyTable")




'Insert Row Fields
With ActiveSheet.PivotTables("ViewModifyTable").PivotFields("Domain")
.Orientation = xlRowField
.Position = 1
End With


With ActiveSheet.PivotTables("ViewModifyTable").PivotFields("Functional Area")
.Orientation = xlRowField
.Position = 2
End With


'Insert Column Fields
With ActiveSheet.PivotTables("ViewModifyTable").PivotFields("Security Group")
.Orientation = xlColumnField
.Position = 1
End With


'Format Pivot
tableactivesheet.PivotTables("ViewModifyTable").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("ViewModifyTable").TableStyle2 = "PivotStyleMedium9"
ActiveSheet.PivotTables("ViewModifyTable").RowAxisLayout xlTabularRow
ActiveSheet.PivotTables("ViewModifyTable").PivotFields("Domain").Subtotals(1) = False




Dim lastcollumn As Long
lastcollumn = cells(3, Columns.Count).End(xlToLeft).Column






On Error Resume Next
Application.DisplayAlerts = False


Sheets.Add before:=ActiveSheet
ActiveSheet.Name = "DataForViewModify"


Sheets("PivotTable_View_Modify").cells.Copy
Sheets("DataForViewModify").Select
Range("A1").PasteSpecial xlPasteValues
Worksheets("PivotTable_View_Modify").Delete
Application.DisplayAlerts = True


Range("D4").Formula = "=VLOOKUP((CONCAT($B4,""_"",D$3)),Master_View_Modify!$D$2:$E$" & LastRow & ",2,False)"


Range("D4").AutoFill Destination:=Range("D4:D" & LastRow), Type:=xlFillDefault
Range("D4:D" & LastRow).AutoFill Destination:=Range(cells(4, "D"), cells(LastRow, lastcollumn)), Type:=xlFillDefault


Sheets("DataForViewModify").cells.Copy
Range("A1").PasteSpecial xlPasteValues


cells.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False


Application.ScreenUpdating = True


Call PivotTableGet_Put


End Sub


Sub PivotTableGet_Put()


'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim lastcol As Long


Application.ScreenUpdating = False


'Insert a New Blank Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable_Get_Put").Delete
Sheets.Add before:=ActiveSheet
ActiveSheet.Name = "PivotTable_Get_Put"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable_Get_Put")
Set DSheet = Worksheets("Master_Get_Put")




'Define Data Range
LastRow = DSheet.cells(Rows.Count, 1).End(xlUp).Row
lastcol = DSheet.cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.cells(1, 1).Resize(LastRow, lastcol)




'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.cells(2, 2), _
TableName:="GetPutTable")


'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.cells(1, 1), TableName:="GetPutTable")




'Insert Row Fields
With ActiveSheet.PivotTables("GetPutTable").PivotFields("Domain")
.Orientation = xlRowField
.Position = 1
End With


With ActiveSheet.PivotTables("GetPutTable").PivotFields("Functional Area")
.Orientation = xlRowField
.Position = 2
End With


'Insert Column Fields
With ActiveSheet.PivotTables("GetPutTable").PivotFields("Security Group")
.Orientation = xlColumnField
.Position = 1
End With


'Format Pivot
tableactivesheet.PivotTables("GetPutTable").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("GetPutTable").TableStyle2 = "PivotStyleMedium9"
ActiveSheet.PivotTables("GetPutTable").RowAxisLayout xlTabularRow
ActiveSheet.PivotTables("GetPutTable").PivotFields("Domain").Subtotals(1) = False




Dim lastcollumn As Long
lastcollumn = cells(3, Columns.Count).End(xlToLeft).Column






On Error Resume Next
Application.DisplayAlerts = False


Sheets.Add before:=ActiveSheet
ActiveSheet.Name = "DataForGetPut"


Sheets("PivotTable_Get_Put").cells.Copy
Sheets("DataForGetPut").Select
Range("A1").PasteSpecial xlPasteValues
Worksheets("PivotTable_Get_Put").Delete
Application.DisplayAlerts = True


Range("D4").Formula = "=VLOOKUP((CONCAT($B4,""_"",D$3)),Master_Get_Put!$D$2:$E$" & LastRow & ",2,False)"


Range("D4").AutoFill Destination:=Range("D4:D" & LastRow), Type:=xlFillDefault
Range("D4:D" & LastRow).AutoFill Destination:=Range(cells(4, "D"), cells(LastRow, lastcollumn)), Type:=xlFillDefault


Sheets("DataForGetPut").cells.Copy
Range("A1").PasteSpecial xlPasteValues


cells.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False




Application.DisplayAlerts = False


Sheets("Master_View_Modify").Delete
Sheets("Master_Get_Put").Delete


Application.DisplayAlerts = True


Application.ScreenUpdating = True




End Sub

 

[daverunt]

Can't see anything but a lot of calls to other macros and select and paste in sheets - do you really need all those subs?
Too much of a strain to follow : )

You shouldn't need to select the sheet but you could try that to see if it gets through the code
or
You could try defining the sheet at the start of the macro it is used in.
Set wsMVM = Worksheets("Whatever sheetname is before rename")

 

[Logit]

.
What happens if you comment out that specific line ?

I don't understand what that line of code is intended to do. My understanding at this point in time is that the line actually does nothing.

 

[RavinSabre]

Hi! so that particular line of code renames a worksheet. it's pretty basic, which is why I was very confused as to why I was encountering this error. As I said, I'm very new to this, and I've tried a lot of things I found on google as far as troubleshooting for this error. There's also the additional aspect that this mess of code works perfectly when I step through it, but fails (always with the same error) when I try to actually *run* the code. Any advice is super appreciated!

 

[RavinSabre]

Hi! so that particular line of code renames a worksheet. it's pretty basic, which is why I was very confused as to why I was encountering this error. As I said, I'm very new to this, and I've tried a lot of things I found on google as far as troubleshooting for this error. There's also the additional aspect that this mess of code works perfectly when I step through it, but fails (always with the same error) when I try to actually *run* the code. Any advice is super appreciated!

As far as what happens if I comment out that line, the macro would fail. I reference the new worksheet name further in, so commenting out the name change would cause an error.

 

[Logit]

.
Have you tried this approach ?

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Sheets("Sheet2").Name = "Master_View_Modify</code>"