1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved Excel 2010 - Copy Formula

Discussion in 'Business Applications' started by Gevans0666, Sep 6, 2018.

Thread Status:
Not open for further replies.
Advertisement
  1. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    40
    Using Excel 2010

    I have a rather large spread and don't want to copy formula down thousands of rows on a weekly basis.

    I've attached a sample spread... 1 workbook and 2 worksheets (sheet 1 and 2)

    I wish to copy data from sheet 2 (raw data dump) to sheet 1 (which contains formulas, formats, etc.)

    How to I copy down formula without dragging it and have it stop when it reaches a blank row?
     

    Attached Files:

  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,582
    First Name:
    Hans
    Hi welcome to the forum.
    Ho'w your VBA knowledge. this has to be done using a macro (at least thtás the only way I could get it done)
    Are macros are problem for you?
     
  3. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    40
    I a bit familiar -- I could record one? Is that what you mean?
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,582
    First Name:
    Hans
    Yes, that’s what I meant
    The steps to follow while recording is to type the formula in the first row and then double click the right bottom corner of that cell so the formula is copied all the way down to the last row in that column
    After that you can edit the recorded macro so that you determine what’s the last filled row so that it works on all circumstances
    It’s easier to do than explain but this is the idea
     
  5. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    40
    Okay -- may need some help "editing" the macro... stay tuned!

    ...and thanks :)
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,582
    First Name:
    Hans
    No problem
     
  7. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    40
    Help! Macro requires Edit?


    Sheet 1 (Data1) is a raw data file with thousands of records (rows)


    Sheet 2 (IOH) is formatted as a table (had sorting issues – that fixed it) – and is to contain all the data from sheet 1 with additional columns inserted that contain formulas (this changes weekly – old data is replaced by new data file)


    I want to be able to record a macro that copies all the data rows from sheet 1 to sheet 2 into the corresponding columns… and continue down to the last row of data from sheet 1


    I can record a simple macro but fail miserably when they require editing!


    I assumed I would need to first do a macro to clear the old data from the table – so I did.


    1st Macro recorded (clears all old data from IOH with the exception of the first row – as there are formulas in several columns)

    ******************************************

    Sub Macro1()

    '

    ' Macro1 Macro

    ' Prep IOH Sheet

    '

    ' Keyboard Shortcut: Ctrl+Shift+C

    '

    Sheets("IOH").Select

    ActiveWorkbook.Worksheets("IOH").ListObjects("Table2").Sort.SortFields.Clear

    Application.Goto Reference:="R3C1"

    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

    Selection.EntireRow.Delete

    Application.Goto Reference:="R2C1"

    Range("A2:D2,G2").Select

    Range("Table2[Invoice Date]").Activate

    ActiveWindow.SmallScroll ToRight:=9

    Range("A2:D2,G2,N2:p2,R2,S2").Select

    Range("Table2[Terms]").Activate

    Selection.ClearContents

    Application.Goto Reference:="R2C1"

    End Sub


    ***********************************************

    2nd Macro recorded – this is supposed to copy the raw data from Data1 to IOH but it only does it for the 1st row.

    Sub Macro4()

    '

    ' Macro4 Macro

    '

    ' Keyboard Shortcut: Ctrl+v

    '

    Sheets("IOH").Select

    Application.Goto Reference:="R2C1"

    ActiveCell.FormulaR1C1 = "=Data1!RC[2]"

    Range("Table2[Requester]").Select

    ActiveCell.FormulaR1C1 = "=Data1!RC[2]"

    Range("Table2[PO Number]").Select

    ActiveCell.FormulaR1C1 = "=Data1!RC[2]"

    Range("Table2[Trading Partner]").Select

    ActiveCell.FormulaR1C1 = "=Data1!RC[2]"

    Range("Table2[Invoice Date]").Select

    ActiveCell.FormulaR1C1 = "=Data1!RC[2]"

    Range("G3").Select

    ActiveWindow.ScrollColumn = 2

    ActiveWindow.ScrollColumn = 3

    ActiveWindow.ScrollColumn = 4

    ActiveWindow.ScrollColumn = 5

    ActiveWindow.ScrollColumn = 6

    Range("Table2[Invoice Num]").Select

    ActiveCell.FormulaR1C1 = "=Data1!RC[-4]"

    Range("Table2[Invoice Curr]").Select

    ActiveCell.FormulaR1C1 = "=Data1!RC[-4]"

    Range("Table2[Invoice Amount]").Select

    ActiveCell.FormulaR1C1 = "=Data1!RC[-4]"

    Range("Table2[Description]").Select

    ActiveCell.FormulaR1C1 = "=Data1!RC[5]"

    Range("R3").Select

    ActiveWindow.ScrollColumn = 7

    ActiveWindow.ScrollColumn = 8

    ActiveWindow.ScrollColumn = 9

    Range("Table2[Terms]").Select

    ActiveCell.FormulaR1C1 = "=Data1!RC[17]"

    Range("S3").Select

    ActiveWindow.ScrollColumn = 10

    End Sub
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,582
    First Name:
    Hans
    You should use the code tags around inserting code

    [ code ] << without the blanks to start
    the code
    [ / code ] << without the blanks at the end

    The result will be

    Code:
    the code here
    
    I'll look at what you've recorded and see if I can make heads or tails form it :)
     
  9. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    40
    Thank you!
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,582
    First Name:
    Hans
    The problem with a recorded macro is that it scrolls down and all that, so without (in this case) the table coordinates and all that it makes it diffcult to interpret.
    When selecting an entire range you can use the SpecialCells() and in the available options there is one that say xlTypeConstans or something similir, look it up.
    You can then say(for example) Range("A2:Q25").SpecialCells(xlTypeConstants).clearContentents
    In this case the cells with formulas are automatically excluded.
     
  11. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,582
    First Name:
    Hans
    Forgot, I already had you file :)
    Will look at that and see if I can make it work for you
     
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,582
    First Name:
    Hans
    Okay, the Sample table has no relation to the recorded macro since there is no table named Table2 and the worksheet names are non-existent.
     
  13. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,582
    First Name:
    Hans
    Cannot work with the Sample file and the recorded macro that was recorded elsewhere.
    Suggest the same file with dummy data or well, can't help
     
  14. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    40
    Here is the dummy file with correct references and recorded macros.
     

    Attached Files:

  15. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,582
    First Name:
    Hans
    One thing I do not understand.
    Table2 is full of formulas, at least, I get the impression even if all give a #REF error due to erroneous copying and pasting.
    My assumption (how I hate assumptions) is that the formulas point to the Data worksheet.
    So why, if all the cells in Table2 are formulas are you trying to clear them? What's the point of this all?
    Your Macro1 does that (as far as I can figure it out)

    Macro4 then goes on places formulas in 9 columns which take data from the Data1 worksheet
    Data1 contains 2365 rows of data and is this what varies?
    All you would need to really do is make your formulas check if there is data and then calculate,
    if is cell x is blank the value is blank else the value is the formula.
    If you know more or less how much data is dumped into Data1 then you can that number of rows + a % extra as standard and make sure it works.

    I'll see if I can find out what the #REF point to and see if I can upload the basic idea
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1216171

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice