Hi:

I'm modifying someone else's macro (which works fine) to instead of
inserting the cheet name to sort it, it would insert the value of the
last cell in column B

this is part of that macro

Sub SortALLSheets()
'
Dim WB As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim cSheets As Integer
Dim sSheets() As String
Dim i As Integer
'
Set WB = ActiveWorkbook 'get true array dimension
cSheets = WB.Sheets.Count
ReDim sSheets(1 To cSheets) 'fill array with worksheet names
'
For i = 1 To cSheets
'sSheets(i) = WB.Sheets(i).Name
sSheets(i) = Range("B65536").End(xlUp).Select ------HERE
'Selection.Copy
Next
Set ws = WB.Worksheets.Add 'create new sheet and put names in first
column
'
For i = 1 To cSheets
ws.Cells(i, 1).Value = sSheets(i)
Next

Just use

For i = 1 To cSheets
sSheets(i) = Range("B65536").End(xlUp).Value
Next


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"halem2" wrote in
message ...

Hi:

I'm modifying someone else's macro (which works fine) to instead of
inserting the cheet name to sort it, it would insert the value of the
last cell in column B

this is part of that macro

Sub SortALLSheets()
'
Dim WB As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim cSheets As Integer
Dim sSheets() As String
Dim i As Integer
'
Set WB = ActiveWorkbook 'get true array dimension
cSheets = WB.Sheets.Count
ReDim sSheets(1 To cSheets) 'fill array with worksheet names
'
For i = 1 To cSheets
'sSheets(i) = WB.Sheets(i).Name
sSheets(i) = Range("B65536").End(xlUp).Select ------HERE
'Selection.Copy
Next
Set ws = WB.Worksheets.Add 'create new sheet and put names in first
column
'
For i = 1 To cSheets
ws.Cells(i, 1).Value = sSheets(i)
Next

sSheets(i) = Range("B65536").End(xlUp).Value

rather than

sSheets(i) = Range("B65536").End(xlUp).Select

--
Regards,
Tom Ogilvy


"halem2" wrote:


Hi:

I'm modifying someone else's macro (which works fine) to instead of
inserting the cheet name to sort it, it would insert the value of the
last cell in column B

this is part of that macro

Sub SortALLSheets()
'
Dim WB As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim cSheets As Integer
Dim sSheets() As String
Dim i As Integer
'
Set WB = ActiveWorkbook 'get true array dimension
cSheets = WB.Sheets.Count
ReDim sSheets(1 To cSheets) 'fill array with worksheet names
'
For i = 1 To cSheets
'sSheets(i) = WB.Sheets(i).Name
sSheets(i) = Range("B65536").End(xlUp).Select ------HERE
'Selection.Copy
Next
Set ws = WB.Worksheets.Add 'create new sheet and put names in first
column
'
For i = 1 To cSheets
ws.Cells(i, 1).Value = sSheets(i)
Next

I have tried that but it copies the same value to th new sheet instea
of the actual value of the cells in every sheet. It's not transferrin
the values properly. Any other thoughts

--
halem
-----------------------------------------------------------------------
halem2's Profile: http://www.excelforum.com/member.php...nfo&userid=993
View this thread: http://www.excelforum.com/showthread.php?threadid=55986