selecting cells

[oaishm]

I don't understand why this doesnt' work

I'm trying to copy data from one sheet to another but its' crazy

So I have

   file = "l:\somefile.xls"
    Dim book As Workbook
    Dim pointer As Range
    Dim sheet As Worksheet
    Set pointer = Selection <=save where I am
    On Error Resume Next
    Application.DisplayAlerts = False
    
    If Right(file, 3) = "xls" Or Right(file, 4) = "xlsx" Then
        Workbooks.Open (file)
        fn = Dir(file)
        Set book = Workbooks(fn)
        For Each sheet In book.Worksheets
            pointer.Value = file
            Set pointer = pointer.offset(1,0) <= this doesn't increment
            
        Next
        book.Close
        
    End If
    Application.DisplayAlerts = True
    
End Sub

I can't get this thing to move the selection down one cell. Do I have to do the super tedious

set currentbook = activeworkbook
.. bunch of code
currentbook.select
currentsheet.select
set pointer = pointer.offset(1,0)

Or some other such nonsense in order to get back to my pointer and increment down one cell?

 

[tweedle]

Set pointer = pointer.Offset(1, 0)
works fine for me.

If you want to see it move...
pointer.activate
or output to Immediate: Debug.Print pointer.Address

 

[oaishm]

Yes, it moves when the pointer is on the active sheet in the active workbook. I actually found a solution, but I'm sure there must be something much smarter. Surely we shouldn't have to activate books all over the place just to set values from one workbook to another

dim currentbook as workbook
dim currentsheet as worksheet
dim pointer as range
set currentbook = activeworkbook
set currentsheet = activeworksheet
set pointer = selection

... lots of code traversing many other workbooks that we're opening and closing

'Now to move the pointer down one cell
Workbooks(currentbook.Name).Worksheets(currentsheet.Name).Activate
                Selection.Activate
                Selection.Offset(1, 0).Select
                Set pointer = Selection

There must be something much faster

 

[tweedle]

you shouldn't need to use .select to punch data around.
.activate works where needed.

Sub foo()
    Dim currentbook, remotewb As Workbook
    Dim currentsheet As Worksheet
    Dim pointer As Range
    Set currentbook = ActiveWorkbook
    Set currentsheet = ActiveSheet
    
    NR = Cells(Rows.Count, 1).End(xlUp).Row + 1
    Set pointer = Cells(NR, 1)
    ofile = "blah.xls"
'    vFilename = Dir()
    Do While ofile <> ""
'... lots of code traversing many other workbooks that we're opening and closing
'    Set remotewb = Workbooks.Open vFilename
'    For Each Sheet In remotewb
'        .blah
'    Next Sheet
        pointer.Offset(0, 0) = ofile
        pointer.Offset(0, 1) = "Other sheet data"
        pointer.Offset(0, 2) = "more stuff"
        pointer.Offset(0, 3) = pointer.Address
        'Now to move the pointer down one cell
        'currentsheet.Activate
        Set pointer = pointer.Offset(1, 0)
        ofile = Left(ofile, Len(ofile) - 1)
    Loop
End Sub