Code:
[SIZE=1]Public Sub PlannedManufactureCsvUpload() 'UPLOAD PLANNED MANUFACTURE TO COGNOS CSV
If MsgBox("Upload Planning Data to Cognos" & vbCrLf & vbLf & "This function will take 3 - 4 min depending on your PC ", _
vbYesNo, "Cognos Planning Data Upload ") = vbNo Then MsgFlag = 1 'MESSAGE BOX UPLOAD YES/NO
If MsgFlag = 1 Then Exit Sub
Application.ScreenUpdating = False 'SWITCH OFF SCREEN UPDATE
Application.Calculation = xlManual 'SWITCH OFF CALCULATIONS
Sheets("CognosCSV").Select
Application.ScreenUpdating = False 'SWITCH OFF SCREEN UPDATE
Sheets("CognosCSV").Range("c3:IV1000").Value = "" 'CLEAR OLD DATA FROM "CognosCSV" SHEET
Sheets("CognosCSV").Range("b3:b3000").Value = Sheets("DATA").Range("A1:A1000").Value 'IMPORT SKU CODES FROM "DATA" SHEET
Sheets("CognosCSV").Range("C3:IE3").Value = Sheets("Planning Board").Range("T9:IV9").Value 'IMPORT MANUFACTURING DATES FROM "Planning Board" SHEET
Range("C4:IE1002").Value = "=CONCATENATE(R1C3,R2C,RC1)" 'CONCATENATE CELL REF TO STRING
Range("C4:IE1002").Value = Range("C4:IE1002").Value 'REPLACE FORMULAS WITH STATIC VALUES
Range("C4:IE1002").Select 'CONVERT STRING TO LOOKUP PLANNED VALUES
Selection.Replace What:="*='Planning ", Replacement:="='Planning ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("C4:IE1002").Value = Range("C4:IE1002").Value 'REPLACE FORMULAS WITH STATIC VALUES
Range("B4:IE1003").Select 'SELECT DATA
Selection.NumberFormat = "0.00" 'REFORMAT TO NUMBER AS TO 2 DECIMAL PLACES
Sheets("CognosCSV").Select 'COPY COGNOS CSV
Sheets("CognosCSV").Copy 'CREATE NEW SHEET
Application.DisplayAlerts = False 'DISABLE WARNINGS
ActiveWorkbook.SaveAs Filename:="\\kates32\cognos\CSV Files\Planner_Data\Planned_Manufacture.csv" 'SAVE VALUES TO CSV FILE
Application.DisplayAlerts = True 'ENABLE WARNINGS
Windows("Planned_Manufacture.csv").Activate 'SET FOCUS TO CSV
ActiveWorkbook.Close 'CLOSE CSV
Sheets("Planning Board").Select 'SELECT PLANNING BOARD
Application.Calculation = xlAutomatic 'SWITCH ON CALCULATIONS
Application.ScreenUpdating = True 'SWITCH ON SCREEN UPDATE
End Sub[/SIZE]
The Module is hanging @ Line 12 & 13 (CONCATENATE) & @ Lines 23 - 28 where it is Copying and transfering data
Any help in making this more efficient would be appreciated