Supress col/row increment with copy

Question with (hopefully) an easy answer....

When you copy a cell that uses a formula referring to another cell,
for simple illustration say "=C4"  it will increment based on where
you paste it.  So, it will increment up to "=C5" if you paste it in
the next cell, or "=D4" if you paste below.

How can I suppress that, so that when I paste the new cell receives
"=C4" as well.

(I know I can simply copy the text and paste that in, but I want to
copy a whole row of formulas to be the same.  I thought
Paste>Special>Formulas would work, but it seems to be the same as
regular paste)

Thnx in advance

Ross.
0
11/24/2003 3:28:48 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
323 Views

Similar Articles

[PageSpeed] 41

Use absolute references

=$C$4

you can select the cell in question, press F2 and then press F4
and it will change. Or you can run a macro like this

Sub RepCellAbs()

Dim formula As String
Dim cell As Object

For Each cell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)

    formula = cell.formula
    cell.formula = Application.ConvertFormula(formula, _
    fromreferencestyle:=xlA1, _
    toreferencestyle:=xlA1, toabsolute:=xlAbsolute)

Next

End Sub


-- 

Regards,

Peo Sjoblom


"scrodchunk" <scrodchunk@hotmail.com> wrote in message
news:90f0e62d.0311240728.7b3b97d5@posting.google.com...
> Question with (hopefully) an easy answer....
>
> When you copy a cell that uses a formula referring to another cell,
> for simple illustration say "=C4"  it will increment based on where
> you paste it.  So, it will increment up to "=C5" if you paste it in
> the next cell, or "=D4" if you paste below.
>
> How can I suppress that, so that when I paste the new cell receives
> "=C4" as well.
>
> (I know I can simply copy the text and paste that in, but I want to
> copy a whole row of formulas to be the same.  I thought
> Paste>Special>Formulas would work, but it seems to be the same as
> regular paste)
>
> Thnx in advance
>
> Ross.


0
terre08 (1112)
11/24/2003 3:35:42 PM
Thanks for the replies about absolute references - I should have
mentioned that the column I'm copying is one that needs to be created
with copies as well, so hvaing absolute references there will impede
me from copying and creating the source cells in the first place.

Using F4 to change each cell in the column to absolute before copying
is one approach I guess - but still labour intensive.

Ideally I'd like to set up my references in one cell, copy and paste
to create appropriate formulas in the next (with increments happening)
then copy that whole column and <ALT><SHIFT><LIFT LEFT LEG> Paste to
get that column to make my new column have the same formulas (without
increments).  Sounds like a macro is the only way to go.

Thx.
Ross.




"Jason Morin" <jason.morin@us.exel.com> wrote in message news:<50f601c3b2a1$42641580$a601280a@phx.gbl>...
> You have to turn the cell reference into an absolute 
> reference by adding $'s. The quick way is to click inside 
> the cell reference (or if a range, select the range) while 
> in the formula bar and press F4.
> 
> =$C$4 (absolute)
> =$C4 (mixed - column is frozen)
> =C$4 (mixed - row is frozen)
> =C4 (relative)
> 
> 
> HTH
> Jason
> Atlanta, GA
> 
> >-----Original Message-----
> >Question with (hopefully) an easy answer....
> >
> >When you copy a cell that uses a formula referring to 
>  another cell,
> >for simple illustration say "=C4"  it will increment 
>  based on where
> >you paste it.  So, it will increment up to "=C5" if you 
>  paste it in
> >the next cell, or "=D4" if you paste below.
> >
> >How can I suppress that, so that when I paste the new 
>  cell receives
> >"=C4" as well.
> >
> >(I know I can simply copy the text and paste that in, but 
>  I want to
> >copy a whole row of formulas to be the same.  I thought
> >Paste>Special>Formulas would work, but it seems to be the 
>  same as
> >regular paste)
> >
> >Thnx in advance
> >
> >Ross.
> >.
> >
0
11/24/2003 7:47:06 PM
Ross

Create your first column using relative addressing so's you can increment.

Then select that column and run this macro to change to absolute.

Sub Absolute()
    Dim cell As Range
    For Each cell In Selection
    If cell.HasFormula Then
       cell.Formula = Application.ConvertFormula _
       (cell.Formula, xlA1, xlA1, xlAbsolute)
    End If
    Next
End Sub

Now you can copy this column to another.

OR just use the following to copy the formulas as is to the other column.

Cannot remember who first posted this code.  Maybe Tom Ogilvy or John
Walkenbach but.......???

Sub CopyFormulasExact()
    Dim rngCopyFrom As Range
    Dim rngCopyTo As Range
    Dim intColCount As Integer
    Dim intRowCount As Integer

    '   Check that a range is selected
    If Not TypeName(Selection) = "Range" Then End
    '   check that the range has only one area
    If Not Selection.Areas.Count = 1 Then
        MsgBox "Multiple Selections Not Allowed", vbExclamation
        End
    End If

    '   Assign selection to object variable
    Set rngCopyFrom = Selection
    If Not Selection.HasFormula Then
        MsgBox "Cells do not contain formulas"
        End
    End If

    '   This is required in case cancel is clicked.
    '   Type 8 input box returns a range object if OK is
    '   clicked or False if cancel is clicked.  I do not
    '   know of a way to test for both cases without
    '   using error trapping
    On Error GoTo UserCancelled

    '   Assign object variable to user-selected cell
    Set rngCopyTo = Application.InputBox( _
            Prompt:="Select the UPPER LEFT CELL of the " _
            & "range to which you wish to paste", _
            Title:="Copy Range Formulae", Type:=8).Cells(1, 1)

    On Error GoTo 0

    '   Loop through source range assigning any formulae found
    '   to the equivalent cell of the destination range.
    For intColCount = 1 To rngCopyFrom.Columns.Count
        For intRowCount = 1 To rngCopyFrom.Rows.Count
            If rngCopyFrom.Cells(intRowCount, _
                    intColCount).HasFormula Then
                rngCopyTo.Offset(intRowCount - 1, _
                        intColCount - 1).Formula = _
                        rngCopyFrom.Cells(intRowCount, _
                        intColCount).Formula
            End If
        Next intRowCount
    Next intColCount

UserCancelled:
End Sub

Gord Dibben   XL2002


On 24 Nov 2003 11:47:06 -0800, scrodchunk@hotmail.com (scrodchunk) wrote:

>Thanks for the replies about absolute references - I should have
>mentioned that the column I'm copying is one that needs to be created
>with copies as well, so hvaing absolute references there will impede
>me from copying and creating the source cells in the first place.
>
>Using F4 to change each cell in the column to absolute before copying
>is one approach I guess - but still labour intensive.
>
>Ideally I'd like to set up my references in one cell, copy and paste
>to create appropriate formulas in the next (with increments happening)
>then copy that whole column and <ALT><SHIFT><LIFT LEFT LEG> Paste to
>get that column to make my new column have the same formulas (without
>increments).  Sounds like a macro is the only way to go.
>
>Thx.
>Ross.
>
>
>
>
>"Jason Morin" <jason.morin@us.exel.com> wrote in message news:<50f601c3b2a1$42641580$a601280a@phx.gbl>...
>> You have to turn the cell reference into an absolute 
>> reference by adding $'s. The quick way is to click inside 
>> the cell reference (or if a range, select the range) while 
>> in the formula bar and press F4.
>> 
>> =$C$4 (absolute)
>> =$C4 (mixed - column is frozen)
>> =C$4 (mixed - row is frozen)
>> =C4 (relative)
>> 
>> 
>> HTH
>> Jason
>> Atlanta, GA
>> 
>> >-----Original Message-----
>> >Question with (hopefully) an easy answer....
>> >
>> >When you copy a cell that uses a formula referring to 
>>  another cell,
>> >for simple illustration say "=C4"  it will increment 
>>  based on where
>> >you paste it.  So, it will increment up to "=C5" if you 
>>  paste it in
>> >the next cell, or "=D4" if you paste below.
>> >
>> >How can I suppress that, so that when I paste the new 
>>  cell receives
>> >"=C4" as well.
>> >
>> >(I know I can simply copy the text and paste that in, but 
>>  I want to
>> >copy a whole row of formulas to be the same.  I thought
>> >Paste>Special>Formulas would work, but it seems to be the 
>>  same as
>> >regular paste)
>> >
>> >Thnx in advance
>> >
>> >Ross.
>> >.
>> >

0
Gord
11/24/2003 9:47:35 PM
Reply:

Similar Artilces:

Copy/Paste VBA
Ich will eine ganze Visio Seite kopieren mit allen Verbindungen zwischen den Shape und deren Verklebungen (glue Ich habs versucht mit select all, group , copy paste und ungroup auch mit der Option bei copy/paste das es an der orignalposition positioniert werden sol abe 1) die Positionen stimmten nicht überei 2) die Verbindungen waren eingetragen aber nicht verglue Was nun einer eine Version die funktioniert und einfach is thx, mfg Daniel H. You might try Copy / Paste without doing the Group / Ungroup. Are you working with a specific set of shapes? Some Visio shapes have protections t...

Copying sorksheet formatting
How do I copy worksheet formatting, inc. custom headers, footers, column & row formatting across all the worksheets in a workbook? Hi one way: formating them all at the same time: - group the sheets (hold down the SHIFT key while selecting the sheets) - apply the format to a cell, column, row -- Regards Frank Kabel Frankfurt, Germany driverdriver wrote: > How do I copy worksheet formatting, inc. custom headers, > footers, column & row formatting across all the worksheets > in a workbook? You can also select the entire sheet with CTRL+A and do Edit / Copy, then group yo...

Copy and Paste question
I have a macro that copies and pastes a large amount of data. After it has run I get the question: " There is a large amount of information on the clipboard. Do you want to [keep it]?" Could someone please tell me how I can avoid this question being asked as I never wish to keep the data? Many thanks Insert this line after the pasting is done, it will clear the clipboard: Application.CutCopyMode = False hth knut egil "Richard" <rgarwell@jaguar.invalid> skrev i melding news:bpi1ee$cf71@eccws12.dearborn.ford.com... > I have a macro that copies and pastes a l...

Adding Space Between Rows?
I've imported a vocabulary list from Word into Excel, with one entry per cell in column A, and I want to put a little space between the rows for readability. In this case, I can't insert blank rows between the entry rows because of the contents of another column. Is there a way to make Excel increase the cell padding? Thanks to anyone who can help solve this quandary. P.S. I'm using Excel 2000 in Windows XP. "Joshua" <givetheword@yahoo.com> wrote in message news:10n3pmpdk2p5ne8@corp.supernews.com... > I've imported a vocabulary list from Word into ...

how to deal with duplicate data rows
I need to interface with a program that generates a .csv file with row of data. It generates 2 or more rows for each instance uniquely identified by the docket/page combination Example: last first bank trustee docket page smith john ny bank (blank field) 12235 8907 (blank field) (blank field) cal western 12235 8907 smith janet ny bank (blank field) 12235 8907 I need to consolidate all three lines into one row by concating field that contain different data (first name column) and columns with blan spaces to look like the ...

How can I color every other row
Help please. I want to make it easier to use a large spreadsheet where two rows are used for each record. Filling in the background color of every second row prevents mistakes when entering data. I want to color only the used range, not the entire row. I recorded a macro and got the following: TheRange.Activate With Selection.Interior .ColorIndex = 36 .Pattern = xlSolid End With I really appreciate the help. On way: Dim iCtr As Long With ActiveSheet.UsedRange For iCtr = .Row To .Rows(.Rows.Count)....

Increment value in a Formula
Hi, I'm using Excel version 2003. I have the following formula =IF(SUM('Section 3'!$H$1675:$J$1686)=0,"",SUM('Section 3'!$H$1675:$J$1686)) and would like to increment the cell references by 12. I was wondering if there is a way to do this using a bit of VBA code? So the result i'm looking for after code run is =IF(SUM('Section 3'!$H$1687:$J$1698)=0,"",SUM('Section 3'!$H$1687:$J$1698)). I will be running the formula along the columns so will need to increment the formula by 12 for each new column. Thanks Dave...

Returning row and column headers
I have a spreadsheet that contains soccer player positions for our team. I have the positions layed out in a range that has the field positions as column headers and the period played as the row headers. The names of all the players are filled in for each of the four periods and for each position. I would like to be able to extract from this range, each boys position and period by using a formula. i.e. Jake is playing center forward in the second period. I would like to have a seperate range that has the players name and all 4 periods listed and be able to ectract from the data range, the posi...

Problem copying cell contents
I have a worksheet called Input which has text in cells A3, A30, A57, A84 etc which I want to copy to cells B4, B5, B6 etc of a worksheet called Creditors. I can type =input!A3 into cell B4 of the Creditors worksheet and get the correct answer but if I try to copy this to B5 I get =input!A4. Please can anyone tell me how to copy this but increment the cell reference by 27. Hope this makes sense! Thanks for your help. Hi Try =INDIRECT("Sheet1!A"&(ROW()-4)*27+3) Regards Roger Govier QJ wrote: > I have a worksheet called Input which has text in cells A3, A30, A57, A84...

Re: Unable to copy outlook.pst filen
"John V. Petersen" <lady@di.com> wrote in message news:... > Message-ID: <80649fa887ef4a9abd44de6b30ac7abd@newspe.com> > X-Mailer: http://www.umailcampaign.com, ip log:80.160.104.147 > Newsgroups: microsoft.public.outlook > NNTP-Posting-Host: 22.bb.5446.static.theplanet.com 70.84.187.34 > Lines: 1 > Path: > news.mixmin.net!proxad.net!feeder1-2.proxad.net!newshub.sdsu.edu!msrtrans!TK2MSFTFEEDS02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl!newspe.com > Xref: news.mixmin.net microsoft.public.outlook:20550 > > I have tried that again now. ...

Is there a way to copy and paste the date into worksheets
Is there a way to copy and paste dates into the worksheet withou individually typing up each one -- mark_vi ----------------------------------------------------------------------- mark_vi_'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2450 View this thread: http://www.excelforum.com/showthread.php?threadid=38137 Is there a way to insert it the date into the the worksheet tab (not th cells in the actual worksheet but the name of the worksheet itself -- mark_vi ----------------------------------------------------------------------- mark_vi_'s Profile: ht...

Adding a total row to a chart data table
I have the following data in a chart Week 1 2 3 4 5 6 7 8 Data1 1 5 7 6 1 6 7 6 Data2 3 4 5 7 9 4 6 6 Is it possible to have the data table show a totals row? I.E: Totals 4 9 12 13 10 10 13 12 Thanks in advance, John You can't include the total in the data table. As a workaround, you could embed the chart on a worksheet, and display the data below it. Tushar Mehta has instructions on his web site: http://tushar-mehta.com/excel/newsgroups/data_table/index.htm John Ortt wrote: > I have the following data in a chart > > Week 1 2 3 4 5 6 7 8 > Data1 1 5 7 6 1 6 7 6...

find a copy of a standard formal report lay out/template
looking for an example of a formal report template Hello pppe productivity placement program example wrote: > looking for an example of a formal report template You can find templates offered by Microsoft at the following URL: http://office.microsoft.com/en-us/templates/default.aspx namely: http://office.microsoft.com/en-us/templates/CT101043361033.aspx HTH Robert -- /"\ ASCII Ribbon Campaign | MSFT | \ / | MVP | Scientific Reports X Against HTML | for | with Word? / \ in e-mail & news | Word | ...

copy protection
I want to write software copy protection code. I don't need it to be very highly secure, but I do want it to prevent casual copying and do some things to make hacking not so easy. So a few questions. 1. I want to make the registration code be different on each PC. I think processors now have a unique ID and the MAC address of the network card could be used. How do I get access to these IDs programmatically? Are there other things I could use? 2. When you download a demo version of some s/w and it times out, you usually cannot uninstall and reinstall, because even after uninstalling, some...

Copy Cells
Hi all I have a spreadsheet which has lots of calls logged on it. Each call is logged by a person. I have a tab for each person. What i need to do is each persons tab needs to pick up the information for that person (pick it out of the main sheet which has everyones call on it). So any cell in column 'A' with the name 'Joes Bloggs' needs to copy accross to tab 'Joe Bloggs' along with the rest of the information on that line. Hope this makes sense, if you dont understand reply with a question. Thanks in advance Darren --- Message posted from http://www.ExcelForum...

How to copy mails stored in Drafts Folder?
Hi; I'll be leaving my employer soon and would like to "save" (on stick, on local hard disk) some mails which are in my Drafts folder. Is this possible? Grateful for any hints. Regards, Gordon Filby ...

Adding the values on even rows/odd rows.
please refer to this post to prevent dupilcates http://www.mrexcel.com/forum/showthread.php?t=322724 Huh? Dave. ...

Copying data but keeping original
Hi There! I have two spreadsheets with several rows of data on each of them, they both have header rows, with the same heading names. I want to copy the data from spreadsheet 1 ( without the header row) to the spreadsheet 2 but keeping spreadsheet 2 original data ( i.e NOT REPLACE ) i just want the data on spreadsheet 1 to be pasted on after the last line of data on spreadsheet 2; as i need to keep the existing data in spreadsheet 2. Any ideas on how I can do this with a macro or VB code? Help appreciated thanks! See Ron de Bruin's site for various copying macros. http://www.rondebrui...

How do I copy a graph and use the data in the new worksheet?
I have new data in different worksheets all with the same data format. I want to make the same graph in each worksheet using the data in that worksheet but I was hoping not to have to recreate the graph and select all the data in each worksheet. Is there a way to do this as long as the data is in the same place in each worksheet? Copy the sheet with the chart. The chart points to the new sheet's data. Copy a new set of data, and paste it over the data that the chart is pointing to, and the chart will update. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Service...

Copying a Drop Down Menu
I am making a data-entry spreadsheet. I have a drop down menu for each column, and I want to copy that drop down menu and paste it in each cell for that column. When I have tried to follow instructions on excel (copy, paste special, check validation), the top item in the menu is erased. It looks like the range is changing in each cell (i.e. =F1:F6 in F16 goes to =F2:F7 in F17, then =F3:F8 in F18). How can I copy my drop down menu and keep it the same for each cell? Thanks for your help! How about an alternative? Instead of using a dropdown from the Forms toolbar or a combob...

Include Row number to print
Hi Expert, How could I include the row number in my printing? Thanks for your help. One thing more, Can I copy the row number only to another sheet? "Rechie" wrote: > Hi Expert, > > How could I include the row number in my printing? > > Thanks for your help. The ROW() function will return the row number. =ROW() will always return the current row number =ROW(A5) will return the row number of the referenced cell (5 in this case) on another sheet you could use something like: =ROW('First Sheet Name'!A5) which would return 5 also. &qu...

Copy Values From Wbk1, Outlook Them To Another PC, Paste Them Into Wbk2
WBK1 (shared) has a routine in it which opens WBK2, copies the value of WBK1-E3, pastes it into Next empty row col A-WBK2, goes back to WBK1, copies the value of WBK1-B2, pastes it into Next empty row col B- WBK2, etc, for 11 values =96 so that WBK2 ends up with a new row of 11 cell-values from WBK1. The routine then sorts the rows in WBK2 by col A (which are numbers =96 from E3), saves and closes it, goes back to WBK1 and clears the 11 cells of their values =96 ready for the next entry. I want the copying and pasting part of this to work for more users - users who don=92t have acce...

Can i print copy numbers?
At my job we must print copies of databases and keep track of them. We only print 100 copies at a time. Is there a way to make it automaticly put "1 of 100" or "copy 1 of 100" in the footer?? You could run a little macro: Option Explicit Sub testme() Dim iCtr As Long For iCtr = 1 To 3 '100 when you're ready With ActiveSheet .PageSetup.RightFooter = "Copy " & iCtr & " of 100" .PrintOut preview:=True 'false when you're ready End With Next iCtr End Sub I used 1 to 3 and pri...

Flipping column and row headings
Hello, I need to flip the column and row headings. Is this possible? Thanks, Steve Sorry...I found my answer. Steve seve wrote: > Hello, > > I need to flip the column and row headings. > > Is this possible? > > Thanks, > > Steve ...

Outlook 2003 copying messages to folders question
I am using Outlook 2003 and have a question about filing either “incoming” or “sent” mail in any of the folders that I have created. The filing choices in mail are either to “move” or “copy”. If I choose to “copy”, Outlook will create a copy of the email and place it in the folder I have selected. If, for some reason, I forget that I’ve already filed the email in that same folder and do it again, Outlook doesn’t inform me that a copy of that email already exists in the file. As such, it is very possible to have multiple copies of the same email filed in a folder. I would lik...