How to copy formatting from another cell into the active cell using VBA

[Hafsi007]

Hello!

I am trying to create a timesheet for recording hours worked by staff. I have created command buttons for Annual Leave and Sick Leave with corresponding buttons to remove Annual Leave/Sick leave. If the annual leave button is pressed it will turn the cells purple and merge into one cell. I am trying to do the command button for removing annual leave.

The staff would click on the relevant cell that has the annual leave purple on it and then click this "- Annual Leave button". When clicked, I want the button to copy the formatting from cells S3:U3 to the active cell (because there are numerous conditions working in the background such as change colour for weekends and stay white for weekdays etc). I am trying to do this by recording a macro. My issue is that when I click away from the active cell to copy S3:U3 range, how do I tell the macro what the active cell was? The active cell needs to be the cell active at the start before the command button is pressed.

I hope this makes sense and the below pic helps.

 

[Peter_SSs]

Welcome to the MrExcel board!

Try having your command button run something like this. Test with a copy of your workbook.

Sub RemoveAnnualLeave()
  Range("S3:U3").Copy
  ActiveCell.PasteSpecial xlPasteFormats
  Application.CutCopyMode = False
End Sub

.. or it may be that you actually want this instead

ActiveCell.PasteSpecial xlPasteFormats
ActiveCell.PasteSpecial xlPasteAllUsingSourceTheme

 

[Hafsi007]

This works Thank you for this - much appreciated.

I wonder if you can help with another issue. I am now trying to protect the sheet but the macros does not run once protected. I have searched the forum and found the below code:

Private Sub Workbook_Open()

Dim Sh As Worksheet

For Each Sh In Worksheets
Sh.Protect UserInterFaceOnly:=True
Next

End Sub

I have added the above code to ThisWorkbook and then tried to run the macros again but it still does not work. Any ideas?

Thanks!

 

[Peter_SSs]

A simple way is to have the code unprotect the sheet at the start and re-protect at the end.
Something like this.
Change the password to match yours or just remove the password parts if you are not using a password.

Sub RemoveAnnualLeave()
  ActiveSheet.Unprotect Password:="abc"
  Range("S3:U3").Copy
  ActiveCell.PasteSpecial xlPasteFormats
  Application.CutCopyMode = False
  ActiveSheet.Protect Password:="abc"
End Sub