How to select a specific sheet in multiple files

DSLA

Active Member
Joined
Jun 6, 2005
Messages
296
I have a macro that should open all the files in a specific folder, and then select a specific sheet from it and leave it so that when the file is opened, the sheet is opened first.

Macro is here, i tried it and it goes without any warnings but the files doesn't have the wanted sheet first when i open them.

Dim fso, f, fl, fc, sPath As String
Set fso = CreateObject("Scripting.FileSystemObject")

sPath = "C:\Test\"

Set f = fso.GetFolder(sPath)
Set fc = f.Files

For Each fl In fc
Workbooks.Open sPath & fl.Name
With Workbooks(fl.Name)
.Sheets("Testsheet").Select
.Close (True)
End With
Next fl


Help anyone?
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Joined
Jul 30, 2006
Messages
3,656
DSLA,

After you open the file and make the 'Testsheet' active,you will have to save it, and then close it.

Next time you open it, the active sheet should be 'Testsheet'.

Have a great day,
Stan
 

DSLA

Active Member
Joined
Jun 6, 2005
Messages
296
DSLA,

After you open the file and make the 'Testsheet' active,you will have to save it, and then close it.

Next time you open it, the active sheet should be 'Testsheet'.

Have a great day,
Stan

Isn't this the code?:

.Sheets("Testsheet").Select
.Close (True) [<--this is just a shorted version of SaveChange=True, atleast i think?]
 
Joined
Jul 30, 2006
Messages
3,656
DSLA,

I am not sure about your code.

But, you could open each of the files in the folder and past the below code into the (see below). And, then save it, and close it. Each time the files are opened, the WorkBook_Open Event will select the sheetname 'Testsheet'.


Press and hold down the 'ALT' key, and press the 'F11' key.

Copy the below code, and paste it into VBAProject, Microsoft Excel Objects, ThisWorkbook


Code:
Private Sub Workbook_Open()

    Sheets("Testsheet").Select

End Sub

Have a great day,
Stan
 

Watch MrExcel Video

Forum statistics

Threads
1,122,577
Messages
5,596,996
Members
414,116
Latest member
sfullnet

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