SOLVED Editing A Macro

[9januerry9]

From Linked In I export my connections to a .csv file and then format it using the macro below. I then save the file as a Macro Enabled Worksheet. I find that, after saving, the worksheet is in Manual Recalc mode. I would like to add a section to the attached Macro which changes the recalc method to Automatic. Is this doable and can someone show me how the Macro should be changed to reflect that? Thank you very much for your assistance.

Sub FormatLinkedInExportFile()

'

' FormatLinkedInExportFile Macro

' Format Linked In Export File

'

' Keyboard Shortcut: Ctrl+Shift+F

'

Dim LastRow As Long



LastRow = Range("B" & Rows.Count).End(xlUp).Row



Rows("1:1").Select

Selection.Delete Shift:=xlUp

Columns("I:AC").Select

Selection.Delete Shift:=xlToLeft

Columns("B:K").Select

ActiveWorkbook.Worksheets("linkedin_connections_export_mic").Sort.SortFields. _

Clear

ActiveWorkbook.Worksheets("linkedin_connections_export_mic").Sort.SortFields. _

Add Key:=Range("D1" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, _

DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("linkedin_connections_export_mic").Sort

.SetRange Range("B1:K" & LastRow)

.Header = xlGuess

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

Selection.Columns.AutoFit

Columns("A:A").Select

Selection.ColumnWidth = 0.42

Columns("B:B").ColumnWidth = 9.29

Columns("C:C").Select

Selection.Delete Shift:=xlToLeft

Selection.ColumnWidth = 24.57

Columns("D").Select

Selection.Delete Shift:=xlToLeft

Selection.ColumnWidth = 30.29

Columns("E:F").Select

Selection.Delete Shift:=xlToLeft

Selection.ColumnWidth = 52.86

Columns("F:F").Select

Selection.Delete Shift:=xlToLeft

ActiveWindow.LargeScroll ToRight:=1

Selection.ColumnWidth = 48.14

ActiveWindow.SmallScroll ToRight:=-5



End Sub

 

[clifford_cooley]

If I understand you correctly, Excel is no longer automatically calculating after sheet changes.

Your thinking this is a result of creating a Vbscript Sub and wondering how to edit the sub to correct this problem?

 

[9januerry9]

Thank you in advance for your input. To be clear, the worksheet in question begins as a .csv file which has been exported from Linked In. I'm assuming that as a .csv file it is in Manual Recalc mode (?). That mode doesn't change when I run the Macro to format it the way I want it to look. After running the Macro, I open the newly created file as well as another file which has calculations in it. It's that 2nd file that I open that reverts to Manual recalc because as I understand the way Excel operates if you have two files open at the same time calculation will default to the lower setting (am I right about that?). So my objective is to add code to the Macro which formats the .csv file to force it to calculate Automatically. I hope I'm making myseld clear.

 

[clifford_cooley]

I don't work in CSV. That might be why I'm not familiar with your dilemma. Upon opening CSV I save to the format I use most.

It might be possible to use other subs to make calculations automatic. You can tell Excel to Calculate at any time through code. By placing "Calculate" in "Private Sub Worksheet_Change", you are telling Excel to calculate every time the sheet changes.

Private Sub Worksheet_Change(ByVal Target As Range)
  Calculate
End Sub

Please be advised I am by no means an expert at coding, I simply know more than the average person.


.

 

[9januerry9]

Thanks again for your input. I am not a Macro expert by any means so this question may sound dumb. Where do I put the code that you provided? Do I add it somewhere in the Macro? Is it a separate Macro? Can you assist?

 

[clifford_cooley]

Right click one of your tabs. You will see an option to "View Code". This will open "Microsoft Visual Basic for Applications (aka: VBA)". That is where you can edit all code stored, whether it was macro recorded or manually programmed.

The default window when viewing code is code page for that specific tab. You can navigate/edit code for all other tabs and macros once within VBA. If you will notice the image I posted above has both the file name and sheet number. Any code placed in that window will be specific to "sheet3" in that workbook.

The default drop down on the left is "General". Change that to "Worksheet" and you will have other options in the right drop down. The one I listed above is listed in the right drop down as "Change".

Other thoughts:
Please keep in mind this is independent from Macros. Macros have a specific location for code inside VBA much like workbooks and worksheets. And even though the code is independent from each other, they both have the ability to call each other. But for the simple task you are asking about that is not important for the moment. It's just that any code that is moved from the Macro location is technically no longer called a Macro, even though it started out as a Macro and can possibly still be called from a Macro. Once you step outside the confines of a Macro, it should be referenced as VBA coding. To make it even more confusing VBA is still yet different than VB, which is short for Visual Basic. So if you do a research for help on coding in Excel, make sure you are referring to VBA not VB.

 

[9januerry9]

I am still sooooooo confused. I think I'm making a mountain out of a molehill. It seems to me that after I run the macro which formats the .csv file, and before I save it, I should just change the calculation method to Automatic. Doesn't that make more sense? The other thought that I had was to add the line of code that you provided

Worksheet_SelectionChange(ByVal Target As Range)

somewhere in the formatting Macro, but I don't know where to put it.

Please let me know if you think it's worthwhile pursuing this or should we just consider this thread resolved? Thanks again for all of your help.

 

[clifford_cooley]

Worksheet_SelectionChange(ByVal Target As Range)

somewhere in the formatting Macro, but I don't know where to put it.

When you select the option from the drop down, VBA places the Sub in the appropriate location. There is not need in wondering where you should move it to, because it is where it needs to be. All you are needing to do is select "Change" from the right drop down and then add "Calculate" to that Sub. Then every time there is a change on the sheet, sheet calculation will be initiated. What I'm instructing you to do in independent from your Macro coding.

 

[9januerry9]

I'm really not getting this at all. It's not you. It's me. I don't even know how to explain my problem. All I know is that after exporting my connections to the .csv file and running the formatting macro, the sheet remains in Manual recalc. I am simply going to change the calculation method before saving. I appreciate your help. I am almost 83 years old, so some of this is quite foreign to me. I know enough to be dangerous. I am marking this issue as resolved.

 

[clifford_cooley]

Is there a reason why you are not saving to xls? I assumed when you started the thread you had reason to continue using the CSV file format.

 

[9januerry9]

I do save it as an .xls file. See attached snip where I save it as a Macro Enabled Workbook.

 

[clifford_cooley]

Maybe I'm missing something. Are you able to run the Macro? Because Macro's are not meant to be run automatically.

 

[9januerry9]

Yes. As soon as the .csv file is exported from Linked In, I open the file in .csv format. I then invoke Shift-Ctrl F and that formats the file. I then save the file as a Macro Enabled Workbook. So, I'm thinking that all I have to do before saving is change the calculation method to Automatic.

 

[clifford_cooley]

OK, I think I have been misunderstanding this whole thread.

With all your patience, I think you finally got through to me. The code you are asking about that you can add to your Macro is as follows.

    Application.Calculation = xlManual
    Application.Calculation = xlAutomatic

Placing one or the other line in your code will either switch the mode to Manual or Automatic.

 

[9januerry9]

Yessssssssssss!!!! That's the ticket. I boldly went where no man has gone before and just put that magic line in the formatting macro and lo and behold, after running the macro, the resulting Macro Enabled Workbook is set to calculate Automatically. Thanks for your persistence. We can now officially close this thread.

 

[9januerry9]

Wait a minute. Why doesn't it hold that setting. After I close the file and then reopen it, it's back to Manual.

 

[clifford_cooley]

Hmm, I'm not sure. I assume during closing you did save the workbook after toggling the calculation mode.

 

[9januerry9]

I may have discovered something. When I open Excel, it defaults to an Autosaved file called Personal. I have no idea why it opens that file. In any event, that file was set to Manual. So, I reasoned that any file that you then open (2 files open at the same time) will then default to the lower level of calculation. So, I reset Personal to calc Automatically. Now it seems to be OK. Perhaps you know why Personal opens and how you just get it to open a plain Excel workbook?

 

[clifford_cooley]

Ahh yeah, I know a little about that file. That is a hidden workbook that loads every time Excel loads. The reason for this file is for coding that people want to use in everything they work with. Without this file the user would need to keep track of all their personal coding themselves. And to be honest the file had slipped my mind. With it being a hidden file I often forget about it. Anyway you may have stumbled across the solution that was alluding my thoughts for the time. Now it all makes since for once.

Edit:
I just noticed my Excel no longer is loading this file by default. I once upon a time used it though.

 

[9januerry9]

I must have done something to now suddenly have this hidden file open. In the past, if I opened Excel, the space above the toolbar would simply say Excel. Now it says PERSONAL. If I close it, it says PERSONAL (Autosaved). Then I close it again and it says Excel. How do I get it back to simply being Excel? And I believe you are correct because the formatting macro is in that hidden PERSONAL file so that I can use it whenever and wherever I need it.