Not sure if this exactly as you wish, but you get the idea:

Private Sub CommandButton1_Click()
Dim strNameSheet As String
Dim SourceWB As Workbook

strNameSheet = "Råbalance"

With ThisWorkbook 'I assume it is "Afd 12. World Hedged 30.06.2007.xls"
On Error Resume Next
.Worksheets(strNameSheet).Delete
On Error GoTo 0

Set SourceWB = Workbooks.Open(Filename:="J:\1900
overførsler\1901_SPX_right.xls")
SourceWB.Worksheets(1).Copy After:=.Worksheets(.Worksheets.Count)
SourceWB.Close False

With .Worksheets(.Worksheets.Count)
.Name = strNameSheet
.Cells.EntireColumn.AutoFit
With .Columns("C:C")
.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
.NumberFormat = "0"
End With
End With
End With

End Sub

NickHK

"Heine" wrote in message
oups.com...
Hello everybody,

This is my macro.

Dim strNameSheet As String
Dim boolFound As Boolean
Dim MySheets As Worksheet


boolFound = False
strNameSheet = "Råbalance"
For Each MySheets In Worksheets
If MySheets.Name = strNameSheet Then boolFound = True
Next
If boolFound Then
MsgBox ("This sheet already exists")
Else
Sheets.Add
ActiveSheet.Name = strNameSheet
End If



Sheets("Råbalance").Select
Cells.Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select
Workbooks.Open Filename:="J:\1900 overførsler\1901_SPX_right.xls"
Cells.Select
Selection.Copy
Windows("Afd 12. World Hedged 30.06.2007.xls").Activate
Sheets("Råbalance").Select
Range("A1").Select
ActiveSheet.Paste
Cells.Select
Cells.EntireColumn.AutoFit
Columns("C:C").Select
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("C1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Selection.NumberFormat = "0"
Windows("1901_SPX_right.xls").Activate
ActiveWorkbook.Close
Range("A1").Select
End Sub


I use this template in many sheets so I was wondering if there was a
way I could avoid changing the file name everytime in this line:

Windows("Afd 12. World Hedged 30.06.2007.xls").Activate

Can´t I just make a reference to the file I am currently in or
something?

When recorded I change activation between two sheets and that is why I
have to change the name of the file everytime I make a copy of my
sheet.

any thoughts?

best regards
Heine