Forum Discussion
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 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
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 SubPlease find the attached with the code in place to test it.
11 Replies
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 SubPlease find the attached with the code in place to test it.
That worked beautifully!!. Exactly what I was trying to do... Thank you so much!!.
You're welcome Kenneo! Glad it worked as desired.
