To automatically copy marked data from Sheet 1 to Sheet 2 in Excel, you can utilize Excel's built-in functionality called Conditional Formatting combined with a simple VBA (Visual Basic for Applications) script. Here's a step-by-step guide to achieve this:
1. Set up Conditional Formatting:
- Select the range of cells in Sheet 1 where you want to mark data.
- Go to the "Home" tab, click on "Conditional Formatting," and choose "New Rule."
- Select "Use a formula to determine which cells to format."
- Enter a formula like =CELL("address")=CELL("address",Sheet1!$A$1) to highlight the entire row based on the active cell.
- Apply the formatting you want for the marked rows.
2. Write a VBA Script:
- Press "Alt + F11" to open the VBA editor.
- Go to "Insert" > "Module" to add a new module.
- Copy and paste the following VBA code:
```vba
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Target.EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
End Sub
```
3. Run the Script:
- Close the VBA editor and return to your Excel workbook.
- Now, whenever you mark a row in Sheet 1, the entire row's data will be automatically copied to the next available row in Sheet 2.
By combining Conditional Formatting with VBA, you can achieve the desired functionality of copying marked data from one sheet to another in Excel seamlessly.