Forum Discussion

mbarbosa2's avatar
mbarbosa2
Copper Contributor
Jun 30, 2023

Copying Data From One Tab to Another

Hello.

 

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!

Excel
Formulas and Functions
Macros and VBA
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jul 01, 2023

    mbarbosa2 

    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:

    1. Press Alt+F11 to open the VBA editor in Excel.
    2. Insert a new module by clicking on "Insert" in the menu and selecting "Module."
    3. In the module, paste the following code:
    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.

Resources