Copy Value Of Last Cell

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
 
B

Bob Phillips

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)
 
T

Tom Ogilvy

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

rather than

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

halem2

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top