Looping and copying

[gypmaster]

Hi,

First post here so forgive my abject ignorance....

I've got a workbook with multiple sheets - don't necessarily know ho
many.

I want, when I click a button, to clear out a Summary sheet, loo
through all existing sheets in the workbook and copy the same cell
from each sheet into a cell on the Summary. OBviously, after I'v
copied the cells from the first sheet in to the Summary sheet I want t
copy the cells from the next sheet in to the next cell down in th
Summary sheet. Here's my code so far....

Private Sub CommandButton1_Click()
Rows("5:125").Select
Selection.Delete Shift:=xlUp
For Each ws In Worksheets
If ws.Name <> "Summary" Then
Worksheets(ws.Name).Activate
Worksheets(ws.Name).Range("A2:F2").Cop
Destination:=Worksheets("Summary").Range("E5")
MsgBox ws.Name
wrk = wrk + 1
End If
Next ws
Worksheets("Summary").Select
End Sub

Now this code will only copy the selected cells in to Range E5 on th
Summary sheet.

Anyone tell me how to use a variable to make sure the data is copie
into the next cell down?

Thanks in advance

 

[Frank Kabel]

Hi
try the following code

Private Sub CommandButton1_Click()
Dim row_index
Dim ws As Worksheet
Dim target_wks As Worksheet

Set target_wks = Worksheets("Summary")
row_index = 5
Rows("5:125").Delete Shift:=xlUp

For Each ws In Worksheets
If ws.Name <> "Summary" Then
target_wks.Range(Cells(row_index, "E"), Cells(row_index,
"J")).Value = _
ws.Range("A2:F2").Value
row_index = row_index + 1
'MsgBox ws.Name
End If
Next

target_wks.Select
End Sub

 

[Kalle]

Sub CopySameCellOfAllWorksheetsToSummary()
For i = 1 To Worksheets.Count
If Worksheets(i).Name <> "Summary" Then
Worksheets("Summary").Cells(i, DesiredColumnInSummary).Value =
_
Worksheets(i).Cells(DesiredRow, DesiredColumn).Value
End If
Next
End Sub

 

[Ron de Bruin]

Try this example gypmaster

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