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

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.