Forum Discussion

Kenneo's avatar
Kenneo
Copper Contributor
Apr 13, 2022
Solved

Copy and Paste A Row of Data to Another Worksheet When a Cell Has a Value of 1

Hello all, I have been searching for almost 2 days now and cannot find the answer or figure out how to do what I'm trying to do, hopefully someone here can help me out... Basically, I have two...
excel
office 365
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Apr 13, 2022

    Kenneo 

     

    You may use VBA to achieve this. Please find attached a Macro-Enabled Workbook with Worksheet_Activate Event code on Material Count Sheet Module. Each time you activate the Material Count Sheet, the code will check the cell A2 on Choose Options Sheet and if its value is 1, it will copy the range G2:CC2 from Choose Options Sheet and paste it into the range G2:CC2 on Material Count Sheet.

     

    To implement this code to your file, follow these steps...

     

    1. Open your file.

    2. Press Alt+F11 to open VB Editor.

    3. On VB Editor, you will find the Project Explorer pane on Left Side.

    4. On Project Explorer, double click on the sheet with the name Material Count in round brackets.

    5. Step 4 will open a Code Window for Sheet Module.

    6. Copy the code given below and paste it into the opened code window.

    7. Save your file as Macro-Enabled Workbook.

     

    Code:

    Private Sub Worksheet_Activate()
Dim wsOptions   As Worksheet

Application.ScreenUpdating = False

Set wsOptions = ThisWorkbook.Worksheets("Choose Options")

If wsOptions.Range("A2").Value = 1 Then
    wsOptions.Range("G2:CC2").Copy Range("G2")
Else
    Range("G2:CC2").Clear
End If

Application.ScreenUpdating = True
End Sub

     

    Please find the attached with the code in place to test it.

     

     

     

     

Subodh_Tiwari_sktneer's avatar
Subodh_Tiwari_sktneer
Silver Contributor
Apr 15, 2022

Kenneo 

 

What's the range of input in column A? Do you manually enter 1 in column A?

 

e.g.

If you input 1 in A2, the code should copy the range G2:CC2 to Material Sheet?

If you input 1 in A3, the code should copy the range G3:CC3 to Material Sheet?

If you input 1 in A4, the code should copy the range G4:CC4 to Material Sheet?

So on so forth?

 

Kenneo's avatar
Kenneo
Copper Contributor
Apr 15, 2022

Subodh_Tiwari_sktneer 

 

The range in column A would be to A65 atleast, but I would like to be able to expand in the future. You are correct as far as A2 moves G2 through CC2, A3 moves G3 through CC3, A4 moves G4 through CC4, and so on...

 

Thanks again for your help!

 

 

  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Apr 15, 2022

    Kenneo 

     

    Please find the attached with the Worksheet_Change event code on Choose Options Sheet Module.

    As per the code, once you input 1 in column A starting from row#2, the corresponding data in that row will be copied to the Material Count Sheet.

     

    Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub

Dim r           As Long
Dim wsMaterial  As Worksheet

Set wsMaterial = ThisWorkbook.Worksheets("Material Count")

On Error GoTo Skip

If Target.Column = 1 Then
    Application.EnableEvents = False
    If Target.Value = 1 Then
        r = Target.Row
        Range("G" & r & ":CC" & r).Copy wsMaterial.Range("G" & r)
        Target.Select
    End If
End If
Skip:
Application.EnableEvents = True
End Sub

     

     

    • Kenneo's avatar
      Kenneo
      Copper Contributor
      Apr 15, 2022

      Subodh_Tiwari_sktneer 

       

      Thank you very much for your time and effort! Everything is working perfect as far as moving the data to the "Material Count" sheet, however, unlike the last code you provided, when the "1" value is removed from the A column, the data remains in the "Material Count" sheet instead of being removed. Is there something that I can do about this? thanks again!!

      • Subodh_Tiwari_sktneer's avatar
        Subodh_Tiwari_sktneer
        Silver Contributor
        Apr 15, 2022

        Kenneo 

         

        To achieve that, replace the existing code with the following one...

        Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub

Dim r           As Long
Dim wsMaterial  As Worksheet

Set wsMaterial = ThisWorkbook.Worksheets("Material Count")

On Error GoTo Skip

If Target.Column = 1 Then
    Application.EnableEvents = False
    r = Target.Row
    If Target.Value = 1 Then
        Range("G" & r & ":CC" & r).Copy wsMaterial.Range("G" & r)
    Else
        wsMaterial.Range("G" & r & ":CC" & r).Clear
    End If
    Target.Select
End If
Skip:
Application.EnableEvents = True
End Sub

         

         

Resources