On Mar 27, 10:16*am, Rob P wrote:
On Mar 26, 11:00*pm, Rob P wrote:

Hi there, I have over100tabswith numerical data - each tab is named

I want to create a summary on one tab

I am having problems writing a macro to copy the data from each tab
onto the summary page

I want to paste each tab name on the summary sheet in column A with
the data in rows starting in Column B

i have written the loop - but am struggling to include the tab names -
can anyone help with this code?

any ideas? *many thanks

Sub MakeSummary()
'
' MakeSummary Macro
' Macro created 3/26/09 by Gwatcheater
'
'

* * Sheets("SUMMARY").Select
' * Clear the existing values (if any)
* * Range("$A$2:$D$60").Value = ""
' * J tracks the row number on the summary page
' * I tracks the sheet number being processed
* * J = 2
* * For I = 2 To Sheets.Count
* * * * A$ = Sheets(I).Name
' * Process the current sheet
* * * * Range("B" + Format(J)).FormulaR1C1 = "='" + A$ + "'!R1C1"
* * * * Range("C" + Format(J)).FormulaR1C1 = "='" + A$ + "'!R1C2"
* * * * Range("D" + Format(J)).FormulaR1C1 = "='" + A$ + "'!R1C3"
* * * * Range("E" + Format(J)).FormulaR1C1 = "='" + A$ + "'!R1C4"
' * Copy the sheet name in the A column
' * struggling with code for this

* * * * J = J + 1

* * Next I

End Sub

Rob,

It's good programming practice to enable "Require Variable
Declaration" (Tools | Options; Editor Page). If this is enabled, VBA
will compile your variables and let you know if something is wrong
with your syntax (such as a misspelling). This option will write
"Option Explicit" at the top of modules that are subsequently added.
The option will also require you to dimension the variables, e.g. Dim
j, Dim i, etc. When you dimension your variables you can also assign
a data type, letting VBA know how much memory to allocate to each
variable, e.g. Dim j As Long. If you don't include a data type, VBA
will assign the variable as a variant (which requires the most amount
of memory).

You may not know it, but you have your worksheet name answer already
in your For loop. I've added the line you are looking for, and
changed your A$ variable to strWksName (see below). Also, as a side
note, I noticed you are using Range("A"... but your formulas are R1C1
notation. As a result, I don't know if you prefer R1C1 or A1
notation. I find R1C1 notation less intuitive than the A1 notation.
The following is an alternative to your R1C1 notation: Range("B" &
j).Formula = "='" & strWksName & "'!A1"; the "&" is to concatenate
items (see Excel function help for CONCATENATE).

Best,

Matt

Sub MakeSummary()

Dim j As Long
Dim i As Long
Dim strWksName As String

Sheets("SUMMARY").Select

' Clear the existing values (if any)
Range("$A$2:$D$60").Value = ""

' J tracks the row number on the summary page
' I tracks the sheet number being processed
j = 2
For i = 2 To Sheets.Count
strWksName = Sheets(i).Name
' Process the current sheet
Range("A" + Format(j)).FormulaR1C1 = strWksName
Range("B" + Format(j)).FormulaR1C1 = "='" + strWksName + "'!
R1C1"
Range("C" + Format(j)).FormulaR1C1 = "='" + strWksName + "'!
R1C2"
Range("D" + Format(j)).FormulaR1C1 = "='" + strWksName + "'!
R1C3"
Range("E" + Format(j)).FormulaR1C1 = "='" + strWksName + "'!
R1C4"
' Copy the sheet name in the A column
' struggling with code for this

j = j + 1

Next i

End Sub