Beginner help

Paddy1979

Well-known Member
Joined
Sep 23, 2005
Messages
608
Please can you help me.

I have this code below running of a button , which makes a user open a certain s'sheet but i want the code to return to cell D8 on the orginal s'sheet but i dont know how to do it, as I cannot use the recorder as the filename may vary which will crash the macro?

Sub Max_Stock()

Dim strFileName As Variant

MsgBox ("Please open the INNS148 S'sheet")

'select file
strFileName = Application.GetOpenFilename(Title:="Open INNS148 Report")
If strFileName = False Then
MsgBox "No File Selected"
Exit Sub
End If

'openfile
Workbooks.OpenText Filename:=strFileName, Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
I added lines in your code below that set the original sheetname into a variable you use later to return to that worksheet.
Code:
Sub Max_Stock() 

Dim strFileName As Variant 
    'Set variable of current sheet name
    Set OldActive = ActiveSheet

MsgBox ("Please open the INNS148 S'sheet") 

'select file 
strFileName = Application.GetOpenFilename(Title:="Open INNS148 Report") 
If strFileName = False Then 
MsgBox "No File Selected" 
Exit Sub 
End If 

'openfile 
Workbooks.OpenText Filename:=strFileName, Origin:=xlMSDOS, _ 
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)

    OldActive.Activate  'Return to sheet you came from
End Sub
 

Forum statistics

Threads
1,136,991
Messages
5,678,996
Members
419,797
Latest member
ikethegenius

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