Solved How to copy a formula with a tab name.

January 16, 2013 at 02:56:49
Specs: Windows Vista
I have a workbook with 53 pages and a summary page bringing info in from each page as it is entered each week. I wanrt to add a link to each tab in turn on the summary page. i.e. col C would be "+'1'!$c$1" and col D should be "+'2'!$c$1" etc. If I try to copy/paste I get the same tab name in each cell. Can I copy and paste the changing tab name somehow or do I have to edit each link?

See More: How to copy a formula with a tab name.

Report •


#1
January 16, 2013 at 06:48:11
✔ Best Answer
First, I'd like to ask why you are using plus signs in your formulas? That is a very old-school syntax. The normal syntax these days is to use an equal sign.

='1'!$C$1

There is no easy way to drag or copy/paste foumulas and have it update the sheet names in each cell. It can however be done fairly easily with a macro.

The following code will create a formula that references C1 in each sheet from tab 2 to tab 53 (or however many sheets there are) by building the formula using the Tab name for each sheet. It will start placing these formulas in SHEET1!C2 and continue down Column C until it is done.

Modify it to fit your actual needs.

Sub GetRowFromWorksheet()
    For shtNum = 2 To Sheets.Count
       Sheets(1).Cells(shtNum, 3).Formula = _
            "='" & Sheets(shtNum).Name & "'!$C$1"
    Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 16, 2013 at 09:01:16
Hi DerbyDad03,

The + is a throwback to the days of Quatro Pro and Lotus 123 I guess. I also tend to use the + next to the numerical keypad when entering simple formulas as I find it easier.

Thanks for the solution, at first I did not realise that it would paste the results into sheet1 as I wanted them in sheet 9, but once I realised that I found the formulas and transposed them into the required cells across the page.
What tells the macro where to paste the cells and how would you amend the macro to paste the references in the cell below the one that the cursor is currently in?

Many thanks.


Report •

#3
January 16, 2013 at 10:10:13
First, as Moderator, I have reset the Best Answer since it appears that you have more questions on this issue. When we are done, feel free to mark whatever answer you feel is the best as, well, the best. :)

The code is very simple in that it uses the same number for both the Tab number as well as the Row number for the formulas. A breakdown follows:

For shtNum = 2 To Sheets.Count

This creates a For-Next loop starting from 2 to the number of sheets in the workbook.

re: What tells the macro where to paste the cells

       Sheets(1).Cells(shtNum, 3).Formula =

The Cells method takes 2 arguments: Cells(Rowindex, ColumnIndex)
Cells(1,1) is Row 1, Column 1 or "A1"
Cells(2,3) is Row 2, Column 3 or "C2"

Therefore, Cells(shtNum, 3) means C2, then C3, then C4 as it loops through the sheets.

.Formula tells VBA that we are going to put a formula in each of those cells.

            "='" & Sheets(shtNum).Name & "'!$C$1"

The equal sign and single quote for the formula are hardcoded text as is '!$C$1.

The tab name is determined by VBA using the .Name method. As the code loops through the sheets from 2 to Sheets.Count, it picks up the name of Sheets(2), then Sheets(3), then Sheets(4), etc.

Note that the VBA syntax of Sheets(number) means the tab that is in that numbered position, reading left to right. For example, Sheets(2) will always refer to the second tab, regardless of its name and regardless if you move the second tab to a different position in the wokrbook. Sheets(2) will always refer to then current 2nd tab when the code reaches that point.

re: "...how would you amend the macro to paste the references in the cell below the one that the cursor is currently in.

The first thing the code would need to do is determine the Row and Column numbers of the selection:

nextRw = Selection.Row
myCol = Selection.Column

Then it would need to increment the Row number each time through the loop to place the formulas in each row.

The final code might look like this:

Sub GetRowFromWorksheet()
'Determine Row and Column Of Selected cell
  nextRw = Selection.Row
  myCol = Selection.Column
'Loop through sheets
    For shtNum = 2 To Sheets.Count
'Increment Row for next formula
    nextRw = nextRw + 1
'Build formula and place it in the next cell
       Sheets(1).Cells(nextRw, myCol).Formula = _
            "='" & Sheets(shtNum).Name & "'!$C$1"
    Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions


Ask Question