Hi I have a macro which updates our core stock sheet, however is it possible when this is running it displays a progress bar to display its progress..
Below is my code
Thanks
Sub Update_Core_Stock()
'
' Update_Core_Stock Macro
' This will update the core stock. SBanks
'
' Message to confirm execution of program
var1 = MsgBox("YOU ARE ABOUT TO UPDATE THIS FILE.. ARE YOU SURE YOU WANT TO UPDATE?? IF YOU DO PLEASE CLICK YES OTHERWISE TO EXIT PLEASE PRESS NO ", vbYesNo + vbCritical, "Schneider-Electric Manufacturing Order Book")
If var1 = vbNo Then
stopnow = True
Else
End If
' Import FH30 Sheet Clear
Sheets("Import FH30").Select
Range("A2:Q2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A2").Select
' FH30 Open Orders Sheet Clear
Sheets("FH 30 O.Orders").Select
Range("A2:S2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A2").Select
' FH30 Open Delivery Sheet Clear
Sheets("FH 30 O.Delivery").Select
Range("A2:P2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A2").Select
' Import COOIS Sheet Clear
Sheets("Import COOIS").Select
Range("A2:T2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A2").Select
' OPEN Downloads from Supplu chain File
Sheets("Import FH30").Select
ChDir "J:\Supply Chain\Order Books\Manufacturing Order Book Downloads"
Workbooks.Open Filename:= _
"J:\Supply Chain\Order Books\Manufacturing Order Book Downloads\COIS Download.xls"
Workbooks.Open Filename:= _
"J:\Supply Chain\Order Books\Manufacturing Order Book Downloads\Import FH09.xls"
Workbooks.Open Filename:= _
"J:\Supply Chain\Order Books\Manufacturing Order Book Downloads\Open Deliveries Report.xls"
Workbooks.Open Filename:= _
"J:\Supply Chain\Order Books\Manufacturing Order Book Downloads\Open Order Report.xls"
' Copy SAP Download from IMPORT FH09
Windows("Import FH09.xls").Activate
Sheets("Sheet1").Select
Range("A2:Q2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
' Paste Import FH09 download into Core Stock
Windows("Copy of Manufacturing daily order book 2012 UK V2.xlsm").Activate
Sheets("Import FH30").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D2").Select
Sheets("FH 30 O.Orders").Select
' Copy SAP Download from Open Order Report
Windows("Open Order Report.xls").Activate
Sheets("Sheet1").Select
Range("A2:S2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
' Paste Import Open Orders download into Core Stock
Windows("Copy of Manufacturing daily order book 2012 UK V2.xlsm").Activate
Sheets("FH 30 O.Orders").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G2").Select
Sheets("FH 30 O.Delivery").Select
' Copy SAP Download from Open Delivery Report
Windows("Open Deliveries Report.xls").Activate
Sheets("Sheet1").Select
Range("A2:P2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
' Paste Import Open Delivery download into Core Stock
Windows("Copy of Manufacturing daily order book 2012 UK V2.xlsm").Activate
Sheets("FH 30 O.Delivery").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Import COOIS").Select
' Copy SAP Download from COOIS Report
Windows("COIS Download.xls").Activate
Sheets("Sheet1").Select
Range("A2:R2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
' Paste Import COOIS download into Core Stock
Windows("Copy of Manufacturing daily order book 2012 UK V2.xlsm").Activate
Sheets("IMPORT COOIS").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' Close Download Reports
Windows("Open Order Report.xls").Activate
Application.DisplayAlerts = False
ActiveWindow.Close
Windows("Open Deliveries Report.xls").Activate
Application.DisplayAlerts = False
ActiveWindow.Close
Windows("COIS Download.xls").Activate
Application.DisplayAlerts = False
ActiveWindow.Close
Windows("Import FH09.xls").Activate
Application.DisplayAlerts = False
ActiveWindow.Close
' UPDATE CORE STOCK VIA SAP TAB
Sheets("SAP").Select
Range("AI3:AK3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A3").Select
Do While ActiveCell.Formula <> ""
ActiveCell.Offset(0, 1).Select
Loop
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' Select Current Tab As Finished
Sheets("CURRENT").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
' Message upon Completion
var1 = MsgBox("Imports Completed and Document Saved", vbOKOnly + vbInformation, "Thankyou")
End Sub
Below is my code
Thanks
Sub Update_Core_Stock()
'
' Update_Core_Stock Macro
' This will update the core stock. SBanks
'
' Message to confirm execution of program
var1 = MsgBox("YOU ARE ABOUT TO UPDATE THIS FILE.. ARE YOU SURE YOU WANT TO UPDATE?? IF YOU DO PLEASE CLICK YES OTHERWISE TO EXIT PLEASE PRESS NO ", vbYesNo + vbCritical, "Schneider-Electric Manufacturing Order Book")
If var1 = vbNo Then
stopnow = True
Else
End If
' Import FH30 Sheet Clear
Sheets("Import FH30").Select
Range("A2:Q2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A2").Select
' FH30 Open Orders Sheet Clear
Sheets("FH 30 O.Orders").Select
Range("A2:S2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A2").Select
' FH30 Open Delivery Sheet Clear
Sheets("FH 30 O.Delivery").Select
Range("A2:P2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A2").Select
' Import COOIS Sheet Clear
Sheets("Import COOIS").Select
Range("A2:T2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A2").Select
' OPEN Downloads from Supplu chain File
Sheets("Import FH30").Select
ChDir "J:\Supply Chain\Order Books\Manufacturing Order Book Downloads"
Workbooks.Open Filename:= _
"J:\Supply Chain\Order Books\Manufacturing Order Book Downloads\COIS Download.xls"
Workbooks.Open Filename:= _
"J:\Supply Chain\Order Books\Manufacturing Order Book Downloads\Import FH09.xls"
Workbooks.Open Filename:= _
"J:\Supply Chain\Order Books\Manufacturing Order Book Downloads\Open Deliveries Report.xls"
Workbooks.Open Filename:= _
"J:\Supply Chain\Order Books\Manufacturing Order Book Downloads\Open Order Report.xls"
' Copy SAP Download from IMPORT FH09
Windows("Import FH09.xls").Activate
Sheets("Sheet1").Select
Range("A2:Q2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
' Paste Import FH09 download into Core Stock
Windows("Copy of Manufacturing daily order book 2012 UK V2.xlsm").Activate
Sheets("Import FH30").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D2").Select
Sheets("FH 30 O.Orders").Select
' Copy SAP Download from Open Order Report
Windows("Open Order Report.xls").Activate
Sheets("Sheet1").Select
Range("A2:S2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
' Paste Import Open Orders download into Core Stock
Windows("Copy of Manufacturing daily order book 2012 UK V2.xlsm").Activate
Sheets("FH 30 O.Orders").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G2").Select
Sheets("FH 30 O.Delivery").Select
' Copy SAP Download from Open Delivery Report
Windows("Open Deliveries Report.xls").Activate
Sheets("Sheet1").Select
Range("A2:P2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
' Paste Import Open Delivery download into Core Stock
Windows("Copy of Manufacturing daily order book 2012 UK V2.xlsm").Activate
Sheets("FH 30 O.Delivery").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Import COOIS").Select
' Copy SAP Download from COOIS Report
Windows("COIS Download.xls").Activate
Sheets("Sheet1").Select
Range("A2:R2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
' Paste Import COOIS download into Core Stock
Windows("Copy of Manufacturing daily order book 2012 UK V2.xlsm").Activate
Sheets("IMPORT COOIS").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' Close Download Reports
Windows("Open Order Report.xls").Activate
Application.DisplayAlerts = False
ActiveWindow.Close
Windows("Open Deliveries Report.xls").Activate
Application.DisplayAlerts = False
ActiveWindow.Close
Windows("COIS Download.xls").Activate
Application.DisplayAlerts = False
ActiveWindow.Close
Windows("Import FH09.xls").Activate
Application.DisplayAlerts = False
ActiveWindow.Close
' UPDATE CORE STOCK VIA SAP TAB
Sheets("SAP").Select
Range("AI3:AK3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A3").Select
Do While ActiveCell.Formula <> ""
ActiveCell.Offset(0, 1).Select
Loop
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' Select Current Tab As Finished
Sheets("CURRENT").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
' Message upon Completion
var1 = MsgBox("Imports Completed and Document Saved", vbOKOnly + vbInformation, "Thankyou")
End Sub