Copy Formulas from 'Formulas' Sheet into Data Table Columns

Morning all,

Below is what I want to achieve in pseudo code:

1. Copy Formula from Cell
2. Paste Formula into Entirety of Data Table Column
3. Copy & Paste Special Cells in Data Table Columns
4. Repeat from A Different Formulas to A Different Table Column

In principle, this should be very simple, but I'm new to VBA coding and I'm not sure how to handle data column references.

A particular example:

1. Copy =[@Kgs]+1000 from 'Formula' sheet
2. Paste to ProductSpecDB[1000] (table name / column name)
3. Copy&Paste Formulas to ProductSpecDB[1000] (table name / column name)

The reason behind doing this is I have a large workbook which is slow to work with, even with Calculation set to Manual.

Any pointers would be great Thanks, Jonny

Originally Posted by AlphaFrog

Most of your code could be derived by recording a macro and doing the steps manually while recording. At least it could help create base code to further modify.

Here are two good reference sites for syntax and methods used in VBA for ListObject (Tables).
The VBA Guide To ListObject Excel Tables
Working With Tables In Excel 2013, 2010 And 2007 (VBA)

If you want specific help, please describe what specific sheet\cells you want to copy from and what specific sheet\table\column you want to paste to. Maybe attach an example workbook and include any code you may have started even if it's incomplete.

Thanks AlphaFrog! Ill take a look, have a good and post an example workbook when i get home

Hi,

I've attached a simple example of the kind of thing I need - the actual file runs to ten of thousands of lines across multiple worksheets which its running sluggish!

I've made a Formula sheet, and I've written why the formulas should go in the workbook (formulas I've coloured in red).

Firstly, I'd like to get the macro working as I mentioned in the first post. Possibly after that, I would like to try the macro reading the coordinates from the Formula sheet and place the formulas based on the coordinates, rather than strictly coding the macro to put 1 formula into one specific column.

Any points would be very helpful

Jonny

Hi all,

I've had a go at this macro, and although I can get something working with the macro recorder, I don't really know if its the fastest way to write what I need, bearing in mind running the calculations on my sheet quickly is the main reason for the macro.

Simple question - when I state a ListObject, do you have to put "ActiveSheet." in front? Or does VBA recognise ListObjects no matter where they are?

Thanks!