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

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

"farid2001" wrote:

> 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

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

Regards
farid2001

"mikeaj72" wrote:

>
> 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
> --------------------
>
>
> --
> mikeaj72
> ------------------------------------------------------------------------
> mikeaj72's Profile: http://www.thecodecage.com/forumz/member.php?userid=46
> View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=44996
>
>

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 wrote:
>
> Thanks guys, but I get error message "Subindex out of interval"
> What could be wrong?
>
> Regards
> farid2001
>
> "mikeaj72" wrote:
>
> >
> > 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
> > --------------------
> >
> >
> > --
> > mikeaj72
> > ------------------------------------------------------------------------
> > mikeaj72's Profile: http://www.thecodecage.com/forumz/member.php?userid=46
> > View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=44996
> >
> >

--

Dave Peterson

Thanks Dave

That was the problem, it works great now.

Regards
farid2001

"Dave Peterson" wrote:

> 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 wrote:
> >
> > Thanks guys, but I get error message "Subindex out of interval"
> > What could be wrong?
> >
> > Regards
> > farid2001
> >
> > "mikeaj72" wrote:
> >
> > >
> > > 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
> > > --------------------
> > >
> > >
> > > --
> > > mikeaj72
> > > ------------------------------------------------------------------------
> > > mikeaj72's Profile: http://www.thecodecage.com/forumz/member.php?userid=46
> > > View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=44996
> > >
> > >
>
> --
>
> Dave Peterson
>