+ Reply to Thread
Results 1 to 6 of 6

Copy Formulas from 'Formulas' Sheet into Data Table Columns

  1. #1
    Registered User
    Join Date
    04-03-2014
    Location
    Rotherham
    MS-Off Ver
    Excel 365
    Posts
    24

    Post 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


  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Copy Formulas from 'Formulas' Sheet into Data Table Columns

    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.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    04-03-2014
    Location
    Rotherham
    MS-Off Ver
    Excel 365
    Posts
    24
    Quote Originally Posted by AlphaFrog View Post
    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

  4. #4
    Registered User
    Join Date
    04-03-2014
    Location
    Rotherham
    MS-Off Ver
    Excel 365
    Posts
    24

    Re: Copy Formulas from 'Formulas' Sheet into Data Table Columns

    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
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-03-2014
    Location
    Rotherham
    MS-Off Ver
    Excel 365
    Posts
    24

    Re: Copy Formulas from 'Formulas' Sheet into Data Table Columns

    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!

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Copy Formulas from 'Formulas' Sheet into Data Table Columns

    You do have to qualify a sheet (the ActiveSheet or a specific sheet) in front of ListObjects

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copy data from one sheet to another without formulas automaticly
    By bee88 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 12-10-2020, 04:19 PM
  2. Replies: 1
    Last Post: 03-26-2015, 10:33 AM
  3. macro to add columns after existing columns and copy/paste headers and formulas
    By rhybeka in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-21-2013, 02:35 PM
  4. [SOLVED] Adding Record to data table + preserve array formulas in specific columns
    By olivierpbeland in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-21-2013, 02:33 PM
  5. Data in 2 columns... copy formulas to rows
    By Xx7 in forum Excel General
    Replies: 3
    Last Post: 11-03-2011, 08:40 PM
  6. Replies: 3
    Last Post: 05-25-2011, 01:30 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1