How to streamline this macro
 

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

How to streamline this macro
 

It is seldom necessary nor desirable to .Select object in Excel in order to
work with them. e.g.
Sheets("Råbalance").Cells.ClearContents

Also give yourself some variables to work with and be explicit which WB/WS
you are currently addressing:
Dim SourceWB As Workbook
Set SourceWB=Workbooks.Open (Filename:="J:\1900
overførsler\1901_SPX_right.xls")

With SourceWB
...etc

Not sure about the problem with
Windows("Afd 12. World Hedged 30.06.2007.xls").Activate
as I do not know which WB this is, but you can use ThisWorkbook to refer to
the WB that contain this code, so do not need to know its name.

Also, I would avoid using the Windows collection (unless you really need
some of its properties); use the Workbooks collection instead. But possibly
with above, it will not be necessary anyway.

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

How to streamline this macro
 

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

How to streamline this macro
 

Thanks a lot for your response, Nick

Actually your idea about changing the file path to "ThisWorkbook" was
a great help. This way I don´t have to change the file path everytime.
Thanks for your generel ideas and thoughts. I have a lot to learn
about VBA for sure.

Best Regards
Heine