Loop not returning to original workbook

[excelnut1954]

This macro works to a point. It's suppose to open up another workbook,
then come back, and perform a Do Until, looking for records that are
over 360 days old, copy that record over to the other workbook, then
come back and loop to read the next record.

It performs OK for the 1st record to be copied, but it won't come back
to the original workbook, (which has a name that changes each day,
based on the date.)

If you have any suggestions... please let me know. Thanks, J.O.

Sub OlderThanYearList()
'This will go through the list, and find all those records that are
older
'than a year. It will copy those records to the Year Old List file.

'Opens up the Year Old file
Workbooks.Open Filename:= _
"\\ceddfssrv01\cedroot\public\Furniture Staging List\Year old
list - Current Year.xls"

'Returns to Staging List (can't use the file name, since the name
changes each day based on date)
ActiveWindow.ActivatePrevious
With Sheets("Official List")
.Select
.Range("Date_Staged").Select
End With

Do Until IsEmpty(ActiveCell.Value)

ActiveCell.Offset(1, 0).Select 'goes down to the next cell.
If Range("Current_Date").Value - ActiveCell.Value > 360 Then
Rows(ActiveCell.Row).Select
Selection.Copy

'Switches to the Year Old file to paste
Workbooks("Year old list - Current Year.xls").Activate
Sheets("Imports").Select
Range("B65536").End(xlUp).Offset(1, -1).Select
ActiveSheet.Paste

'It should go back to the original file, and to the previous active
cell just copied from. Again, I can't use the workbook name because
that name will change each day.
ActiveWindow.ActivatePrevious 'I tried putting this line after End
IF, but it didn't work at all.

End If

Loop
End Sub

 

[Jim Thomlinson]

I assume that the original workbook is the book where the code is running
from. If that is the case then you can use

ThisWorkbook.Select

 

[excelnut1954]

I appreciate the reply, Jim.
But, I don't understand this command.
Are you implying that "This Workbook.Select" would be replaced by
inserting the workbook name in the command??
If so, I can't. The name will change each day. I tried the command just
as you have it, and it errors out.

IIsn't there a command I can use similar to the
ActiveWindow.ActivatePrevious command, but instead of Window, it has
Workbooks? I've tried different variations of it, but none of them
work.
Thanks,
J.O.

 

[Mark Lincoln]

In this kind of situation, I Dim a string variable called CallingDoc
and set it to the workbook name in question using:

CallingDoc = ActiveWorkbook.Name

Then I can activate this workbook at any time by using:

Workbooks(CallingDoc).Activate

I did this because my users sometimes like to rename their workbooks.

HTH

Mark

 

[Jim Thomlinson]

If it was me I would do something like this (Untested but it should be
close)...

Sub OlderThanYearList()
'This will go through the list, and find all those records that are older
'than a year. It will copy those records to the Year Old List file.
dim wbkOpen as Workbook
'Opens up the Year Old file
Workbooks.Open Filename:= _
"\\ceddfssrv01\cedroot\public\Furniture Staging List\Year old list -
Current Year.xls"

Set wbkOpen = Activeworkbook
Thisworkbook.Select

With Thisworkbook.Sheets("Official List")
.Select
.Range("Date_Staged").Select
End With

Do Until IsEmpty(ActiveCell.Value)

ActiveCell.Offset(1, 0).Select 'goes down to the next cell.
If Range("Current_Date").Value - ActiveCell.Value > 360 Then
Rows(ActiveCell.Row).Copy

'Switches to the Year Old file to paste
wbkOpen.Sheets("Imports").Range("B65536").End(xlUp).Offset(1, -1).Paste

End If

Loop
End Sub