# Copy, Paste Special values IF....VBA

#### TDL76

##### New Member
Hello,

I'm hoping someone can help...

What would the VBA code be for Coping a range of cells "\$B\$5:\$B\$9" to "G5:G9" if cell \$B\$4 is the same as G4... and then as the data table grows H4, I4, J4, K4, L4... etc

I'd like to use VBA as this will minimise a manual task...

Many thanks

Tracey

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
You could do this with a formula. Try this in G5 and copy down/ across.

=IF(G\$4=B\$4,B5,"")

Steve,

Simplicity works for me!!! Here I was trying to be super smart!!! Thank you...

However... How do I paste special values as this will be "Static" data for trending?

Last edited:
If you want it in VBA,

Code:
``````Sub a()
Dim iLastColumn As Integer
Dim iLoop As Integer

iLastColumn = Cells(4, Columns.Count).End(xlToLeft).Column

For iLoop = 7 To iLastColumn

If Range("B4").Value = Cells(4, iLoop).Value Then

Range(Cells(5, iLoop), Cells(9, iLoop)).Value = Range("B5:B9").Value

End If

Next iLoop

End Sub``````

Hi Dave,

Thank you for that.... It works for the first table but when I use this code for the subsequent tables below (changing the cell references to the appropriate range) I get a "Compile Error: Ambiguous name detected: a"

do I need to change "a" to "b" "c" etc for each of the subsequent tables?

Thank you

Also, Dave.. will this auto update?

Yes that's just name of your macro. You cant have two by the same name.

Ok so I've changed the "Name" ... THANK YOU

How do I get it to run? Either by a "Button" or by refresh?

"View" tab... then macros on the right. Select it. Then run.

Replies
9
Views
1K
Replies
12
Views
1K
Replies
2
Views
440
Replies
3
Views
330
Replies
2
Views
747

1,203,600
Messages
6,056,200
Members
444,850
Latest member
dancasta7

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back