Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Referencing
It's Tuesday.. which I deem the 2nd Monday and I've only have 1 cup of coffee.
I have a macro that generates a worksheet based on user input. Once the X number of worksheets are generated a SUMMARY worksheet is generated. This worksheet will be populated with information from the other worksheets. Here's the code for the SUMMARY part... With ActiveWorkbook.Worksheets .Add befo=.Item(1) End With ActiveSheet.Select ActiveSheet.name = "SUMMARY" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "Project #" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "Project" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "Weekly Hours" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "Forecasted" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "% to Forecast" ActiveCell.Offset(1, -4).Range("A1").Select ActiveCell.FormulaR1C1 = "=MZ6756!RC[1]" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "=MZ6756!RC[1]" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "=MZ6756!R[66]C[3]" ActiveCell.Offset(0, 2).Range("A1").Select ActiveCell.FormulaR1C1 = "=MZ6756!R[68]C[1]" ActiveCell.Offset(1, 0).Range("A1").Select I want the last 4 referenced cells to see the worksheet but not by name, also the last two references need to be more like this... Selection.End(xlToDown).Select Like I said... it's the 2nd Monday and the caffeine has not kicked in.... THANK YOU!! -- Jack of all trades... master of none.. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Referencing
I assume you mean xldown on the worksheet with the data, not the summary
sheet. Since your formulas are relative to the the location of the formula on the summary sheet, and your macro is based on whatever cell is the active sheet, it is unclear where you want to do an end(xldown). Sub BBBB() Dim sName As String ActiveSheet.Name = "SUMMARY" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.Resize(1, 5) = Array("Project #", _ "Project", "Weekly Hours", "Forecasted", _ "% to Forecast") sName = Worksheets(1).Name ActiveCell.Offset(1, 0).FormulaR1C1 = _ "=" & sName & "!RC[1]" ActiveCell.Offset(1, 1).FormulaR1C1 = _ "=" & sName & "!RC[1]" ActiveCell.Offset(1, 2).FormulaR1C1 = _ "=" & sName & "!R[66]C[3]" ActiveCell.Offset(1, 3).FormulaR1C1 = _ "=" & sName & "!R[68]C[1]" ActiveCell.Offset(2, 0).Range("A1").Select End Sub -- Regards, Tom Ogilvy "Alex" wrote in message ... It's Tuesday.. which I deem the 2nd Monday and I've only have 1 cup of coffee. I have a macro that generates a worksheet based on user input. Once the X number of worksheets are generated a SUMMARY worksheet is generated. This worksheet will be populated with information from the other worksheets. Here's the code for the SUMMARY part... With ActiveWorkbook.Worksheets .Add befo=.Item(1) End With ActiveSheet.Select ActiveSheet.name = "SUMMARY" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "Project #" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "Project" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "Weekly Hours" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "Forecasted" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "% to Forecast" ActiveCell.Offset(1, -4).Range("A1").Select ActiveCell.FormulaR1C1 = "=MZ6756!RC[1]" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "=MZ6756!RC[1]" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "=MZ6756!R[66]C[3]" ActiveCell.Offset(0, 2).Range("A1").Select ActiveCell.FormulaR1C1 = "=MZ6756!R[68]C[1]" ActiveCell.Offset(1, 0).Range("A1").Select I want the last 4 referenced cells to see the worksheet but not by name, also the last two references need to be more like this... Selection.End(xlToDown).Select Like I said... it's the 2nd Monday and the caffeine has not kicked in.... THANK YOU!! -- Jack of all trades... master of none.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing Last Cell | Excel Worksheet Functions | |||
cell referencing | Excel Worksheet Functions | |||
Referencing Cell Next To Today's Date Cell | Excel Discussion (Misc queries) | |||
Cell referencing SC | Excel Discussion (Misc queries) | |||
Cell referencing | Excel Programming |