Hello All, I have a workbook, with multiple worksheets. Each one of them have Active x buttons on them. I them all set to paste special values and formats. These work great the first time, but when I use my clear contents button (code below). The next time I initiate the action of copying the info, it forgets it on about half of them. If another attempt is made, it forgets even more of the formatting, even though all sheets are formatted original the same. Is there a fix for this? It is pretty annoying to have to reformat after each copy and paste. I have also included an example of a copy code for reference. Thanks in advance?
Clear button code
ActiveSheet.ShowAllData
With ActiveSheet
ActiveSheet.[A2:K500].SpecialCells(xlCellTypeConstants).ClearContents
ActiveSheet.[P2:Z500].SpecialCells(xlCellTypeConstants).ClearContents
End With
End Sub
Copy and Paste example
Dim FirstEmptyRow As Long
Dim Rng As Range
Set Rng = Range("P:Z")
Application.ScreenUpdating = False
With ActiveSheet
FirstEmptyRow = .Cells(.Rows.Count, "P").End(xlUp).Offset(1, 0).Row
ActiveSheet.Range("A2:K3000").Copy
.Cells(FirstEmptyRow, "P").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=True
End With
Rng.RemoveDuplicates Columns:=Array(1, 5), Header:=xlYes
Excel.Application.CutCopyMode = False
Application.ScreenUpdating = True
Clear button code
ActiveSheet.ShowAllData
With ActiveSheet
ActiveSheet.[A2:K500].SpecialCells(xlCellTypeConstants).ClearContents
ActiveSheet.[P2:Z500].SpecialCells(xlCellTypeConstants).ClearContents
End With
End Sub
Copy and Paste example
Dim FirstEmptyRow As Long
Dim Rng As Range
Set Rng = Range("P:Z")
Application.ScreenUpdating = False
With ActiveSheet
FirstEmptyRow = .Cells(.Rows.Count, "P").End(xlUp).Offset(1, 0).Row
ActiveSheet.Range("A2:K3000").Copy
.Cells(FirstEmptyRow, "P").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=True
End With
Rng.RemoveDuplicates Columns:=Array(1, 5), Header:=xlYes
Excel.Application.CutCopyMode = False
Application.ScreenUpdating = True