Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting multiple cell contents using a macro
Hi, I'm trying to build/refresh a summary sheet using a macro that pull key data from other sheets. I have started with listing the names of the other sheets with the d loop below and this works ok. The command lines commented out is wher I am having trouble. I would like to populate rows in the summary shee with some data from the other sheets. I tried to use 'evaluate' , bu not sure how to call the sheet using sheet numbers, I also tried a tem variable for the sheet name, but could not get that to work either. Ca anyone help with the correct way to do this? For i = 1 To Sheets.Count - 1 Cells(1 + i, 2).Value = Sheets(i + 1).Name ' Cells(1 + i, 8).Value = Evaluate(Sheets(i + 1).Name!h6) ' tempb = Sheets(i + 1).Name ' Cells(1 + i, 8).Value = Evaluate(tempb!h6) Next i Thanks Nei -- neilgran Posted from - http://www.officehelp.i |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting multiple cell contents using a macro
Maybe this one Neil
http://www.rondebruin.nl/copy2.htm Or with formula links http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl "neilgrant" wrote in message ... Hi, I'm trying to build/refresh a summary sheet using a macro that pulls key data from other sheets. I have started with listing the names of the other sheets with the do loop below and this works ok. The command lines commented out is where I am having trouble. I would like to populate rows in the summary sheet with some data from the other sheets. I tried to use 'evaluate' , but not sure how to call the sheet using sheet numbers, I also tried a temp variable for the sheet name, but could not get that to work either. Can anyone help with the correct way to do this? For i = 1 To Sheets.Count - 1 Cells(1 + i, 2).Value = Sheets(i + 1).Name ' Cells(1 + i, 8).Value = Evaluate(Sheets(i + 1).Name!h6) ' tempb = Sheets(i + 1).Name ' Cells(1 + i, 8).Value = Evaluate(tempb!h6) Next i Thanks Neil -- neilgrant Posted from - http://www.officehelp.in |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting multiple cell contents using a macro
Thanks Ron !! I tried the second solution you suggested and it works great. I would also like to make the sheet names on the summary sheet int hyperlinks to jump to the detail sheet, would you have an example o the code that I can add to do this? thanks Nei -- neilgran Posted from - http://www.officehelp.i |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting multiple cell contents using a macro
Why not add one forms button on the sheet and assign this macro
Sub test() On Error Resume Next Sheets(Cells(ActiveCell.Row, "A").Value).Select On Error GoTo 0 End Sub It will go to the sheet name in column A of the activecell row -- Regards Ron de Bruin http://www.rondebruin.nl "neilgrant" wrote in message ... Thanks Ron !! I tried the second solution you suggested and it works great. I would also like to make the sheet names on the summary sheet into hyperlinks to jump to the detail sheet, would you have an example of the code that I can add to do this? thanks Neil -- neilgrant Posted from - http://www.officehelp.in |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to separate contents of one cell into multiple horizontal cell | Excel Discussion (Misc queries) | |||
separate contents of one cell into multiple cells | Excel Worksheet Functions | |||
How do I combine the contents of multiple cells in one cell? | Excel Worksheet Functions | |||
Macro to remove contents of cell and move all other contents up one row | Excel Discussion (Misc queries) | |||
Splitting multiple cell contents containing values at end | Excel Worksheet Functions |