| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to update active workbook in one folder
I have a macro to update inventory in the workbook the macro is written in,
however when saved with a new name the macro will not work. How should the macro be written to update saved files that are kept in the same folder. The folder name is "Quote Workbook", the file names will range from 630000 to 659999. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro to update active workbook in one folder
You should always post your code for comments/suggestions Perhaps
activeworkbook -- Don Guillett Microsoft MVP Excel SalesAid Software "Mike @ GD" wrote in message ... I have a macro to update inventory in the workbook the macro is written in, however when saved with a new name the macro will not work. How should the macro be written to update saved files that are kept in the same folder. The folder name is "Quote Workbook", the file names will range from 630000 to 659999. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro to update active workbook in one folder
Sorry...new at this, here's the code
Sub Update() ' ' Update Macro ' Macro recorded 12/15/2008 by micmul ' Application.ScreenUpdating = False Workbooks.Open Filename:= _ "C:\Documents and Settings\micmul\Desktop\Quote Workbook\inventory.xls" Cells.Select Selection.Copy Windows("Template.xls").Activate Sheets("Inventory").Select Cells.Select ActiveSheet.Paste Windows("inventory.xls").Activate Range("D15").Select Application.CutCopyMode = False ActiveWindow.Close Range("H19").Select Sheets("Quote").Select Application.ScreenUpdating = True End Sub "Don Guillett" wrote: You should always post your code for comments/suggestions Perhaps activeworkbook -- Don Guillett Microsoft MVP Excel SalesAid Software "Mike @ GD" wrote in message ... I have a macro to update inventory in the workbook the macro is written in, however when saved with a new name the macro will not work. How should the macro be written to update saved files that are kept in the same folder. The folder name is "Quote Workbook", the file names will range from 630000 to 659999. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro to update active workbook in one folder
Getting a run time error 9, sub script out of range @ point of
"Sheets(mysht).Range(myrng).Copy". Below is the script as it is. Sub Update() ' ' Update Macro ' Macro recorded 12/15/2008 by micmul ' Sub copytoactiveworkbook() mywb = "C:\Documents and Settings\micmul\Desktop\Quote Workbook\inventory.xls" mysht = "Inventory" myrng = "A1:U5511" Application.ScreenUpdating = False Workbooks.Open Filename:=mywb Sheets(mysht).Range(myrng).Copy ActiveWindow.Close True Range("E6").Select ActiveSheet.Paste Application.ScreenUpdating = True End Sub "Don Guillett" wrote: One way to copy from a workbook to your ACTIVE workbook Sub copytoactiveworkbook() mywb = "C:\yourworkbooknamehere.xls" mysht = "yoursourcesheetnamehere" myrng = "B73:F79" Application.ScreenUpdating = False Workbooks.Open Filename:=mywb Sheets(mysht).Range(myrng).Copy ActiveWindow.Close True Range("E6").Select ActiveSheet.Paste Application.ScreenUpdating = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Mike @ GD" wrote in message ... Sorry...new at this, here's the code Sub Update() ' ' Update Macro ' Macro recorded 12/15/2008 by micmul ' Application.ScreenUpdating = False Workbooks.Open Filename:= _ "C:\Documents and Settings\micmul\Desktop\Quote Workbook\inventory.xls" Cells.Select Selection.Copy Windows("Template.xls").Activate Sheets("Inventory").Select Cells.Select ActiveSheet.Paste Windows("inventory.xls").Activate Range("D15").Select Application.CutCopyMode = False ActiveWindow.Close Range("H19").Select Sheets("Quote").Select Application.ScreenUpdating = True End Sub "Don Guillett" wrote: You should always post your code for comments/suggestions Perhaps activeworkbook -- Don Guillett Microsoft MVP Excel SalesAid Software "Mike @ GD" wrote in message ... I have a macro to update inventory in the workbook the macro is written in, however when saved with a new name the macro will not work. How should the macro be written to update saved files that are kept in the same folder. The folder name is "Quote Workbook", the file names will range from 630000 to 659999. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro to update active workbook in one folder
Hi,
Modify Don's code with a couple of DIM statemensts Sub copytoactiveworkbook() Dim mywb As String Dim mysht As String Dim myrng As String mywb = "C:\yourworkbooknamehere.xls" mysht = "yoursourcesheetnamehere" myrng = "B73:F79" Application.ScreenUpdating = False Workbooks.Open Filename:=mywb Sheets(mysht).Range(myrng).Copy ActiveWindow.Close True Range("E6").Select ActiveSheet.Paste Application.ScreenUpdating = True End Sub -- If this helps, please click the Yes button Cheers, Shane Devenshire "Mike @ GD" wrote: Getting a run time error 9, sub script out of range @ point of "Sheets(mysht).Range(myrng).Copy". Below is the script as it is. Sub Update() ' ' Update Macro ' Macro recorded 12/15/2008 by micmul ' Sub copytoactiveworkbook() mywb = "C:\Documents and Settings\micmul\Desktop\Quote Workbook\inventory.xls" mysht = "Inventory" myrng = "A1:U5511" Application.ScreenUpdating = False Workbooks.Open Filename:=mywb Sheets(mysht).Range(myrng).Copy ActiveWindow.Close True Range("E6").Select ActiveSheet.Paste Application.ScreenUpdating = True End Sub "Don Guillett" wrote: One way to copy from a workbook to your ACTIVE workbook Sub copytoactiveworkbook() mywb = "C:\yourworkbooknamehere.xls" mysht = "yoursourcesheetnamehere" myrng = "B73:F79" Application.ScreenUpdating = False Workbooks.Open Filename:=mywb Sheets(mysht).Range(myrng).Copy ActiveWindow.Close True Range("E6").Select ActiveSheet.Paste Application.ScreenUpdating = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Mike @ GD" wrote in message ... Sorry...new at this, here's the code Sub Update() ' ' Update Macro ' Macro recorded 12/15/2008 by micmul ' Application.ScreenUpdating = False Workbooks.Open Filename:= _ "C:\Documents and Settings\micmul\Desktop\Quote Workbook\inventory.xls" Cells.Select Selection.Copy Windows("Template.xls").Activate Sheets("Inventory").Select Cells.Select ActiveSheet.Paste Windows("inventory.xls").Activate Range("D15").Select Application.CutCopyMode = False ActiveWindow.Close Range("H19").Select Sheets("Quote").Select Application.ScreenUpdating = True End Sub "Don Guillett" wrote: You should always post your code for comments/suggestions Perhaps activeworkbook -- Don Guillett Microsoft MVP Excel SalesAid Software "Mike @ GD" wrote in message ... I have a macro to update inventory in the workbook the macro is written in, however when saved with a new name the macro will not work. How should the macro be written to update saved files that are kept in the same folder. The folder name is "Quote Workbook", the file names will range from 630000 to 659999. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro to update active workbook in one folder
Shane, & Don,
Thanks for the help, all is working well. The only issue at this point is the anoying popup's for the clip board, is the a alternative to this, would their be a difference between the windows and system clipboard???. "Shane Devenshire" wrote: Hi, Modify Don's code with a couple of DIM statemensts Sub copytoactiveworkbook() Dim mywb As String Dim mysht As String Dim myrng As String mywb = "C:\yourworkbooknamehere.xls" mysht = "yoursourcesheetnamehere" myrng = "B73:F79" Application.ScreenUpdating = False Workbooks.Open Filename:=mywb Sheets(mysht).Range(myrng).Copy ActiveWindow.Close True Range("E6").Select ActiveSheet.Paste Application.ScreenUpdating = True End Sub -- If this helps, please click the Yes button Cheers, Shane Devenshire "Mike @ GD" wrote: Getting a run time error 9, sub script out of range @ point of "Sheets(mysht).Range(myrng).Copy". Below is the script as it is. Sub Update() ' ' Update Macro ' Macro recorded 12/15/2008 by micmul ' Sub copytoactiveworkbook() mywb = "C:\Documents and Settings\micmul\Desktop\Quote Workbook\inventory.xls" mysht = "Inventory" myrng = "A1:U5511" Application.ScreenUpdating = False Workbooks.Open Filename:=mywb Sheets(mysht).Range(myrng).Copy ActiveWindow.Close True Range("E6").Select ActiveSheet.Paste Application.ScreenUpdating = True End Sub "Don Guillett" wrote: One way to copy from a workbook to your ACTIVE workbook Sub copytoactiveworkbook() mywb = "C:\yourworkbooknamehere.xls" mysht = "yoursourcesheetnamehere" myrng = "B73:F79" Application.ScreenUpdating = False Workbooks.Open Filename:=mywb Sheets(mysht).Range(myrng).Copy ActiveWindow.Close True Range("E6").Select ActiveSheet.Paste Application.ScreenUpdating = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Mike @ GD" wrote in message ... Sorry...new at this, here's the code Sub Update() ' ' Update Macro ' Macro recorded 12/15/2008 by micmul ' Application.ScreenUpdating = False Workbooks.Open Filename:= _ "C:\Documents and Settings\micmul\Desktop\Quote Workbook\inventory.xls" Cells.Select Selection.Copy Windows("Template.xls").Activate Sheets("Inventory").Select Cells.Select ActiveSheet.Paste Windows("inventory.xls").Activate Range("D15").Select Application.CutCopyMode = False ActiveWindow.Close Range("H19").Select Sheets("Quote").Select Application.ScreenUpdating = True End Sub "Don Guillett" wrote: You should always post your code for comments/suggestions Perhaps activeworkbook -- Don Guillett Microsoft MVP Excel SalesAid Software "Mike @ GD" wrote in message ... I have a macro to update inventory in the workbook the macro is written in, however when saved with a new name the macro will not work. How should the macro be written to update saved files that are kept in the same folder. The folder name is "Quote Workbook", the file names will range from 630000 to 659999. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro to update active workbook in one folder
try application.displayalerts=false code application.displayalerts=true -- Don Guillett Microsoft MVP Excel SalesAid Software "Mike @ GD" wrote in message ... Shane, & Don, Thanks for the help, all is working well. The only issue at this point is the anoying popup's for the clip board, is the a alternative to this, would their be a difference between the windows and system clipboard???. "Shane Devenshire" wrote: Hi, Modify Don's code with a couple of DIM statemensts Sub copytoactiveworkbook() Dim mywb As String Dim mysht As String Dim myrng As String mywb = "C:\yourworkbooknamehere.xls" mysht = "yoursourcesheetnamehere" myrng = "B73:F79" Application.ScreenUpdating = False Workbooks.Open Filename:=mywb Sheets(mysht).Range(myrng).Copy ActiveWindow.Close True Range("E6").Select ActiveSheet.Paste Application.ScreenUpdating = True End Sub -- If this helps, please click the Yes button Cheers, Shane Devenshire "Mike @ GD" wrote: Getting a run time error 9, sub script out of range @ point of "Sheets(mysht).Range(myrng).Copy". Below is the script as it is. Sub Update() ' ' Update Macro ' Macro recorded 12/15/2008 by micmul ' Sub copytoactiveworkbook() mywb = "C:\Documents and Settings\micmul\Desktop\Quote Workbook\inventory.xls" mysht = "Inventory" myrng = "A1:U5511" Application.ScreenUpdating = False Workbooks.Open Filename:=mywb Sheets(mysht).Range(myrng).Copy ActiveWindow.Close True Range("E6").Select ActiveSheet.Paste Application.ScreenUpdating = True End Sub "Don Guillett" wrote: One way to copy from a workbook to your ACTIVE workbook Sub copytoactiveworkbook() mywb = "C:\yourworkbooknamehere.xls" mysht = "yoursourcesheetnamehere" myrng = "B73:F79" Application.ScreenUpdating = False Workbooks.Open Filename:=mywb Sheets(mysht).Range(myrng).Copy ActiveWindow.Close True Range("E6").Select ActiveSheet.Paste Application.ScreenUpdating = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Mike @ GD" wrote in message ... Sorry...new at this, here's the code Sub Update() ' ' Update Macro ' Macro recorded 12/15/2008 by micmul ' Application.ScreenUpdating = False Workbooks.Open Filename:= _ "C:\Documents and Settings\micmul\Desktop\Quote Workbook\inventory.xls" Cells.Select Selection.Copy Windows("Template.xls").Activate Sheets("Inventory").Select Cells.Select ActiveSheet.Paste Windows("inventory.xls").Activate Range("D15").Select Application.CutCopyMode = False ActiveWindow.Close Range("H19").Select Sheets("Quote").Select Application.ScreenUpdating = True End Sub "Don Guillett" wrote: You should always post your code for comments/suggestions Perhaps activeworkbook -- Don Guillett Microsoft MVP Excel SalesAid Software "Mike @ GD" wrote in message ... I have a macro to update inventory in the workbook the macro is written in, however when saved with a new name the macro will not work. How should the macro be written to update saved files that are kept in the same folder. The folder name is "Quote Workbook", the file names will range from 630000 to 659999. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro to update active workbook in one folder
DOn,
That did the trick, thanks very much to you and Shane. "Don Guillett" wrote: try application.displayalerts=false code application.displayalerts=true -- Don Guillett Microsoft MVP Excel SalesAid Software "Mike @ GD" wrote in message ... Shane, & Don, Thanks for the help, all is working well. The only issue at this point is the anoying popup's for the clip board, is the a alternative to this, would their be a difference between the windows and system clipboard???. "Shane Devenshire" wrote: Hi, Modify Don's code with a couple of DIM statemensts Sub copytoactiveworkbook() Dim mywb As String Dim mysht As String Dim myrng As String mywb = "C:\yourworkbooknamehere.xls" mysht = "yoursourcesheetnamehere" myrng = "B73:F79" Application.ScreenUpdating = False Workbooks.Open Filename:=mywb Sheets(mysht).Range(myrng).Copy ActiveWindow.Close True Range("E6").Select ActiveSheet.Paste Application.ScreenUpdating = True End Sub -- If this helps, please click the Yes button Cheers, Shane Devenshire "Mike @ GD" wrote: Getting a run time error 9, sub script out of range @ point of "Sheets(mysht).Range(myrng).Copy". Below is the script as it is. Sub Update() ' ' Update Macro ' Macro recorded 12/15/2008 by micmul ' Sub copytoactiveworkbook() mywb = "C:\Documents and Settings\micmul\Desktop\Quote Workbook\inventory.xls" mysht = "Inventory" myrng = "A1:U5511" Application.ScreenUpdating = False Workbooks.Open Filename:=mywb Sheets(mysht).Range(myrng).Copy ActiveWindow.Close True Range("E6").Select ActiveSheet.Paste Application.ScreenUpdating = True End Sub "Don Guillett" wrote: One way to copy from a workbook to your ACTIVE workbook Sub copytoactiveworkbook() mywb = "C:\yourworkbooknamehere.xls" mysht = "yoursourcesheetnamehere" myrng = "B73:F79" Application.ScreenUpdating = False Workbooks.Open Filename:=mywb Sheets(mysht).Range(myrng).Copy ActiveWindow.Close True Range("E6").Select ActiveSheet.Paste Application.ScreenUpdating = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Mike @ GD" wrote in message ... Sorry...new at this, here's the code Sub Update() ' ' Update Macro ' Macro recorded 12/15/2008 by micmul ' Application.ScreenUpdating = False Workbooks.Open Filename:= _ "C:\Documents and Settings\micmul\Desktop\Quote Workbook\inventory.xls" Cells.Select Selection.Copy Windows("Template.xls").Activate Sheets("Inventory").Select Cells.Select ActiveSheet.Paste Windows("inventory.xls").Activate Range("D15").Select Application.CutCopyMode = False ActiveWindow.Close Range("H19").Select Sheets("Quote").Select Application.ScreenUpdating = True End Sub "Don Guillett" wrote: You should always post your code for comments/suggestions Perhaps activeworkbook -- Don Guillett Microsoft MVP Excel SalesAid Software "Mike @ GD" wrote in message ... I have a macro to update inventory in the workbook the macro is written in, however when saved with a new name the macro will not work. How should the macro be written to update saved files that are kept in the same folder. The folder name is "Quote Workbook", the file names will range from 630000 to 659999. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Personal Macro Workbook in startup folder missing | Excel Discussion (Misc queries) | |||
| Macro to copy active worksheet to new workbook | Excel Discussion (Misc queries) | |||
| Move Active Workbook to another Folder | Excel Discussion (Misc queries) | |||
| Update Macro: Leave Colour As Before Once Cell is Not Active | Excel Discussion (Misc queries) | |||
| Macro: Exit active workbook without save? | Excel Worksheet Functions | |||
Linear Mode
