Worksheet names in macro
 

Hi

I'm very new to macros and would appreciate any help. I am using Excel
in MS Office 2003. I'm trying to write a macro that summarizes info
onto a Summary tab from Multiple worksheets starting at the 3rd
worksheet. All the worksheets are set up similarly and I want to pull
the same 2 cells B4 and A3 from each worksheet to the Summary page.
Also all my worksheets have names. I tried changing the "Sheet" to
"Worksheet" and that doesn't help. What am I doing wrong?

Dim i As Integer, Sheet As Worksheet

Range("A1").Select
For i = 3 To Worksheets.Count Step 1
ActiveCell.FormulaR1C1 = "=Sheet(i)!R[3]C[1]"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=Sheet(i)!R[2]C[-1]"
ActiveCell.Offset(1, -1).Range("A1").Select
Next i
End Sub

Thanks
Thanks

Worksheet names in macro
 

Try

ActiveCell.FormulaR1C1 = "=Sheet" & i & "!R[3]C[1]"

or
ActiveCell.Value = "=Sheet" & i" & "!A8"


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=529901

Worksheet names in macro
 

not sure exactly what you want on the summary sheet, but i assumed you wanted a3
for each sheet added on the summary sheet and the same with b4.

see if this helps, if not, post back with more details


Sub test()
Dim i As Integer, Sheet As Worksheet
Dim wks As Worksheet
Set wks = Worksheets("Summary")

For i = 3 To Worksheets.Count Step 1
With Worksheets(i)
wks.Range("A3") = wks.Range("A3") + .Range("a3")
wks.Range("B4") = wks.Range("B4") + .Range("B4")
End With
Next i
End Sub



--


Gary


wrote in message
ups.com...
Hi

I'm very new to macros and would appreciate any help. I am using Excel
in MS Office 2003. I'm trying to write a macro that summarizes info
onto a Summary tab from Multiple worksheets starting at the 3rd
worksheet. All the worksheets are set up similarly and I want to pull
the same 2 cells B4 and A3 from each worksheet to the Summary page.
Also all my worksheets have names. I tried changing the "Sheet" to
"Worksheet" and that doesn't help. What am I doing wrong?

Dim i As Integer, Sheet As Worksheet

Range("A1").Select
For i = 3 To Worksheets.Count Step 1
ActiveCell.FormulaR1C1 = "=Sheet(i)!R[3]C[1]"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=Sheet(i)!R[2]C[-1]"
ActiveCell.Offset(1, -1).Range("A1").Select
Next i
End Sub

Thanks
Thanks

Worksheet names in macro
 

Many Thanks.

Worksheet names in macro
 

This worked when I used it on a test spreadsheet where the Tabs were
named Sheet 3, Sheet 4 and Sheet 5, but when I tried to use it on the
real file where the corresponding Tabs were named for example: North
Lane Apts, Sherri Lake Apts and Carriage House Apts, it would not
recognize the Sheet name probably because my formula is set up to call
them Sheet3, Sheet4, Sheet5. When the Macro started to run it prompted
me at every step to select the workbook and the worksheet that I wanted
to updated the values from. How can I get the Worksheet names included
in my formulas. There are many worksheets I just used 3 in my test
file.

Again what I was trying to do was to pick up information from a couple
cells from multiple worksheets and summarize it on one worksheet, with
each apartment complex having it's own line.

Here is what my macro looks like now:

Dim i As Integer, Sheet As Worksheet


Range("A1").Select
For i = 3 To Worksheets.Count Step 1
ActiveCell.FormulaR1C1 = "=Sheet" & i & "!R4C2"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=Sheet" & i & "!R3c1"
ActiveCell.Offset(1, -1).Range("A1").Select
Next i
End Sub

Thanks for any help.