Macro to update active workbook in one folder

[Mike @ GD]

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.

 

[Don Guillett]

You should always post your code for comments/suggestions Perhaps

activeworkbook

 

[Mike @ GD]

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]

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

 

[Mike @ GD]

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

 

[Shane Devenshire]

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

 

[Mike @ GD]

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???.

 

[Don Guillett]

try
application.displayalerts=false
code
application.displayalerts=true

 

[Mike @ GD]

DOn,

That did the trick, thanks very much to you and Shane.