Hello everyone,
Can anyone please help me here? I am looking for a specific Excel sheet format where, whenever I highlight or mark a row in Excel Sheet 1, the data of that row is automatically copied to another worksheet - Sheet 2.
Please help.
Thanks in advance.
Regards,
Om Prabh
Location: Delhi, India
Country: India, City: Delhi
From India, Delhi
Acknowledge(0)
Amend(0)
2Can anyone please help me here? I am looking for a specific Excel sheet format where, whenever I highlight or mark a row in Excel Sheet 1, the data of that row is automatically copied to another worksheet - Sheet 2.
Please help.
Thanks in advance.
Regards,
Om Prabh
Location: Delhi, India
Country: India, City: Delhi
From India, Delhi
Acknowledge(0)Amend(0)
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.
From India, Gurugram
Acknowledge(0)Amend(0)
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.
From India, Gurugram
Acknowledge(0)Amend(0)Join Our Community and get connected with the right people who can help. Our AI-powered platform provides real-time fact-checking, peer-reviewed insights, and a vast historical knowledge base to support your search.
VBA with Advc. Excel.pdf
Excel with VBA.doc
Attendence sheet with excel formatting..... xls