Looking for an efficient way to paste 100's of VBA formulas into a spread sheet.

Hi All,

I have a workbook, with many sheets and many many tables.

Because I have to insert formulas into the top rows of the tables, absolute and relative referencing goes out the door.

So I have ended up programing hundreds of cells via VBA to look something like this.

Sub Test()
Application.Calculation = xlManual
Sheets("Sheet1").Select
Range("A4").Select
With Selection
    .Formula = "= A1 * A2"
    .HorizontalAlignment = xlLeft
    .etc
    .etc
    .etc
End With

Range("B4").select
With selection
   .Formula
   .HorizontalAlignment
   .etc
   .etc
   .etc
End With
Application.Calculation = xlAutomatic
End Sub

With some great help from Ali, I have now also introduced PQ into refreshing dependent tables. However, the refreshing has to happen very frequently. Which all leads to the system ssssslllllloooooooooooowwwwwwing down.

Any ideas on how to program better, or just be able to speed this up would be most appreciated. Or do I just have to bide my time and wait for the circular curser to stop spinning ?

Thanks for your help in advance.