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

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

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

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