Copy Value Of Last Cell

  • Thread starter Thread starter halem2
  • Start date Start date
H

halem2

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)
 
sSheets(i) = Range("B65536").End(xlUp).Value

rather than

sSheets(i) = Range("B65536").End(xlUp).Select
 
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
 
Back
Top