Trouble navigating Workbooks/Multiple functions in Sub()

ryanexcelent

New Member
Joined
Nov 2, 2018
Messages
1
I have declared my variable to make sense of the below at the top. However, once the TemplateFile section has run, I cannot get VBA to get back to Account workbook, navigate to the Records spreadsheet and then select the used range to sort them using the control + shift + down method and control + shift + right.

TemplateFile = Application.GetOpenFilename(, , "Please open the workbook that serves as a blank template for missing data files.")


' Open the file dialog
Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker)
diaFolder.Title = "Please select a folder to output the results."
diaFolder.AllowMultiSelect = False
diaFolder.Show


output = diaFolder.SelectedItems(1)


Set diaFolder = Nothing








'this section is to sort the data to ensure the loop as accurate data to loop through


Workbooks("Account").Worksheets("Records").Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Worksheets("Records").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Records").AutoFilter.Sort.SortFields.Add Key:=Range _
("A2:A407"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Records").AutoFilter.Sort.SortFields.Add Key:=Range _
("E2:E407"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Records").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply


End With
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You created the variable 'output' as the file name selected from the file dialog box, but I don't see where you have used it in the code after it is initialized. If you want to activate that workbook, I think
Code:
Workbooks(output).Activate
would probably do it. Then you would need to specify the sheet you want to work with, scince you are using the select and activate method of coding. If you could learn to use direct coding and eliminate all the select and acivate syntax, the code would be much more efficient.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,381
Messages
5,641,823
Members
417,239
Latest member
AymericA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top