Copy a row of data and only paste the formulas into the next row

[BetterBeef]

Is there an easy way to copy data from a row and when you paste it, to only have the formulas paste, and not the raw data? For example, cell A1 had the number 5 in it, and cell A2 had the formula =A1+7, I would like to copy the the row into row 2, but when I paste it, the data in B1 would be blank, and B2 would read =B1+7? Is it possible to write code in VBA to do this, or is there a shortcut out there that I am unaware of?

 

[SkipVought]

Hi,

Excel 'has a feature for that!' _{to paraphrase...}

Select anywhere in your existing table. I assuem that it is a TABLE (one row of headings; contiguous rows & columns)

Data > List > Create List...

Use the TAB key ALWAYS in this table -- ALWAYS, even at the end of the row of data.

As you TAB to the NEW row, your formulas will automatically propogate to the new row.

VOLA y'all!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Copy / paste special > formulas

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[BetterBeef]

XLBO's solution doesn't work, because when you paste special formulas only, even those cells that have raw data (no formulas) get pasted over. I basically want the equivalent of a paste special, without any raw data getting transferred over.

Skip's solution really didn't work for me either. I have a set amount of rows (right now up to row 8). As each week expires, I add a new row where I enter data and formulas within that row calculate more data. I basically just want to paste the formulas without having to erase the raw data that I previously entered. My guess is there needs to be some VBA code written for this, but I'm not sure.

 

[SkipVought]

BB,

You have previously entered 'raw data' in rows that have no formulas.

If you did THAT INITIAL PROCESS, using the LIST FEATURE, then a formula would automatically be on every row.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BetterBeef]

I should have explain further... In each row there are some columns that have formulas, and some columns which raw data is to be entered. I want to copy row 8 and paste it into row 9 with all raw data having been erased.

 

[SkipVought]

BTW, IMHO, a well designed Excel application will use COPY, INSERT & DELETE sparingly. There are other ways, as in the LIST FEATURE.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BetterBeef]

I'm sorry, I am unaware of what IMHO means.

 

[SkipVought]

Do you ever use google to answer your questions?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BetterBeef]

Sure, many times. Why?

 

[SkipVought]

Because, that is where the answer can be found to your latest question.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BetterBeef]

Sorry, I did not know the expectation was for me to look it up on Google.

 

[SkipVought]

Oh well.

In
My
Humble
Opinion

I'm a 67 year old guy, who's had to look up lots of stuff like this, because, I'm not really part of the current scene. Just like to know what's going on.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I guess the way I should have responded is, "Let me encourage you to search the internet and other authorities, for clarification of language and terms not directly related to MS Office." Sorry.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BetterBeef]

Skip,

I'm just a 28 year old guy who enjoys helping people whenever asked and I'm assuming that you do as well otherwise you would not be on here. I never have a problem looking things up, but when it's what I may consider a "quick hitter", I just ask the question because I figure the other person is as happy to answer it as I would be.

With that said, I can understand where you are coming from though. I in no means intend to be a nuisance. If I thought IMHO was a short hand text acronym, I probably would not have asked you. Unfortunately, I thought it was a technical term for Excel.

I apologize for my confusion, and I look forward to any future help you can give me in Excel.

Thanks,
Chris

 

[SkipVought]

No problem. Let's continue to learn & share!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BetterBeef]

Alright, getting back to work. The list function is definitely a very efficient way to do this, however, I have blank columns to keep groups of data separate. I tried creating multiple lists, however, I have headings and subheadings involved. For example, Row 1 will have a main heading such as "Pounds Purchased". This header is centered across merged cells. Row 2 has the plants involved. These are also merged cells because Row 3 has the data headers. Unfortunately the data headers are the same for each plant. What ends up happening is that the list gives drop down boxes and puts numbers at the end of the row 3 headers. I'm not really interested in sorting data either, I just want an easy way to only copy formulas into the next row. I can send you my spreadsheet if you want to get a better idea of what I mean. I just don't know if the list function is going to get me what I'm looking for.

Any thoughts?

 

[SkipVought]

Merged cells, empty columns, plant data in multiple columns...

This is a recipe for disaster. I'm not saying that it cannot be done, but you're making it extremely difficult to employ the plethora of data analysis and reporting features (like the list feature). You're shooting yourself in the foot by STOREING data like you want to SEE & REPORT the data.

I'll take a look, if you want...

ii36250

a~t

bellhelicopter

d~o~t

textron

d~o~t

c~o~m


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BetterBeef]

Here's my solution:

Sub copydata()
Dim lrow As Long, lrow1 As Long
Dim rng As Range
lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Rows(lrow).Copy Range("A" & lrow + 1)
lrow1 = Cells(Cells.Rows.Count, "A").End(xlUp).Row

Set rng = Rows(lrow + 1)
rng.SpecialCells(xlCellTypeConstants).ClearContents

MsgBox "Done"


End Sub

 

[SkipVought]

That is EXACTLY what will happen the moment you

1. TAB to the next line in the List Table AND

2. enter a data value.

Have you REALLY tried it? NO VBA REQUIRED!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}