Help with Streamlining VB Code (1 Viewer)

[sphynx]

[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

 

[JamesMcS]

Not used Excel VBa much, but is .value a valid property of the range object? By that I mean are you going to have to create a loop to individually populate all the cells in that range?

And I see you're using R1C1 system to select values in that line. Have you set Excel to use that system of reference? You've got R1C3,R2C,RC1 so no column ref in the second but and no row ref in the third. Will that work?

Forgive me if I'm asking stupid questions, just guessing really.....

 

[Brianwarnock]

The concatenate looks ok and works fine on a smaller test sample I set up. Of course I do not have your workbook so cannot see where yours is hanging.

as for the

Sheets("CognosCSV").Select  'COPY COGNOS CSV
Sheets("CognosCSV").Copy

The select is not needed , the copy will copy the sheet to a new workbook that will automatically be created. If you want a new sheet in the same workbook use the Before or After keyword.

Where does it actually hang.

Brian

 

[Brianwarnock]

Not used Excel VBa much, but is .value a valid property of the range object? By that I mean are you going to have to create a loop to individually populate all the cells in that range?

And I see you're using R1C1 system to select values in that line. Have you set Excel to use that system of reference? You've got R1C3,R2C,RC1 so no column ref in the second but and no row ref in the third. Will that work?

Forgive me if I'm asking stupid questions, just guessing really.....

There is no such thing as a stupid question, ignorance is not a crime, we all are ignorant of many things.

It is all fine the lack of a C or R ref says "current Column or Row"
Oh and no he doesn't need a loop.

Brian

 

[sphynx]

I kind of expected the concatenate to hang as it fills approx 24,000 cells.

It is the Opening, Transfering and saving of data to the external CSV that seems to be the problem.

I did try something in this vain, but couldnt get it to work

Windows(Win1).sheets(Sheet1).Range(A1:DE1000).value =Windows(Win2).sheets(Sheet1).Range(A1:DE1000).value

I this possible in any way??

 

[Brianwarnock]

Never having done it it is new to me also, so no immediate help to you.

as for the fact that its 24000 cells , my reaction is so what. Do you know how many it had done?

Brian

 

[Brianwarnock]

Ok created more data to recreate yours, it is the conversion from formula to static values that takes a long time, but it gets there.

Brian

Ps my old machine, 2002, is slow anyway.

 

[Brianwarnock]

Ok had a bit of a play

the Sheets("sheetname").Copy creates a workbook called Book1 with one sheet of sheetname, but does not save it, so you need to change your saveas from
Activeworkbook.saveas.....
to
Workbooks("Book1").saveas....


Brian

 

[sphynx]

I implimented the last change you suggested to the save function and also rejigged the re-calculation points in my modules.

I have managed to get the module to run 3 times for diffrenet data sets in just under 2 minutes, so I am happy.

Thanks for your help

 

[Brianwarnock]

Glad its all ok, there I've typed ok again, didn't realise how often I typed it until I reread my posts.

BTW I loved the full documentation, I wish I was that organized.

Brian