Simple code help an old man

oldmanwilly

Board Regular
Joined
Feb 24, 2016
Messages
217
HI There

M steps fr my code are:

Code:
Sub GET_IOR_DIAGNOSTIC_DATA()
'OPEN FOLDER
Dim myfile As String

On Error GoTo 100
Dir "I:\Blenheim House\Analytics\North Region Report Library\StEIS\STEIS Tool"

myfile = Application.GetOpenFilename
Workbooks.Open (myfile)
100: Exit Sub

'MSGBOX SELECT FILE

MsgBox "please select your file"


'AFTER OPEN,  SELECT FROM the 3 seperate drop downs: NORTH, STP, AND THEn for the last drop down MATCH THE SELECTION ON my sheet

'GO TO THE CCG SHEET AND COPY
ActiveWorkbook.Sheets("sheetname").Select
Cells("a1:s28").Copy


'CLOSE THE SHEET but keep clipboard
ActiveWorkbook.Close
Application.CutCopyMode = False


'GO TO OUR SHEET AND PASTE
Workbook("myworkbook").Activate
Sheets("my sheet").Select
Cells("a1").Activate
ActiveCell.Paste

'END
I have no idea how to open simple folder the open the selected file, i tried changing it to chdir but it does not help.
thks
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,538
Office Version
2010
Platform
Windows
Can you open the file directly without opening the folder? Something like:

Code:
Dim wb As Workbook

set wb = Workbooks.Open(Filename:=mypath & "\" & myfile)
 

oldmanwilly

Board Regular
Joined
Feb 24, 2016
Messages
217
Can you open the file directly without opening the folder? Something like:

Code:
Dim wb As Workbook

set wb = Workbooks.Open(Filename:=mypath & "\" & myfile)
Hi

the filename changes every month. need to just open the folder any ideas? I also need to know how to change the option froma drop down menu based on a value in my
 

Watch MrExcel Video

Forum statistics

Threads
1,096,192
Messages
5,448,884
Members
405,537
Latest member
muh6323

This Week's Hot Topics

Top