When I step through, it works. When I run the macro, it doesn't

[Pat_The_Bat]

This macro is supposed to place an "x" in column z once a row has been copied and pasted, so that I can add items to the list without re-adding all the items on the list. When I step through the code, I watch the x get placed in column Z. But for some reason, when I run it from the button on the sheet in the wkbk, it doesn't work. The button is on a different sheet, so I thought maybe that was why, but the code specifically says "With this sheet, do this thing"

Sub GeneratePublish()


Worksheets("Doc Checklist").Visible = True




Dim LstRow As Integer
Dim dcol As String 'variable to find row range to analyze column Z for marks
Dim CpRange As String 'variable to find those rows which haven't been already copied
Dim Zcol As String
        
       Sheets("Doc Checklist").Visible = True
       
        
       With Worksheets("Doc Checklist")
            LstRow = .Range("D" & .Rows.Count).End(xlUp).Row
       End With
       Debug.Print LstRow
       
       
        
        With Worksheets("Doc Checklist")
            On Error Resume Next
            dcol = .Range("D2:D500").SpecialCells(xlConstants).Address
    
            Debug.Print dcol
     
            Zcol = .Range(dcol).Offset(, 22).Address
               
            Debug.Print Zcol
            On Error GoTo NoDocsToAdd
            CpRange = .Range(Zcol).SpecialCells(xlBlanks).Address
            Debug.Print CpRange
       
            .Range(CpRange).Offset(, -22).EntireRow.Copy
            
            
        
       
       Range(CpRange).Value = "x"
       MsgBox "Docs are Prepared for Publishing"
       
       End With
       '*************************************************************************************************************************
      Dim LstRow2 As Integer
      
       With Sheets("Publish Doc List")
       LstRow2 = .Range("D" & .Rows.Count).End(xlUp).Row
       Debug.Print LstRow2
       
       
       Worksheets("Publish Doc List").Range("A" & LstRow2 + 1).PasteSpecial xlPasteValues
       Application.CutCopyMode = False
       
       Worksheets("Publish Doc List").Activate
       Worksheets("Publish Doc List").Range("A1").Value = "x"
       Worksheets("Publish Doc List").Range("A1").Select
       End With
       


       Call InsertCats2
       'Call DateStamping
       GoTo SkipToFinish
NoDocsToAdd:
       Sheets("Doc Checklist").Visible = False
       Worksheets("Publish Doc List").Activate
       Range("A20").Select
       MsgBox "No Docs To Add"
       
SkipToFinish:
       On Error Resume Next
        Sheets("Publish Doc List").Range("D2:D499").SpecialCells(xlBlanks).EntireRow.Delete
    On Error GoTo 0
       Sheets("Doc Checklist").Visible = False
       
       Worksheets("Publish Doc List").Activate
       Range("A20").Select
       MsgBox "Doc List is ready to Publish"
       
End Sub

 

[Fluff]

You're missing a . from the start of this line

[COLOR=#ff0000].[/COLOR][COLOR=#0000ff]Range(CpRange).Value = "x"[/COLOR]
MsgBox "Docs are Prepared for Publishing"
End With

 

[Norie]

You have a few unqualified ranges in the code you posted, for example here,

Range(CpRange).Value = "x"

and here.

Range("A20").Select

PS Why all the On Error... code?

 

[steve the fish]

Looking for when you put the 'x' in the cell you see this line:

Range(CpRange).Value = "x"

This certainly doesnt qualify the sheet. It will use the activesheet at the time.

 

[Pat_The_Bat]

You're missing a . from the start of this line

[COLOR=#ff0000].[/COLOR][COLOR=#0000ff]Range(CpRange).Value = "x"[/COLOR]
MsgBox "Docs are Prepared for Publishing"
End With

Thanks Fluff. That Worked!

Why does it work when I step through then? How does the dot make it different in the one scenario, as opposed to the other. Just curious for the deeper level understanding.

 

[Fluff]

Without the dot, that line will work on the active sheet rather than "Doc Checklist" sheet.
I suspect that when you ran it using F8 you activated the "Doc Checklist" sheet so that you could see what was happening.

 

[Pat_The_Bat]

Without the dot, that line will work on the active sheet rather than "Doc Checklist" sheet.
I suspect that when you ran it using F8 you activated the "Doc Checklist" sheet so that you could see what was happening.

Your suspicion is correct. Makes sense.

Thanks a million!

 

[Fluff]

You're welcome