SOLVED Editing A Macro


Joined
Jan 22, 2017
Messages
20
Reaction score
0
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:D" & 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: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
 
Ad

Advertisements

Joined
Feb 22, 2014
Messages
1,485
Reaction score
297
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?
 
Joined
Jan 22, 2017
Messages
20
Reaction score
0
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.
 
Joined
Feb 22, 2014
Messages
1,485
Reaction score
297
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.

ExcelCalc.png


Code:
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.


.
 
Joined
Jan 22, 2017
Messages
20
Reaction score
0
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?
 
Joined
Feb 22, 2014
Messages
1,485
Reaction score
297
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.
 
Ad

Advertisements

Joined
Jan 22, 2017
Messages
20
Reaction score
0
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.
 
Joined
Feb 22, 2014
Messages
1,485
Reaction score
297
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.
 
Joined
Jan 22, 2017
Messages
20
Reaction score
0
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.
 
Joined
Feb 22, 2014
Messages
1,485
Reaction score
297
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.
 
Joined
Jan 22, 2017
Messages
20
Reaction score
0
I do save it as an .xls file. See attached snip where I save it as a Macro Enabled Workbook.
 

Attachments

  • Save As.JPG
    Save As.JPG
    49.7 KB · Views: 450
Ad

Advertisements

Joined
Feb 22, 2014
Messages
1,485
Reaction score
297
Maybe I'm missing something. Are you able to run the Macro? Because Macro's are not meant to be run automatically.
 
Joined
Jan 22, 2017
Messages
20
Reaction score
0
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.
 
Joined
Feb 22, 2014
Messages
1,485
Reaction score
297
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.
Code:
    Application.Calculation = xlManual
    Application.Calculation = xlAutomatic
Placing one or the other line in your code will either switch the mode to Manual or Automatic.
 
Joined
Jan 22, 2017
Messages
20
Reaction score
0
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.
 
Joined
Jan 22, 2017
Messages
20
Reaction score
0
Wait a minute. Why doesn't it hold that setting. After I close the file and then reopen it, it's back to Manual.
 
Ad

Advertisements

Joined
Jan 22, 2017
Messages
20
Reaction score
0
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?
 
Joined
Feb 22, 2014
Messages
1,485
Reaction score
297
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.
 
Last edited:
Ad

Advertisements

Joined
Jan 22, 2017
Messages
20
Reaction score
0
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top