referencing a file name and sheet name

[Guest]

how can i reference the file name so that whenever i run this macro in other
file i dont have to go to and change the Worrkbook name and worksheet name. i
want it to recognize it itself because i am going to be usign the macro on
multipe excel file.

here is the code
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range

Set WB = Workbooks("*.xls")
Set SH = WB.Sheets("VT Masterlist") '
Set rng = SH.Range("A:A")


For Each rCell In rng.Cells
With rCell
ipos = InStr(rCell.Value, ".")
If ipos > 0 Then
rCell.Value = Left(rCell.Value, ipos - 1)
End If
End With

Next rCell

End Sub

 

[Dave Peterson]

Can you just run the code against the active sheet?

If yes:

Public Sub Tester()
' Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range

' Set WB = Workbooks("*.xls")
Set SH = activesheet 'WB.Sheets("VT Masterlist")
Set rng = SH.Range("A:A")


For Each rCell In rng.Cells
With rCell
ipos = InStr(rCell.Value, ".")
If ipos > 0 Then
rCell.Value = Left(rCell.Value, ipos - 1)
End If
End With

Next rCell

End Sub

 

[Guest]

The thing is i dont want to write the name of the workbook and worksheet all
the time. i dont know much about activesheet
please explaing what you mean when you say run the code against the
activesheet

 

[Dave Peterson]

The activesheet is the one you're working on at the moment.

 

[Guest]

well how will you make the change in the code i dont know the syntaxx for
that and do i still have to put the workbook name and the worksheet name. i
dont want to do it. i jsut wanna copy paste the code in vb editor and run.
how do i acheive that please help me

 

[Dave Peterson]

The code I included in my first response had the change already made.

well how will you make the change in the code i dont know the syntaxx for
that and do i still have to put the workbook name and the worksheet name. i
dont want to do it. i jsut wanna copy paste the code in vb editor and run.
how do i acheive that please help me

 

[Guest]

' Set WB = Workbooks("*.xls")
Set SH = activesheet 'WB.Sheets("VT Masterlist")
well ("*.xls")is giving me an error saying its not matching
second i did change the sh=wb.activesheet but i still cant give the
workbook the xls name

 

[Dave Peterson]

You changed the code.

I commented that line.
' Set WB = Workbooks("*.xls")
The apostrophe makes the line "invisble" to VBA--it's just there for humans. It
can be a useful technique for documenting your routine or just making the line
"invisible".

' Set WB = Workbooks("*.xls")
Set SH = activesheet 'WB.Sheets("VT Masterlist")
well ("*.xls")is giving me an error saying its not matching
second i did change the sh=wb.activesheet but i still cant give the
workbook the xls name