Home » Forums » Open Source Learning Center » Question and Answer

VBA - IF Sheet2 Cell = X then Copy A,B,C,D to Sheet3 Question

Submitted by brhouse on 18 December, 2012 - 21:27

So I have been building a new report and I have been asked to add an additional item that is messing with my automation of the workbook

If Sheet2 Cell Hx = y then copy Sheet2 Cell Ax, Bx, Cx, Dx to Sheet3

To help understand what I am working with I have VLOOKUP formulas pulling the information into sheet2 in cells B & C, Drop down menu in A and general text in D. Each row is a person with information about them in that row - I only need the above referenced cells to be copied to Sheet3 in the event that cell H = "Accepted Offer"

I have looked at several codes for similar situations, but nothing that matches exactly what I need nor do I have the VBA knowledge to put it together myself in this case.

Definately going to start learning more about VBA as this was much more complicated than I anticipated.

‹ Response to Updating drop down based on previous cell Calendar-based visual dashboard from project progress data ›
»

Private Sub

Submitted by AndyLitch on 19 December, 2012 - 09:51.

Private Sub CommandButton1_Click()
Dim Y As Integer

For Y = 1 To 10
If Sheet3.Range("H" & Y).Value = "Accepted Offer" Then
Sheet2.Range("A" & Y & ":D" & Y).Value = Sheet3.Range("A" & Y & ":D" & Y).Value
End If
Next Y

End Sub

»

Private Sub - Problem but no Error

Submitted by brhouse on 20 December, 2012 - 21:23.

First of all thank you for your response AndyLitch!

I submit the code into a VBA module, ran it, and it did nothing, not even an error. I then attached the code to an ActiveX Control and used the button (in both Sheet2 & Sheet3) and nothing happened again. It did refresh the screen as though something was going to happen, but nothing changed or pulled into Sheet3 from Sheet2.

EDIT - Also tried to put the code into the sheet object itself.

Would you be able to think of something off the top fo your head that I am doing wrong when implementing the code?

Thanks.

»

The code needs to go in any

Submitted by AndyLitch on 21 December, 2012 - 06:56.

The code needs to go in any worksheet module and is triggered by a command button from the same worksheet.

The code only scans the first 10 rows of column H and needs to find an exact match for the search string.. the search region is easy to modify by changing the for next variables.

to verify the code is running you could add a line at the beginning such as
msgbox "code running"

»