SOLVED

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

Occasional Contributor

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 worksheets, a worksheet named "Choose Options" and another one named "Material Count"... What I'm trying to do is move the row starting at cell G2 through Cell CC2 from the "Choose Options" sheet to cell G2 through cell CC2 on the " Material Count" sheet whenever A2 on the "Choose Options" sheet =1... Is there a way to do this??. I have very little experience with excel and any help would be greatly appreciated... Thank you

11 Replies
best response confirmed by Kenneo (Occasional Contributor)
Solution

@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 

 

That worked beautifully!!. Exactly what I was trying to do... Thank you so much!!.

You're welcome @Kenneo! Glad it worked as desired.

@Subodh_Tiwari_sktneer 

 

To follow up. How would the code need to change to allow for multiple rows? For instance, how would I need to change it to allow a "1" value in A3 to move the data to G3 through CC3, A4 to G4 through CC4 and so on? Thank you!

@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?

 

@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!

 

 

@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

 

 

@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!!

@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