Auto Record Transfer (2)

Freshman · 10-25-2005, 12:05 AM

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

Anne Troy · 10-25-2005, 12:05 AM

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

Freshman · 10-25-2005, 03:05 AM

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
>
>
>

Anne Troy · 10-25-2005, 10:05 AM

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >
>> >
>>
>>
>>

Freshman · 10-25-2005, 10:05 PM

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >> >
> >> >
> >>
> >>
> >>
>
>
>

Anne Troy · 10-25-2005, 10:05 PM

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

Freshman · 10-26-2005, 12:05 AM

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Anne Troy · 10-26-2005, 01:05 AM

Sure... use [email protected]
I haven't had to change it due to too much spam yet, tho I will soon!

************
Anne Troy
www.OfficeArticles.com

"Freshman" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> > 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
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>