Hi I hope you can help me. I have made a report that analyses information that extracts from a company template. I have made a macro that copies specify sheet from the template to a hidden sheet on the Report file.

The thing is that the company template could have different names even if the information or the format in it remains the same.

So I need that my macro asks me for the name of the template file and then copies the information from that specified file on to my report.

This is the code I have.
Template will be the name of the variable and total mx 08.xls is the name that I want to change acording to the input name.

Sub Analisis_Bud()
'
' Analisis_bud Macro
' Macro recorded 2/15/2008 for templates version 2008
'

'On Error GoTo Erreur1
Current_File1 = Worksheets("Inst").Cells(21, 2).Value

Windows(Current_File1).Activate
Worksheets("Inst").Select

cont = MsgBox("Is the template file open?", vbYesNo)
If cont = vbNo Then
cont = MsgBox("open the template and try again")
Exit Sub
End If

CurrentFile = Application.InputBox("Input your template name ending with .xls")

If CurrentFile = False Then
Template = MsgBox("Please have your template open and input the name correcly!", vbOKOnly)
If Template = vbOK Then
Exit Sub
End If
End If

'Turn off screen flickering
Application.ScreenUpdating = False

Sheets("Operating Income-Budget").Visible = True
Windows("total mx 08.xls").Activate <- - This is the name of the template that I want it to be variable according to the previous input name

Cells.Select
Selection.AutoFilter
Selection.EntireColumn.Hidden = False
Selection.EntireRow.Hidden = False
Cells.Select
Selection.Copy
Windows("Analisis by PL.xls").Activate
Sheets("Operating Income-Budget").Activate
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Operating Income-Budget").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("inst").Activate

End Sub

MHT

Since CurrentFile is the variable from the InputBox that will store the name given by the user, simply change

Windows("total mx 08.xls").Activate

to

Windows(CurrentFile).Activate

You could change that to be

Windows(CurrentFile & ".xls").Activate

and not have the user enter the .xls. Your choice. You could even make the code more idiot proof by checking to see if the CurrentFile entered by the user ends in .xls and add it if needed.

One more thing I'd like to mention. When you use the recorder, you get a lot of extra code that is not needed. For example if you are sitting in Sheet1 and select Sheet2, then select B3 and enter Fred, the recorder will return this:

Sub Macro1()

' Macro1 Macro
' Macro recorded 2/18/2008 by Me

Sheets("Sheet2").Select
Range("B3").Select
ActiveCell.FormulaR1C1 = "Fred"
End Sub

In reality, all the code you need is:

Sheets("Sheet2").Range("B3") = "Fred"

I mention this because your code will run more efficiently and be easier to read if you eliminate a lot of the .Activate, .Select and other extraneous code that the recorder puts in. The last 15 or so lines of your code could probably be reduced to 5 or 6, maybe even less.

Your great!! thank you I think I understand what my problem was now. I was overlooking the currentfile variable.

Thanks again

MHT