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

Hafsi007

New Member
Joined
Nov 14, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
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.

1605391647251.png
 

Attachments

  • 1605391482582.png
    1605391482582.png
    75.8 KB · Views: 1

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,561
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

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

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

.. or it may be that you actually want this instead
Rich (BB code):
ActiveCell.PasteSpecial xlPasteFormats
ActiveCell.PasteSpecial xlPasteAllUsingSourceTheme
 
Last edited:

Hafsi007

New Member
Joined
Nov 14, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,561
Office Version
  1. 365
Platform
  1. Windows
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.

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

Forum statistics

Threads
1,141,204
Messages
5,704,949
Members
421,372
Latest member
Jamie11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top