Excel VBA - Copy cell value

[GSEX]

I'm trying to copy the value of a cell reference("I3")from multipl
worksheets into a summary worksheet in a relative column to eac
worksheet in the next available cell using a macro.

would appreciate any help.
g

 

[Ron de Bruin]

Try this
http://www.rondebruin.nl/copy2.htm

 

[Tom Ogilvy]

i = 1
for each sh in worksheets
if lcase(sh.name) <> "summary" then
worksheets("summary").cells(1,i).Value = _
sh.Range("I3").Value
end if
Next

 

[Helen Trim]

This code works if your summary sheet is
called 'Summary'. It puts the data into row 2.

Sub InsertSummary()
Dim Sheet As Object

Worksheets("Summary").Range("A2").Select ' Edit start
cell here

'Step through all worksheets
For Each Sheet In Sheets
' Ignore the summary sheet
If Sheet.Name <> "Summary" Then ' Edit sheet name
here
' Take the value of I3 and put it on the Summary
sheet
ActiveCell.Value = Sheet.Range("I3").Value
' Move the cell on by 1
ActiveCell.Offset(0, 1).Select
End If
Next Sheet

End Sub

HTH
Helen

 

[Garry]

Hi gs,
This sounds familiar to me. Ponder this approach:
I use a similar spreadsheet solution in that it collects
data from several multi-rows/columns 'detail sheets' on to
a multi-rows/columns 'summary sheet', then the 'summary
sheet' data gets collected by a 'master sheet', also
comprised of multiple rows/columns. It's all handled using
formulas, not macros. The 'detail sheets' receive data
input and the formulas update the other sheets
automatically. The formulas are simply copied across
and/or down respectively, for the number of rows/columns
required. This simplifies tasks, reduces file size because
the 'code' isn't necessary and, you can protect the cells
collecting the data. Writing code must reference the exact
cells to write to or you could get unexpected results.
Formulas are 'built-in' to the cells collecting the data
so if rows/columns get inserted or deleted, nothing gets
disturbed. This is much easier for spreadsheets that get
revised frequently due to changes in requirements, etc.
Excel on-line help is a very good source for assistance
with formulas. I use code as a last resort to simply
lengthy procedures, etc. because one thing that
continually pops up with code is backward compatibility
issues. This "=" works with all versions of Excel as far
as I know.
Regards, GS