Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Auto Increment Worksheet While Copying

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

How do make an auto increment of worksheet while copying?


if i copy one cell(A1) by dragging the mouse, this will be the result:
(B1 is the worksheet)
Cell -----> Formula
A1 -----> =SUM('B1'!$E$3:$E$14)
A2 -----> =SUM('B1'!$E$3:$E$14)
A3 -----> =SUM('B1'!$E$3:$E$14)
and so on...

the result i want is like this while copying by dragging:

Cell -----> Formula (B1 is the sheet)
A1 -----> =SUM('B1'!$E$3:$E$14)
A2 -----> =SUM('B2'!$E$3:$E$14)
A3 -----> =SUM('B3'!$E$3:$E$14)
and so on...

Need some guidance.. thanks

View Answers     

Similar Excel Tutorials

Increment a Value Every X Number of Rows in Excel
How to increment a value, number, percent, etc. every so many rows in Excel. This tutorial teaches you how to build ...
Increment a Date By X Days for Each Row in Excel
How to quickly increment a date by a certain number of days for each entry. Type the first date in a cell. Make sur ...
How to use Vlookup Across Multiple Worksheets in Excel
This tutorial shows you how to use the Vlookup function across multiple worksheets within Excel.  This means that y ...
How to Resize Rows and Columns in Excel Quickly
Resizing rows and columns in Excel is an easy process. Simply left click in between the columns and drag the mouse ...

Helpful Excel Macros

Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Replace Formulas with Values (For The Entire Workbook)
- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web

Similar Topics

I need to copy one date onto adjacent cells by dragging from the right-down corner, as when copying an ordinary number.
In the case of dates, however, that fills each cell of the series with the date following the previous one.
How can I cancel that serial filling, so that such dragging will just copy the same original date into all those cells?
Thank you for any help you can provide.

How do you make the Autofill function increment in blocks, e.g. instead of increment 1,2,3 increment 1,8,15 inthe following example:

I have a 2 worksheets, one with days of the year across columns and values down rows, and another that summarises the sum of the corresponding days in weeks, so I have 365 columns in sheet 1 and 52 columns in sheet2.

Everytime I update the sum range i cannot autofill it in the summary worksheet, as A1:G1 autofills to B1:H1, but I want it to increment to H1:N1.

Thanks, Simon.


I need to copy a chart to several different pages of an Excel presentation
spreadsheet, and would like for the data source cell references to increment
by 50 each time. I'm copying the chart 50 rows down each time, but the data
source references do not increment. When I change the data source references
from "$A$25" to "A25", they get changed back to the "$A$25" absolute
reference when I click Ok.

I expect that the chart objects aren't tied to specific cells on the
presentation spreadsheet -- so Excel doesn't increment the references when
they get copied.

But ........... is there any easy to increment the data source cell
references when I copy so I don't have to manually change them on each new
copy of the chart?

Thanks in advance -- Dave


I need to copy a chart to several different pages of an Excel presentation
spreadsheet, and would like for the data source cell references to increment
by 50 each time. I'm copying the chart 50 rows down each time, but the data
source references do not increment. When I change the data source references
from "$A$25" to "A25", they get changed back to the "$A$25" absolute
reference when I click Ok.

I expect that the chart objects aren't tied to specific cells on the
presentation spreadsheet -- so Excel doesn't increment the references when
they get copied.

But ........... is there any easy to increment the data source cell
references when I copy so I don't have to manually change them on each new
copy of the chart?

Thanks in advance -- Dave


I'm having problems copying a formula into a certain 'area' of a spreadsheet. The total cell area that I'm copying this formula into is 430 Rows x 179 Cols. Also, this formula pulls information in from other large worksheets. I can copy the formula into the first portion, but there seems to be certain parts that misbehave when I try to continue copying the cells. I'll try to explain 'misbehave' to the best of my ability. If I try dragging and copying, the cell formula gets left blank, but the conditional formating gets copied. If I copy and paste, the cell produces seemingly random results. For example, the first copy and paste produces no formula and a blank cell, the second paste might give me '0.00' or something like that, but still no formula. I can get around the problem in a strange way though. I can't copy things out of the corrupt area into the the corrupt area, but copying cells that start inside of the corrupt area works. So, by manualing copying the formula into a 'corrupted cell', I can copy that cell to the rest of the broken cells.

I'm not getting an error message, so I don't have anything to offer you there. However, I know that something is obviously corrupted or broken. I've tried repairing the file with a few different methods, but it hasn't changed the situation at all. I tried copying everything into a new worksheet, but the issue is still present. I need to eliminate this issue since I may end up passing this spreadsheet off to someone else. If they are unaware that this issue exists, bad information/data may get proliferated. Any help or suggestions with this would be greatly appreciated. Let me know if you need more info about the file.


P.S. Sorry for the long post, but I figured excessive information was better than a lack of.

I use Excel 2010. I just tried copying formulas by using the handle to drag down. It's always worked before when I've used previous versions of Excel. In 2010, dragging down copies the formulas correctly BUT copies the result of the initial formula rather than the result of the correct formula in the current cell. What?? Can anyone help?


Could anyone help me please ? I've tried lots of ways to do this, but none successful so far.

I've got 2 worksheets in the same workbook.

Sheet 1 contains huge amounts of data - thousands of rows and multiple columns

Sheet 2 - I want to extract the data from sheet 1 column A into sheet 2 column A but only the data from every 21st row.

I want to be able to copy the formula automatically down, otherwise it will take hours to do it manually. So far I can only get the cell reference to increment by 1 each time after copying.

So what I'm trying to achieve is :-

Sheet 2 A1 = Sheet 1 A1
Sheet 2 A2 = Sheet 1 A22
Sheet 2 A3 = Sheet 1 A43

If anyone can help me out with this, I'd be most grateful.



I Need To Have 2 - $'s In The Formula So I Can Paste The Col To Another Sheet & Not Have It Get Screwed Up.

"$a" Is No Problem Because Im Staying In The Same Col, But "$1" Needs To Increment But Still Have The $

Not Like This:

Like This:

I Need The Row To Increment, But Still Have A $ So I Can Cut & Paste It

I Dont Want To Do It With Just An $ Before The Col, Drag It So The Rows Increment, & Then Manually Put In The $ Before The Row

I'm having an unusual problem with Vlookup. I enter the formula in a cell and it produces the correct result. However when I copy down the displayed result is exactly the same as in the original cell. When looking at the formulas I note that the lookup cell has been incremented correctly. for example:the original formula is:


dragging this down the next row formula is:


and so on. However the result for each cell is the same as in the top one. I have noticed however that if in the formula I manually delete 'A3' and type in 'A3' then it produces the correct result so obviously something is going wrong when draging down the formula even though it appears to be correct

any idea's?

I have a file that links in several different datasources. What I am doing is updating all my sources, running all my calculations then producing "results" sheets. I have several seperate reports being generated from the same data. I am copying the worksheet into a new workbook then saving, emailing, then going onto the next. Problem is when the users open that file their calculation is set to manual because I am manually calculating everything to speed up processing time. If i enable auto calculation after copying the worksheet it calculates my entire workbook, then saves, and goes onto the next. Each time it auto calculates it takes 15 minutes. With manual calc its 2 minutes. How can i make it so that my main file always has auto calc off but make it so that when users open the seperate file auto calc is enabled?

Hi everyone,

I'm going nuts trying to figure out how to autofill text from one cell
to another. For example, everytime I type text into cell A20, I want
the exact same thing to cell BL20. The same for B20, BL20, etc.

How do I go about that? I already know how to copy formulas from one
cell to another by dragging the skinny black line of the cell. I guess
I just need the formula for a simple IDENTICAL copying of text.

Thanks in advance,


I have a worksheet named App1. I have a macro that creates a new worksheet and copies information. I would then like to rename the worsheet as App2. Each new worksheet added would be incremented - App3, etc. - My question is how can I rename and increment the new worksheet

I would also like to increment a cell in each new worksheet which indicates the application number - 1,2,3... etc.

Can you help?

Hi quick question:

When i copy a cell into a row or colum by clicking and dragging the black
plus sign, a little window pops up that gives me Auto fill options. is there
a way to stop it without copying another cell? i have tried a bunch of
things but no success.


I hope someone can help me.

I'm trying to write a formula where one value is incremented automatically when clicking and dragging, while the other values don't change.

For example:




HI TO all

Pls help me with This one,

im tired of dragging and copying, how can i make this one like this without pressing "ctrl+c" and dragging.

what formula or function must be use to it?

pls help me any help is deeply appreciated

LIB shoud be 22
SHW must be 25
ALB must be 28

site,,, no.
LIB,,, 22
LIB,,, 22
LIB,,, 22
SHW,,, 22
SHW,,, 22
SHW,,, 25
SHW,,, 25
LIB,,, 25
SHW,,, 25
SHW,,, 25
SHW,,, 25
LIB,,, 28
ALB,,, 28
LIB,,, 28
ALB,,, 28
SHW,,, 28
ALB,,, 28
ALB,,, 28

AFTER "CTRL+c" +dragging (so very time consuming)
site no.
LIB,,, 22
LIB,,, 22
LIB,,, 22
LIB,,, 22
LIB,,, 22
SHW,,, 25
SHW,,, 25
SHW,,, 25
SHW,,, 25
SHW,,, 25
SHW,,, 25
ALB,,, 28
ALB,,, 28
ALB,,, 28
ALB,,, 28
ALB,,, 28
ALB,,, 28
ALB,,, 28

how can i make it this way.


How can i set the options of auto fill option so that it will not automatically increment the numbers,so that i can fill all the cell with the same formula for all?Without copying and pasting it manually.



I know how to drag a hyperlink using the =HYPERLINK formula, but can only get it to work with an increment of 1.

Is there a way to have the increment be larger than one? In this case, by 13 cells?

I attached a sample file.

i need to change all price values in an inventory. i inserted a new column (C9) next to price column (C10) and in the first cell wrote formula that gave me the new value i wanted (=C10+0.05)
now if i autofill by dragging down from the corner of that cell the formula copies and i get the correct new values in the cells selected.
but...the column has 4500 rows so want to have alternative to dragging down manually with mouse all the way to bottom of doc.
how can i do this? i tried to use 'fill' from the home tab on the ribbon by copying the cell with the formula and clicking on top of column to select it all but whichever way i then tried to 'fill' i simply got the value from the selected cell pasted to the whole column rather than the results of the formula.
thanks in advance

Hi guys, working my way through a large worksheet of figures which I need to sum according to date, and then calculate the amount of each date's figures with tax on top.

Autosum automatically places the sum of a column underneath the bottommost cell. Is there a way to put it at the side of the bottommost cell? Up to now I've been inserting a blank row beneath, then autosumming then dragging that cell to the right. Or else I can select the cells and type the sum in manually but it would be nice to tell Excel where I want the autosum result to be, without having to manually type in =sum(h29:h31) over and over.

Also, for my tax calculation I've been Ctr-dragging the formula downwards to apply it to the subsequent amounts. It retains the value of the earlier amount until I double-click on it to force a re-calculation. I'm sure I've had other formulas automatically update when I ctr-drag them so why not this one? =K150*(1+12.5%)

Thanks in advance for advice,

I'm having a weird issue with excel that I haven't seen before. I'm trying to copy a formual to another cell and fill it down and the formula is being copied but the result isn't being properly updated. For example, in Column A I have the following values: 1, 2, 3. In column B, I have 3, 4, 5. I enter a formula in Column C to sum A + B (1+3), and the result appears as 4. When I try to copy or auto-fill that down through column C (summing 2+4, 3+5), the formula copies and updates but the result is showing the same as in cell C1 (i.e., 4). Any idea of how to fix this?


Hi, I'm new to this forum. Relatively good at Excel, but am always game to learn new formulas/VBA code.

I have a workbook where I have a button to automatically create a new worksheet. (ex. Report 1, Report 2, Report 3, etc...). I would like to have a Summary worksheet that automatically grabs the data from the existing and newly created worksheets (Report 1, Report 3, Report 3...etc) On the Summary worksheet, I would like to reference the exact same cell range from Report 1, Report 2, Report 3 etc...

However, I am unable to increment the values of the referenced worksheet on my Summary Worksheet. I'm not sure how to change the numerical value inside the single-quotes which refer to the worksheet Report 1, Report 2, etc. (ie 'Report 1'!A$89 ...) I would like to increment the 1 to a 2 and then to a 3 as each new worksheet is created and the Summary puts the data in the next available line, resulting in a Summary Report of Report 1 to Report N.

Hopefully that makes sense. If anyone has an idea, I would appreciate some guidance.

Thanks In Advance

Hi, I am using lookup function to find the last entry in a range on another worksheet. But the array table consists of 9 rows.
I want to find out a way to increment the array values by 9 instead of 1 as I am copying down on the current worksheet. I am looking for a solution that uses existing excel functions as a VBA would slow down the sheet and is irritating when the buttons pop up.
The following is the code I have entered:

So I am looking to change 36 to 45 and 43 to 52 when I copy. And incrementing by 9 throughout.

By the way I'm using 2007 version.

Thanks for any thoughts,

I need help copying a row from one worksheet to another using a formula. I manage a sales team of 22 salespeople and would like to have a separate worksheet for each salesperson. Is there a way for me to use a formula to locate all sales for each salesperson and copy the entire row of the main worksheet to their individual worksheet? There are 8 columns in this worksheet. The column I would like to use to identify the row(s) I would to like copy just contains the salesman name?

I have uploaded a .txt example of the format of the worksheet.

This is something that has perplexed me for [literally] years. It used to
happen with e2k, and is now happening with e2k3.

When I have a value in a cell, be it a month or some number, SOMETIMES when I
try to drag it to "copy" it, instead it'll increment itself. "Jan" will become
"Feb", then "Mar", etc. Likewise the number in the dragged cell will increment.

BUT...this only happens SOMETIMES. Other times, the value, whatever it is,

Of course, THIS time, I'm wanting it to increment, and it won't. (As opposed to
my usual not wanting it to and it does! )

Can someone enlighten me as to what it is I'm doing, not doing, should be doing,

Thanks in advance,



I am a total newbie with Excel, and I just need it for basic data entry.

I don't even really know the terminology for what I am trying to do, so it is making it hard to find the it is

When I enter a number into a cell, and then drag down in the column to auto fill the cells below, the number increases. I want to be able to drag without the number increasing in increment.

Where and how do I do this?

Thank-you in advance for your help!!