Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
referencing a file name and sheet name
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
referencing a file name and sheet name
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 Arain wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
referencing a file name and sheet name
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" wrote: 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 Arain wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
referencing a file name and sheet name
The activesheet is the one you're working on at the moment.
Arain wrote: 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" wrote: 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 Arain wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
referencing a file name and sheet name
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" wrote: The activesheet is the one you're working on at the moment. Arain wrote: 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" wrote: 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 Arain wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
referencing a file name and sheet name
The code I included in my first response had the change already made.
Arain wrote: 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" wrote: The activesheet is the one you're working on at the moment. Arain wrote: 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" wrote: 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 Arain wrote: 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 -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
referencing a file name and sheet name
' 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" wrote: The code I included in my first response had the change already made. Arain wrote: 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" wrote: The activesheet is the one you're working on at the moment. Arain wrote: 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" wrote: 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 Arain wrote: 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 -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
referencing a file name and sheet name
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". Arain wrote: ' 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" wrote: The code I included in my first response had the change already made. Arain wrote: 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" wrote: The activesheet is the one you're working on at the moment. Arain wrote: 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" wrote: 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 Arain wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|