Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet names in macro
Many Thanks.
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to create validation list of worksheet names | Excel Discussion (Misc queries) | |||
Need Macro to Collect Multiple Worksheet Names | Excel Worksheet Functions | |||
Macro to capture worksheet names | Excel Worksheet Functions | |||
I need a macro that will insert names in an excel worksheet, the . | Excel Programming | |||
return all worksheet tab names and chart sheet tab names in report - an example | Excel Programming |