Solved Excel 2010 - Copy Formula

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Gevans0666

Thread Starter
Joined
Sep 6, 2018
Messages
48
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?
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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?
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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
 

Gevans0666

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

...and thanks :)
 

Gevans0666

Thread Starter
Joined
Sep 6, 2018
Messages
48
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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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 :)
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
Forgot, I already had you file :)
Will look at that and see if I can make it work for you
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top