How to select a specific sheet in multiple files

[DSLA]

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?

 

[stanleydgromjr]

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]

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?]

 

[stanleydgromjr]

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

Private Sub Workbook_Open()

    Sheets("Testsheet").Select

End Sub

Have a great day,
Stan