Apr 13 2022 06:27 PM
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
Apr 13 2022 09:04 PM
Solution
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.
Apr 13 2022 10:28 PM
That worked beautifully!!. Exactly what I was trying to do... Thank you so much!!.
Apr 13 2022 11:44 PM
You're welcome @Kenneo! Glad it worked as desired.
Apr 15 2022 05:17 AM
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!
Apr 15 2022 07:04 AM
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?
Apr 15 2022 07:27 AM
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!
Apr 15 2022 08:20 AM
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
Apr 15 2022 06:54 PM
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!!
Apr 15 2022 07:26 PM
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
Apr 15 2022 07:45 PM
Apr 15 2022 08:55 PM
Apr 13 2022 09:04 PM
Solution
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.