Copy, Paste Special values IF....VBA

[TDL76]

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

 

[steve the fish]

You could do this with a formula. Try this in G5 and copy down/ across.

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

 

[TDL76]

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?

 

[davesweep]

If you want it in VBA,

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

 

[TDL76]

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

 

[TDL76]

Also, Dave.. will this auto update?

 

[steve the fish]

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

 

[TDL76]

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

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

 

[steve the fish]

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