Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop not returning to original workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop not returning to original workbook
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 -- HTH... Jim Thomlinson "excelnut1954" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop not returning to original workbook
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. Jim Thomlinson wrote: 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 -- HTH... Jim Thomlinson "excelnut1954" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop not returning to original workbook
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 excelnut1954 wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop not returning to original workbook
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 -- HTH... Jim Thomlinson "excelnut1954" wrote: 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. Jim Thomlinson wrote: 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 -- HTH... Jim Thomlinson "excelnut1954" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning to original view. | Excel Discussion (Misc queries) | |||
loop through a column on a workbook copying data on each row to another workbook, then copy data back to the original workbook | Excel Programming | |||
Trouble returning to original active cell | Excel Programming | |||
Trouble turning off sheet updating and returning to original active cell | Excel Programming | |||
Returning to original workbook | Excel Programming |