VBA copy cells that meet criteria to another sheet

Question

I am brand new to VBA. I want to copy a series of cells from Sheet1 to Sheet2 if the cells on Sheet 2 match a value listed in the first column. For example, I would like to start with SHEET1 and then have the data look like SHEET2 below:

SHEET1
A2=Adam B2=95 C2=92 D2=97 E2=67 F2 blank G2 blank H2 blank
A3=Cindy B3 blank C4 blank D4 blank E4=61 F4=94 G4=95 H4=95

SHEET2
A2=Adam B2=95 C2=92 D2=97 E2=67
A3=Cindy B3=94 C4=95 D4=95 E4=61

In this example, I want to first copy the information from Sheet1 B2:D2 to Sheet2 B2:2. Then I would like to copy the information from Sheet1 F3:H3 and fill in the blanks within Sheet 2 B3:D3. All the information in column E should stay where it is.

Any help would be much appreciated!!

Edit My (Tony Dallimore) attempt to convert the descriptions of Sheet1 and Sheet2 into something more visual:

Sheet1
   |  A   |  B   |  C   |  D   |  E   |  F   |  G   |  H   |
 2 |Adam  |    95|    92|    97|    67|      |      |      |
 3 |Cindy |      |      |      |      |      |      |      |
 4 |      |      |      |      |    61|    94|    95|    95|

Sheet2
   |  A   |  B   |  C   |  D   |  E   |  F   |  G   |  H   |
 2 |Adam  |    95|    92|    97|    67|      |      |      |
 3 |Cindy |    94|      |      |      |      |      |      |
 4 |      |      |    95|    95|    61|      |      |      |

err... what exactly have you done so far? this isn't a 'help me' site. You post programming issues here and people might help based on how the solution would also benefit the general programming community. Please post your existing code and where exactly it stops working. — hnk
I created an image of your worksheet descriptions but it has not helped me understand what you want. Search the web for "Excel VBA Tutorial". Any tutorial will show simple worksheet access within the first few pages. — Tony Dallimore

nwhaught nwhaught · Accepted Answer · 2014-07-06T23:45:53

If I understand your question properly, you probably don't need a VBA solution. I would just go to columns I:K and put in a formula to pull in either the value from columns B:D or the value from columns F:H, like so: (example for cell I2)

=IF(B2="",F2,B2)

Drag that formula three columns across and down as many rows as you need, and now cells I:K have all the values you need. You can then Copy>>Paste Special - Paste Values to get everything into columns B:D.

VBA copy cells that meet criteria to another sheet

1 Answers