Jun 30 2023 07:06 AM
Jun 30 2023 07:06 AM
I have a band and we are using Excel to figure out which songs we are going to rehearsal.
We do that through a voting system. I’ve created an IF formula (=IF(C1+D1+E1>=2, “rehearse”). What I want is this:
I need those songs marked as true (“rehearse”) to be sent to a new tab. I know I could use a filter, but I want to add additional information only to those we should rehearse.
Any chance I could do that?
Thanks for your help!
Jun 30 2023 10:53 PM
You can achieve that by using VBA (Visual Basic for Applications) in Excel. VBA allows you to automate tasks and manipulate data in Excel.
Here's an example of VBA code that copies the songs marked as "rehearse" to a new tab and allows you to add additional information:
Sub CopyRehearseSongs() Dim sourceSheet As Worksheet Dim targetSheet As Worksheet Dim sourceRange As Range Dim targetRow As Long ' Set the source sheet (where the songs are listed) and the target sheet (where the rehearse songs will be copied) Set sourceSheet = ThisWorkbook.Sheets("SourceSheetName") Set targetSheet = ThisWorkbook.Sheets("TargetSheetName") ' Define the source range containing the songs ' Assuming the songs are listed in column A starting from row 2 ' Adjust the range as per your actual data structure Set sourceRange = sourceSheet.Range("A2:A" & sourceSheet.Cells(sourceSheet.Rows.Count, 1).End(xlUp).Row) ' Clear the target sheet before copying the rehearse songs targetSheet.UsedRange.Clear ' Initialize the target row counter targetRow = 2 ' Loop through each song in the source range For Each cell In sourceRange ' Check if the song is marked as "rehearse" If cell.Value = "rehearse" Then ' Copy the song details to the target sheet sourceSheet.Range("A" & cell.Row & ":C" & cell.Row).Copy targetSheet.Range("A" & targetRow) ' Additional code to add additional information to the copied row ' Modify the code below as per your requirements targetSheet.Range("D" & targetRow).Value = "Additional Info" ' Increment the target row counter targetRow = targetRow + 1 End If Next cell End Sub
4. Replace "SourceSheetName" with the actual name of the sheet where your songs are listed.
5. Replace "TargetSheetName" with the actual name of the sheet where you want to copy the rehearse songs.
6. Adjust the source range (e.g., column and starting row) and the additional information code (targetSheet.Range("D" & targetRow).Value = "Additional Info") as per your data structure and requirements.
7. Close the VBA editor.
8. To run the code, press Alt+F8 to open the Macro dialog box, select the "CopyRehearseSongs" macro, and click "Run."
This code will copy the rows with songs marked as "rehearse" from the source sheet to the target sheet, allowing you to add additional information to the copied rows. Adjust the code as needed based on your data structure and requirements. The code should work correctly, but I have not tested it myself. Therefore, always create a backup before the test. The text, steps and code/functions were created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.