Paste Values Works Different w/Form Control vs ActiveX Control - Why? Workaround?

[bvbull200]

I have the code below as both a Private Sub in an ActiveX button and as a standard Macro that is run by a Form Control button. It is copied and pasted from the Macro to the ActiveX Sub, so I know it is character for character the same thing. The only thing that is different is the name of the Sub.

'This is for material update
Dim WorkbookTextFile As Workbook
Dim Template As Workbook
Set Template = ThisWorkbook
Dim ConfirmTemplateSave As Integer
Dim LastRow As Long
'LastRow = ActiveSheet.Range("A:A").Cells.SpecialCells(xlCellTypeConstants).count
LastRow = ActiveSheet.Range("A" & Rows.count).End(xlUp).Row

ConfirmTemplateSave = MsgBox("Have you saved your template in case it needs to be edited?", vbYesNo, "Confirm Template Save")

If ConfirmTemplateSave = vbNo Then
Exit Sub
Else
   
    Set WorkbookTextFile = Workbooks.Add
    Template.Sheets("Material Update Template").Activate
    Range(Cells(1, 1), Cells(LastRow, 123)).Copy
    WorkbookTextFile.Sheets("Sheet1").Activate
    Range("A1").PasteSpecial xlPasteValues
    Range(Cells(1, 29), Cells(LastRow, 29)).NumberFormat = "m/d/yyyy"
    Range(Cells(1, 65), Cells(LastRow, 65)).NumberFormat = "m/d/yyyy"
    Application.DisplayAlerts = False
    fname = Application.GetSaveAsFilename(fileFilter:="Yellow Box Formatted File (*.txt), *.txt", Title:="Save As")
    ActiveWorkbook.SaveAs fname, FileFormat:=xlText, CreateBackup:=False
    ActiveWorkbook.Close SaveChanges:=False
    Application.DisplayAlerts = True
End If

When I run them, the Macro version runs fine and it pastes values from the initial sheet to the new sheet that gets created. When I run the Private Sub from the ActiveX Control, it doesn't ever paste anything. Running the Private Sub step by step, this is where nothing happens:

    Range(Cells(1, 1), Cells(LastRow, 123)).Copy
    WorkbookTextFile.Sheets("Sheet1").Activate
    Range("A1").PasteSpecial xlPasteValues

It doesn't paste anything. If I stop there, then go press enter on the worksheet, it pastes the values and I can continue to run the Sub. I have tried adding a Applications.SendKey command, for the {Enter} button, but that doesn't work either. I end up with a blank worksheet with nothing pasted to it.

Does anyone have any clues as to why this is behaving differently depending on where I run it from and how to get the xlPasteValues to actually paste the values?

I appreciate any input!

 

[Fluff]

It's nothing to do with the difference between a form control & an ActiveX control, it's to do with the location of the code.
If the code is in a regular module, any unqualified ranges will work on the active sheet, however when the code is in a sheet module, the unqualified ranges look at the sheet belonging to the code module.
So if the code is in the code module of a sheet called "Sheet2" these lines

   Range(Cells(1, 1), Cells(LastRow, 123)).Copy
    WorkbookTextFile.Sheets("Sheet1").Activate
    Range("A1").PasteSpecial xlPasteValues

will both be looking at sheet2 not sheet1