Hi there, I have over 100 tabs with 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

On Mar 26, 11:00*pm, Rob P wrote:
Hi there, I have over 100 tabs with 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

Rob P,

As Tim mentioned, post the portion of "the loop" you wrote and
reference your question in relation to that loop. I initally read
your post as a broad description of what you are trying to accomplish
with a single question: how do I include the "tab name" on the summary
page? (This could be accomplished through something like the
following: Worksheets("Sheet1").Range("a1").Value = Worksheets
("Sheet2").Name). You are more likely to get a more direct answer
when you post code along with a detailed question, rather than asking
a high-level question; unless you are indeed looking for a high-level
answer.

I did, however, infer what it is that you are trying to accomplish
(and this is assuming I inferred correctly based on your post). The
sample commented code below does not include any error checking and
assumes that the data is in the appropriate places. I spent very
little time testing it.

Best,

Matt Herbert

Sub LoopWorksheets()
Dim Wks As Worksheet
Dim wksSumm As Worksheet
Dim rngSumm As Range
Dim rngPaste As Range
Dim rngWksData As Range
Dim rngWksName As Range
Dim rngBottomRight As Range
Dim lngOffsetCol As Long

'assuming your Summary worksheet is called "Summary"
Set wksSumm = Worksheets("Summary")

For Each Wks In ActiveWorkbook.Worksheets
If Wks.Name < wksSumm.Name Then

'get Wks data
With Wks
'assumes the data starts in the upper-left corner
' of wksSumm and is contiguous
Set rngWksData = .Range("a1").CurrentRegion

'shift the range down 1 row (assumes each Wks has a
' header for the data; this header doesn't need to be
' copied to wksSumm)
Set rngWksData = rngWksData.Offset(1, 0)

'resize the range to eliminate the last row which
' shifted from Offset
Set rngWksData = rngWksData.Resize(rngWksData.Rows.Count -
1, _
rngWksData.Columns.Count)
End With

'paste Wks data to wksSumm
With wksSumm
'assumes the data starts in the upper-left corner
' of wksSumm and is contiguous; will error if no
' data is on the sheet
Set rngSumm = .Range("a1").CurrentRegion

'set the paste range as the last row in the data
' range
Set rngPaste = .Range("a" & rngSumm.Rows.Count + 1)

'set the paste range as the cell one to the right
' and one down from the lower-left most cell in
' rngSumm
Set rngPaste = rngPaste.Offset(0, 1)
End With

'this will write the range address to the Immediate window
' (View | Immediate Window); as you step through the program,
' i.e. F8 repeatedly, you'll be able to see how the ranges
' are behaving
Debug.Print "rngSumm :"; rngSumm.Address(external:=True)
Debug.Print "rngPste :"; rngPaste.Address(external:=True)
Debug.Print "rngWksDt:"; rngWksData.Address(external:=True)

'paste the data to wksSumm
rngWksData.Copy rngPaste
Application.CutCopyMode = False

'paste wksName in column A next to pasted data
With wksSumm
'same assumption as above (start cell and contiguous)
Set rngWksName = .Range("a1").CurrentRegion

'get bottom-right corner cell
Set rngBottomRight = rngWksName.Cells
(rngWksName.Cells.Count)

'offset column
lngOffsetCol = rngWksName.Columns.Count

'get lower-left cell
Set rngWksName = rngBottomRight.Offset(0, -1 *
lngOffsetCol + 1)

'get empty cells above the lower-left cell
Set rngWksName = .Range(rngWksName, rngWksName.End
(xlUp).Offset(1, 0))
Debug.Print "rngWksNm:"; rngWksName.Address
(external:=True)

rngWksName.Value = Wks.Name
End With
End If
Next

End Sub

Three posts hunh? Will you settle for one reply?
http://www.rondebruin.nl/copy2.htm

Here is a basic tutorial for running VBA:
http://www.anthony-vba.kefra.com/vba/vbabasic1.htm


HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Rob P" wrote:

Hi there, I have over 100 tabs with 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

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

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

On Mar 27, 12:39*pm, wrote:
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- Hide quoted text -

- Show quoted text -


got it - thank you very much Matt

I now have this working


thanks for your help