Copy value from worksheets

[farid2001]

Dear gentlemen
I have about 250 worksheets in a workbook, each worksheet has customer name
which appears on cell "F2" of each worksheet, what I need is to be able to
make a summary in a new worksheet in such a way that in cell "A2" I would get
"F2" of first customer and in cell "B2" I would get cell "J3" of that
customer.
Then on cell "A3" I would get "F2" of second worksheet and in "B3" I would
get "J3" of second worksheet.
Then on cell "A4" get "F2" of third wksheet and "J3" of third wksheet and so
on.

Your help will be grearly appreciated.
thanks & regards
farid2001

 

[Mike]

Maybe something like this
Sub testing()
Const SumSheetName = "Sheet1"
Dim sumWS As Worksheet
Dim ws As Worksheet
Dim i As Long
i = 1
Set sumWS = Worksheets(SumSheetName)
For Each ws In Worksheets
If ws.Name <> sumWS.Name Then
sumWS.Range("A" & i) = ws.Range("F2")
sumWS.Range("B" & i) = ws.Range("J2")
i = i + 1
End If
Next ws
End Sub

 

[mikeaj72]

Code:
--------------------
Sub testing()
Const SumSheetName = "Sheet1"
Dim sumWS As Worksheet
Dim ws As Worksheet
Dim i As Long
i = 1
Set sumWS = Worksheets(SumSheetName)
For Each ws In Worksheets
If ws.Name <> sumWS.Name Then
sumWS.Range("A" & i) = ws.Range("F2")
sumWS.Range("B" & i) = ws.Range("J2")
i = i + 1
End If
Next ws
End Sub

 

[farid2001]

Thanks guys, but I get error message "Subindex out of interval"
What could be wrong?

Regards
farid2001

 

[Dave Peterson]

Either make sure you have a worksheet named Sheet1 when you start the macro.

Or change this line:
Const SumSheetName = "Sheet1"
to show the name of the sheet that acts as a summary sheet.

If that's not the line that causes the error, then you'll have to share more
information.

 

[farid2001]

Thanks Dave

That was the problem, it works great now.

Regards
farid2001