G
Guest
I'm trying to creat a macro which will copy and paste the value of a cell
(say G12 on a series of worksheets) to a newly created sheet "Summary". The
code (so far) is below, but far short of what I want to acheive. Can someone
help me?
Sub GetMyCellValues()
Dim ws As Worksheet
Dim myCell As String
i = 1
myCell = Application.InputBox("Enter Cell Reference")
Sheets.Add
ActiveSheet.Name = "Summary"
For Each ws In Worksheets
Worksheets(ws.Name).Select
'Worksheets(ws.Name).Range("a4").Select
Worksheets(ws.Name).Range(myCell).Copy _
Destination:=Worksheets("Summary").Cells(i, 2)
Worksheets("Summary").Cells(i, 1) = ws.Name
i = i + 1
Next ws
End Sub
(say G12 on a series of worksheets) to a newly created sheet "Summary". The
code (so far) is below, but far short of what I want to acheive. Can someone
help me?
Sub GetMyCellValues()
Dim ws As Worksheet
Dim myCell As String
i = 1
myCell = Application.InputBox("Enter Cell Reference")
Sheets.Add
ActiveSheet.Name = "Summary"
For Each ws In Worksheets
Worksheets(ws.Name).Select
'Worksheets(ws.Name).Range("a4").Select
Worksheets(ws.Name).Range(myCell).Copy _
Destination:=Worksheets("Summary").Cells(i, 2)
Worksheets("Summary").Cells(i, 1) = ws.Name
i = i + 1
Next ws
End Sub