| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Auto Record Transfer (2)
Dear experts/Bernie Deitrick
Bernie wrote me the code below for transferring records from one sheet to another and it works perfectly well in my PC. However, when I put the file into a common network drive and share with other users inside my Company, the code only work in my PC and not in other users' PCs. Is the code below has some restriction to my PC only? If yes, how to modify the code so that the file can be used by other users. Please advise and thanks in advance. Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range On Error GoTo ErrorHandler Application.EnableEvents = False If Target.Column = 4 And Target(1).Value = "Y" Then Dim eRow As Long eRow = Sheets("Completed").Cells(Rows.Count, 1).End(xlUp).Row + 1 For Each myCell In Target myCell.Offset(0, -3).Resize(, 4).Copy _ Sheets("Completed").Cells(eRow, 1) eRow = eRow + 1 Next myCell Target.EntireRow.Delete End If ErrorHandler: Application.EnableEvents = True End Sub |
|
#2
|
|||
|
|||
|
Auto Record Transfer (2)
Where did you store the code, Freshman? And/or what happens when you run the
code on other PCs? Do the other PCs have macros enabled? ************ Anne Troy www.OfficeArticles.com "Freshman" wrote in message ... Dear experts/Bernie Deitrick Bernie wrote me the code below for transferring records from one sheet to another and it works perfectly well in my PC. However, when I put the file into a common network drive and share with other users inside my Company, the code only work in my PC and not in other users' PCs. Is the code below has some restriction to my PC only? If yes, how to modify the code so that the file can be used by other users. Please advise and thanks in advance. Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range On Error GoTo ErrorHandler Application.EnableEvents = False If Target.Column = 4 And Target(1).Value = "Y" Then Dim eRow As Long eRow = Sheets("Completed").Cells(Rows.Count, 1).End(xlUp).Row + 1 For Each myCell In Target myCell.Offset(0, -3).Resize(, 4).Copy _ Sheets("Completed").Cells(eRow, 1) eRow = eRow + 1 Next myCell Target.EntireRow.Delete End If ErrorHandler: Application.EnableEvents = True End Sub |
|
#3
|
|||
|
|||
|
Auto Record Transfer (2)
Hi Anne,
I store the code in the worksheet of VBE(right click the sheet tab and choose "view code"). When other users typed "Y" on the same worksheet, nothing happened and the code seems not running. Please kindly advise your meaning of enabling the macros. How? Is it press F5 to run the code. However, I cannot see the macro name in the "Marco" dialogue box. Please kindly advise what's wrong. Sorry for my limited knowledge to VBA (though I'm try my best to learn right now). Thanks. "Anne Troy" wrote: Where did you store the code, Freshman? And/or what happens when you run the code on other PCs? Do the other PCs have macros enabled? ************ Anne Troy www.OfficeArticles.com "Freshman" wrote in message ... Dear experts/Bernie Deitrick Bernie wrote me the code below for transferring records from one sheet to another and it works perfectly well in my PC. However, when I put the file into a common network drive and share with other users inside my Company, the code only work in my PC and not in other users' PCs. Is the code below has some restriction to my PC only? If yes, how to modify the code so that the file can be used by other users. Please advise and thanks in advance. Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range On Error GoTo ErrorHandler Application.EnableEvents = False If Target.Column = 4 And Target(1).Value = "Y" Then Dim eRow As Long eRow = Sheets("Completed").Cells(Rows.Count, 1).End(xlUp).Row + 1 For Each myCell In Target myCell.Offset(0, -3).Resize(, 4).Copy _ Sheets("Completed").Cells(eRow, 1) eRow = eRow + 1 Next myCell Target.EntireRow.Delete End If ErrorHandler: Application.EnableEvents = True End Sub |
|
#4
|
|||
|
|||
|
Auto Record Transfer (2)
Sorry. Dumb question, Freshman. Which workbook did you store it in? If it's
in your personal.xls file, then you're not really sending your code to the others. If macro security is set to medium (Tools--Macro--Security) and you open a workbook that contains macros, you are asked if you want to enable macros. But if security is set higher than medium, you are not asked at all--and macros aren't enabled. Check the settings of the other users. ************ Anne Troy www.OfficeArticles.com "Freshman" wrote in message ... Hi Anne, I store the code in the worksheet of VBE(right click the sheet tab and choose "view code"). When other users typed "Y" on the same worksheet, nothing happened and the code seems not running. Please kindly advise your meaning of enabling the macros. How? Is it press F5 to run the code. However, I cannot see the macro name in the "Marco" dialogue box. Please kindly advise what's wrong. Sorry for my limited knowledge to VBA (though I'm try my best to learn right now). Thanks. "Anne Troy" wrote: Where did you store the code, Freshman? And/or what happens when you run the code on other PCs? Do the other PCs have macros enabled? ************ Anne Troy www.OfficeArticles.com "Freshman" wrote in message ... Dear experts/Bernie Deitrick Bernie wrote me the code below for transferring records from one sheet to another and it works perfectly well in my PC. However, when I put the file into a common network drive and share with other users inside my Company, the code only work in my PC and not in other users' PCs. Is the code below has some restriction to my PC only? If yes, how to modify the code so that the file can be used by other users. Please advise and thanks in advance. Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range On Error GoTo ErrorHandler Application.EnableEvents = False If Target.Column = 4 And Target(1).Value = "Y" Then Dim eRow As Long eRow = Sheets("Completed").Cells(Rows.Count, 1).End(xlUp).Row + 1 For Each myCell In Target myCell.Offset(0, -3).Resize(, 4).Copy _ Sheets("Completed").Cells(eRow, 1) eRow = eRow + 1 Next myCell Target.EntireRow.Delete End If ErrorHandler: Application.EnableEvents = True End Sub |
|
#5
|
|||
|
|||
|
Auto Record Transfer (2)
Hi Anne,
The code is stored in a workbook other than personal.xls file. I've checked other users' setting and the marco security is medium only. Thanks. "Anne Troy" wrote: Sorry. Dumb question, Freshman. Which workbook did you store it in? If it's in your personal.xls file, then you're not really sending your code to the others. If macro security is set to medium (Tools--Macro--Security) and you open a workbook that contains macros, you are asked if you want to enable macros. But if security is set higher than medium, you are not asked at all--and macros aren't enabled. Check the settings of the other users. ************ Anne Troy www.OfficeArticles.com "Freshman" wrote in message ... Hi Anne, I store the code in the worksheet of VBE(right click the sheet tab and choose "view code"). When other users typed "Y" on the same worksheet, nothing happened and the code seems not running. Please kindly advise your meaning of enabling the macros. How? Is it press F5 to run the code. However, I cannot see the macro name in the "Marco" dialogue box. Please kindly advise what's wrong. Sorry for my limited knowledge to VBA (though I'm try my best to learn right now). Thanks. "Anne Troy" wrote: Where did you store the code, Freshman? And/or what happens when you run the code on other PCs? Do the other PCs have macros enabled? ************ Anne Troy www.OfficeArticles.com "Freshman" wrote in message ... Dear experts/Bernie Deitrick Bernie wrote me the code below for transferring records from one sheet to another and it works perfectly well in my PC. However, when I put the file into a common network drive and share with other users inside my Company, the code only work in my PC and not in other users' PCs. Is the code below has some restriction to my PC only? If yes, how to modify the code so that the file can be used by other users. Please advise and thanks in advance. Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range On Error GoTo ErrorHandler Application.EnableEvents = False If Target.Column = 4 And Target(1).Value = "Y" Then Dim eRow As Long eRow = Sheets("Completed").Cells(Rows.Count, 1).End(xlUp).Row + 1 For Each myCell In Target myCell.Offset(0, -3).Resize(, 4).Copy _ Sheets("Completed").Cells(eRow, 1) eRow = eRow + 1 Next myCell Target.EntireRow.Delete End If ErrorHandler: Application.EnableEvents = True End Sub |
|
#6
|
|||
|
|||
|
Auto Record Transfer (2)
This just gets weirder, guy. I don't know what to tell you. Any way I can
see the workbook to test here? ************ Anne Troy www.OfficeArticles.com "Freshman" wrote in message ... Hi Anne, The code is stored in a workbook other than personal.xls file. I've checked other users' setting and the marco security is medium only. Thanks. "Anne Troy" wrote: Sorry. Dumb question, Freshman. Which workbook did you store it in? If it's in your personal.xls file, then you're not really sending your code to the others. If macro security is set to medium (Tools--Macro--Security) and you open a workbook that contains macros, you are asked if you want to enable macros. But if security is set higher than medium, you are not asked at all--and macros aren't enabled. Check the settings of the other users. ************ Anne Troy www.OfficeArticles.com "Freshman" wrote in message ... Hi Anne, I store the code in the worksheet of VBE(right click the sheet tab and choose "view code"). When other users typed "Y" on the same worksheet, nothing happened and the code seems not running. Please kindly advise your meaning of enabling the macros. How? Is it press F5 to run the code. However, I cannot see the macro name in the "Marco" dialogue box. Please kindly advise what's wrong. Sorry for my limited knowledge to VBA (though I'm try my best to learn right now). Thanks. "Anne Troy" wrote: Where did you store the code, Freshman? And/or what happens when you run the code on other PCs? Do the other PCs have macros enabled? ************ Anne Troy www.OfficeArticles.com "Freshman" wrote in message ... Dear experts/Bernie Deitrick Bernie wrote me the code below for transferring records from one sheet to another and it works perfectly well in my PC. However, when I put the file into a common network drive and share with other users inside my Company, the code only work in my PC and not in other users' PCs. Is the code below has some restriction to my PC only? If yes, how to modify the code so that the file can be used by other users. Please advise and thanks in advance. Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range On Error GoTo ErrorHandler Application.EnableEvents = False If Target.Column = 4 And Target(1).Value = "Y" Then Dim eRow As Long eRow = Sheets("Completed").Cells(Rows.Count, 1).End(xlUp).Row + 1 For Each myCell In Target myCell.Offset(0, -3).Resize(, 4).Copy _ Sheets("Completed").Cells(eRow, 1) eRow = eRow + 1 Next myCell Target.EntireRow.Delete End If ErrorHandler: Application.EnableEvents = True End Sub |
|
#7
|
|||
|
|||
|
Auto Record Transfer (2)
Hi Anne,
Can I have your e-mail address so that I can forward the file to you? Best regards. "Anne Troy" wrote: This just gets weirder, guy. I don't know what to tell you. Any way I can see the workbook to test here? ************ Anne Troy www.OfficeArticles.com "Freshman" wrote in message ... Hi Anne, The code is stored in a workbook other than personal.xls file. I've checked other users' setting and the marco security is medium only. Thanks. "Anne Troy" wrote: Sorry. Dumb question, Freshman. Which workbook did you store it in? If it's in your personal.xls file, then you're not really sending your code to the others. If macro security is set to medium (Tools--Macro--Security) and you open a workbook that contains macros, you are asked if you want to enable macros. But if security is set higher than medium, you are not asked at all--and macros aren't enabled. Check the settings of the other users. ************ Anne Troy www.OfficeArticles.com "Freshman" wrote in message ... Hi Anne, I store the code in the worksheet of VBE(right click the sheet tab and choose "view code"). When other users typed "Y" on the same worksheet, nothing happened and the code seems not running. Please kindly advise your meaning of enabling the macros. How? Is it press F5 to run the code. However, I cannot see the macro name in the "Marco" dialogue box. Please kindly advise what's wrong. Sorry for my limited knowledge to VBA (though I'm try my best to learn right now). Thanks. "Anne Troy" wrote: Where did you store the code, Freshman? And/or what happens when you run the code on other PCs? Do the other PCs have macros enabled? ************ Anne Troy www.OfficeArticles.com "Freshman" wrote in message ... Dear experts/Bernie Deitrick Bernie wrote me the code below for transferring records from one sheet to another and it works perfectly well in my PC. However, when I put the file into a common network drive and share with other users inside my Company, the code only work in my PC and not in other users' PCs. Is the code below has some restriction to my PC only? If yes, how to modify the code so that the file can be used by other users. Please advise and thanks in advance. Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range On Error GoTo ErrorHandler Application.EnableEvents = False If Target.Column = 4 And Target(1).Value = "Y" Then Dim eRow As Long eRow = Sheets("Completed").Cells(Rows.Count, 1).End(xlUp).Row + 1 For Each myCell In Target myCell.Offset(0, -3).Resize(, 4).Copy _ Sheets("Completed").Cells(eRow, 1) eRow = eRow + 1 Next myCell Target.EntireRow.Delete End If ErrorHandler: Application.EnableEvents = True End Sub |
|
#8
|
|||
|
|||
|
Auto Record Transfer (2)
Sure... use
I haven't had to change it due to too much spam yet, tho I will soon! :) ************ Anne Troy www.OfficeArticles.com "Freshman" wrote in message ... Hi Anne, Can I have your e-mail address so that I can forward the file to you? Best regards. "Anne Troy" wrote: This just gets weirder, guy. I don't know what to tell you. Any way I can see the workbook to test here? ************ Anne Troy www.OfficeArticles.com "Freshman" wrote in message ... Hi Anne, The code is stored in a workbook other than personal.xls file. I've checked other users' setting and the marco security is medium only. Thanks. "Anne Troy" wrote: Sorry. Dumb question, Freshman. Which workbook did you store it in? If it's in your personal.xls file, then you're not really sending your code to the others. If macro security is set to medium (Tools--Macro--Security) and you open a workbook that contains macros, you are asked if you want to enable macros. But if security is set higher than medium, you are not asked at all--and macros aren't enabled. Check the settings of the other users. ************ Anne Troy www.OfficeArticles.com "Freshman" wrote in message ... Hi Anne, I store the code in the worksheet of VBE(right click the sheet tab and choose "view code"). When other users typed "Y" on the same worksheet, nothing happened and the code seems not running. Please kindly advise your meaning of enabling the macros. How? Is it press F5 to run the code. However, I cannot see the macro name in the "Marco" dialogue box. Please kindly advise what's wrong. Sorry for my limited knowledge to VBA (though I'm try my best to learn right now). Thanks. "Anne Troy" wrote: Where did you store the code, Freshman? And/or what happens when you run the code on other PCs? Do the other PCs have macros enabled? ************ Anne Troy www.OfficeArticles.com "Freshman" wrote in message ... Dear experts/Bernie Deitrick Bernie wrote me the code below for transferring records from one sheet to another and it works perfectly well in my PC. However, when I put the file into a common network drive and share with other users inside my Company, the code only work in my PC and not in other users' PCs. Is the code below has some restriction to my PC only? If yes, how to modify the code so that the file can be used by other users. Please advise and thanks in advance. Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range On Error GoTo ErrorHandler Application.EnableEvents = False If Target.Column = 4 And Target(1).Value = "Y" Then Dim eRow As Long eRow = Sheets("Completed").Cells(Rows.Count, 1).End(xlUp).Row + 1 For Each myCell In Target myCell.Offset(0, -3).Resize(, 4).Copy _ Sheets("Completed").Cells(eRow, 1) eRow = eRow + 1 Next myCell Target.EntireRow.Delete End If ErrorHandler: Application.EnableEvents = True End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Auto Record Transfer | Excel Worksheet Functions | |||
| Can't go to 'Last Record' in one step in a Word doc. linked To Exc | Excel Discussion (Misc queries) | |||
| Auto Data Transfer | Excel Worksheet Functions | |||
| Help - now really stuck! File transfer problem | Excel Discussion (Misc queries) | |||
| excel links update not working in auto, calculations in auto | Excel Worksheet Functions | |||
Linear Mode
