VBA to paste data into specified number of columns

[jonny1984]

Hi

I currently have VBA code to copy a formula (from cell H5) and then paste it into a big table which then runs calculations.

The formula in H5 is quite long (has several "VLOOKUPS" etc so when its pasted and calculates it can take a long time

To speed up the process I wondered if its possible to adapt my VBA script to it only pastes into rows / columns that are required - I could easily put the maximum number of rows (currently 43) and maximum number of columns (currently 25) into a cell .. for example I could add in cell A1 = 43, and cell A2 = 25 (and I am fine writing a formula which will automaticlaly update those cells as necessary)

How should I update the VBA script below so it only pastes the formula from "H5" into a range that is 43 rows down (ref cell A1) and 25 (ref cell A2) columns across - ie where the 43 and 25 will be dependent on the formula result in Cells A1 and A2 ?

THnaks in advance !!!!

Sheets("Sheet1").Range("H5").Copy
Sheets("Sheet1").Range("H5:EZ9999").PasteSpecial Paste:=xlPasteFormulas

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try this

With Sheets("Sheet1")
  .Range("H5").Copy
  .Range("H5").Resize(.Range("A1").Value, .Range("A2").Value).PasteSpecial Paste:=xlPasteFormulas
End With

 

[jonny1984]

Amazing - this worked ! Perfectly

Thank you so much, you are a genius!!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

.. and for updating your profile.