VBA to copy 2 columns and paste multiple times into another sheet based on a cell value

[nellyc]

Hi All

I am looking for some VBA to copy columns A & B in "Sheet2" then paste, including formulas to the last available column in sheet "Stock Sheet" multiple times based on a cell value in "Stock Sheet" Cell A4.

any suggestions would be greatly appreciated.

thx,

 

[kvsrinivasamurthy]

Try this code. Change range as required.

Sub Macro1()
Dim T&
Sheets("Sheet2").Range("A1:B15").Copy
Sheets("Stock Sheet").Activate
For T = 1 To Sheets("Stock Sheet").Range("A4")
Range("A1").End(xlToRight).Offset(0, 1).Select
ActiveSheet.Paste
Next
End Sub

 

[nellyc]

Thank you, with a bit of adjustment it works well.

Now i need to work out how to keep the formula's in the correct place.
The Formula is - =IF($B8=$BA6,$J8,0), I can seem to lock the middle cell ref to the new columns no matter where the $ signs are placed, any suggestions?
The $BA6 Cell ref in the formula needs to be static on each cell in the inserted columns - so if i insert 3 sets of columns after column BA it need to move to BC, BD etc

Hope that makes sense

 

[kvsrinivasamurthy]

Which cell contains this formula.

 

[nellyc]

Column A on sheet2

 

[nellyc]

More specifically A8 to A319 on sheet2

 

[kvsrinivasamurthy]

=IF($B8=$BA6,$J8,0)
Change the formula in A8 as
=IF($B8=$BA$6,$J8,0)
Drag down and then run macro.