Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > error with code
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 141
Default error with code
Hi,

I have a code but I get an error because of another piece of code I have in
the workbook, here is the code I am using just now:

Sub ApplyStock()
Dim strPrompt As String
Dim intbuttons As Integer
Dim strTitle As String
strPrompt = "WARNING!! This action will reset all stock cards and apply
new stock numbers" & vbNewLine & "" & vbNewLine & "The screen will flicker
for a period" & vbNewLine & "" & vbNewLine & "This action cannot be undone
are you sure you want to continue?"
intbuttons = vbYesNo + vbInformation
strTitle = "Stock Maintenance © M Neil "
If MsgBox(strPrompt, intbuttons, strTitle) = vbYes Then
With Sheets("Stock Maintenance")
Range("C3").Copy Sheet4.Range("B7")
Range("C4").Copy Sheet5.Range("B7")
Range("C5").Copy Sheet6.Range("B7")
Range("C6").Copy Sheet7.Range("B7")
Range("C7").Copy Sheet8.Range("B7")
Range("C8").Copy Sheet9.Range("B7")
Range("C9").Copy Sheet10.Range("B7")
Range("C10").Copy Sheet11.Range("B7")
Range("C11").Copy Sheet12.Range("B7")
Range("C12").Copy Sheet13.Range("B7")
Range("C13").Copy Sheet14.Range("B7")
Range("C14").Copy Sheet15.Range("B7")
Range("C15").Copy Sheet16.Range("B7")
Range("C16").Copy Sheet17.Range("B7")
Range("C17").Copy Sheet18.Range("B7")
Range("C18").Copy Sheet19.Range("B7")
Range("C19").Copy Sheet20.Range("B7")
Range("C20").Copy Sheet21.Range("B7")
Range("C21").Copy Sheet22.Range("B7")
Range("C22").Copy Sheet23.Range("B7")
Range("C23").Copy Sheet24.Range("B7")
Range("C24").Copy Sheet25.Range("B7")
Range("C25").Copy Sheet26.Range("B7")
Range("C26").Copy Sheet27.Range("B7")
Range("C27").Copy Sheet28.Range("B7")
Range("C28").Copy Sheet29.Range("B7")
Range("C29").Copy Sheet30.Range("B7")
Range("C30").Copy Sheet31.Range("B7")
Range("C31").Copy Sheet32.Range("B7")
Range("C32").Copy Sheet33.Range("B7")
Range("C33").Copy Sheet34.Range("B7")
Range("C34").Copy Sheet35.Range("B7")
Range("C35").Copy Sheet36.Range("B7")
Range("C36").Copy Sheet37.Range("B7")
Range("C37").Copy Sheet38.Range("B7")
Range("C38").Copy Sheet39.Range("B7")
Range("C39").Copy Sheet40.Range("B7")
Range("C40").Copy Sheet41.Range("B7")
Range("C41").Copy Sheet42.Range("B7")
Range("C42").Copy Sheet43.Range("B7")
Range("C43").Copy Sheet44.Range("B7")
Range("C44").Copy Sheet45.Range("B7")
Range("C45").Copy Sheet46.Range("B7")
Range("C46").Copy Sheet47.Range("B7")
Range("C47").Copy Sheet48.Range("B7")
Range("C48").Copy Sheet49.Range("B7")
Range("C49").Copy Sheet50.Range("B7")
Range("C50").Copy Sheet51.Range("B7")
Range("C51").Copy Sheet52.Range("B7")
Range("C52").Copy Sheet53.Range("B7")
Range("F3").Copy Sheet54.Range("B7")
Range("F4").Copy Sheet55.Range("B7")
Range("F5").Copy Sheet56.Range("B7")
Range("F6").Copy Sheet57.Range("B7")
Range("F7").Copy Sheet58.Range("B7")
Range("F8").Copy Sheet59.Range("B7")
Range("F9").Copy Sheet60.Range("B7")
Range("F10").Copy Sheet61.Range("B7")
Range("F11").Copy Sheet62.Range("B7")
Range("F12").Copy Sheet63.Range("B7")
Range("F13").Copy Sheet64.Range("B7")
Range("F14").Copy Sheet65.Range("B7")
Range("F15").Copy Sheet66.Range("B7")
Range("F16").Copy Sheet67.Range("B7")
Range("F17").Copy Sheet68.Range("B7")
Range("F18").Copy Sheet69.Range("B7")
Range("F19").Copy Sheet70.Range("B7")
Range("F20").Copy Sheet71.Range("B7")
Range("F21").Copy Sheet72.Range("B7")
Range("F22").Copy Sheet73.Range("B7")
Range("F23").Copy Sheet74.Range("B7")
Range("F24").Copy Sheet75.Range("B7")
Range("F25").Copy Sheet76.Range("B7")
Range("F26").Copy Sheet77.Range("B7")
Range("F27").Copy Sheet78.Range("B7")
Range("F28").Copy Sheet79.Range("B7")
Range("F29").Copy Sheet80.Range("B7")
Range("F30").Copy Sheet81.Range("B7")
Range("F31").Copy Sheet82.Range("B7")
Range("F32").Copy Sheet83.Range("B7")
Range("F33").Copy Sheet84.Range("B7")
Range("F34").Copy Sheet85.Range("B7")
Range("F35").Copy Sheet86.Range("B7")
Range("F36").Copy Sheet87.Range("B7")
Range("F37").Copy Sheet88.Range("B7")
Range("F38").Copy Sheet89.Range("B7")
Range("F39").Copy Sheet90.Range("B7")
Range("F40").Copy Sheet91.Range("B7")
Range("F41").Copy Sheet92.Range("B7")
Range("F42").Copy Sheet93.Range("B7")
Range("F43").Copy Sheet94.Range("B7")
Range("F44").Copy Sheet95.Range("B7")
Range("F45").Copy Sheet96.Range("B7")
Range("F46").Copy Sheet97.Range("B7")
Range("F47").Copy Sheet98.Range("B7")
Range("F48").Copy Sheet99.Range("B7")
Range("F49").Copy Sheet100.Range("B7")
Range("F50").Copy Sheet101.Range("B7")
Range("F51").Copy Sheet102.Range("B7")
Range("F52").Copy Sheet103.Range("B7")
Range("A1:A2").Select
End With
MsgBox "New stock numbers aplied"
End If
End Sub

Here is the code I get an error with, is there a work around for this?

The error is line If Intersect(t, d) Is Nothing Then Exit Sub (this
highlighted yellow)

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
Set t = Target
Set d = Range("D7:D36")
If Intersect(t, d) Is Nothing Then Exit Sub
If t.Value = "" Then Exit Sub
Sheets(1).Activate
Range("A1").Select
End Sub

Many thanks for your help

Mark

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,939
Default error with code
You can turn off events which will stop the SheetChange code from firing. I
would do it something like this

Sub ApplyStock()
Dim strPrompt As String
Dim intbuttons As Integer
Dim strTitle As String

on error goto ErrorHandler
strPrompt = "WARNING!! This action will reset all stock cards and apply
new stock numbers" & vbNewLine & "" & vbNewLine & "This action cannot be
undone
are you sure you want to continue?"
intbuttons = vbYesNo + vbInformation
strTitle = "Stock Maintenance © M Neil "
If MsgBox(strPrompt, intbuttons, strTitle) = vbYes Then

Application.screenupdating = false
Application.enableevents = false
With Sheets("Stock Maintenance")
..Range("C3").Copy Sheet4.Range("B7")
..Range("C4").Copy Sheet5.Range("B7")
..Range("C5").Copy Sheet6.Range("B7")
..Range("C6").Copy Sheet7.Range("B7")
..Range("C7").Copy Sheet8.Range("B7")
..Range("C8").Copy Sheet9.Range("B7")
..Range("C9").Copy Sheet10.Range("B7")
..Range("C10").Copy Sheet11.Range("B7")
..Range("C11").Copy Sheet12.Range("B7")
..Range("C12").Copy Sheet13.Range("B7")
..Range("C13").Copy Sheet14.Range("B7")
..Range("C14").Copy Sheet15.Range("B7")
..Range("C15").Copy Sheet16.Range("B7")
..Range("C16").Copy Sheet17.Range("B7")
..Range("C17").Copy Sheet18.Range("B7")
..Range("C18").Copy Sheet19.Range("B7")
..Range("C19").Copy Sheet20.Range("B7")
..Range("C20").Copy Sheet21.Range("B7")
..Range("C21").Copy Sheet22.Range("B7")
..Range("C22").Copy Sheet23.Range("B7")
..Range("C23").Copy Sheet24.Range("B7")
..Range("C24").Copy Sheet25.Range("B7")
..Range("C25").Copy Sheet26.Range("B7")
..Range("C26").Copy Sheet27.Range("B7")
..Range("C27").Copy Sheet28.Range("B7")
..Range("C28").Copy Sheet29.Range("B7")
..Range("C29").Copy Sheet30.Range("B7")
..Range("C30").Copy Sheet31.Range("B7")
..Range("C31").Copy Sheet32.Range("B7")
..Range("C32").Copy Sheet33.Range("B7")
..Range("C33").Copy Sheet34.Range("B7")
..Range("C34").Copy Sheet35.Range("B7")
..Range("C35").Copy Sheet36.Range("B7")
..Range("C36").Copy Sheet37.Range("B7")
..Range("C37").Copy Sheet38.Range("B7")
..Range("C38").Copy Sheet39.Range("B7")
..Range("C39").Copy Sheet40.Range("B7")
..Range("C40").Copy Sheet41.Range("B7")
..Range("C41").Copy Sheet42.Range("B7")
..Range("C42").Copy Sheet43.Range("B7")
..Range("C43").Copy Sheet44.Range("B7")
..Range("C44").Copy Sheet45.Range("B7")
..Range("C45").Copy Sheet46.Range("B7")
..Range("C46").Copy Sheet47.Range("B7")
..Range("C47").Copy Sheet48.Range("B7")
..Range("C48").Copy Sheet49.Range("B7")
..Range("C49").Copy Sheet50.Range("B7")
..Range("C50").Copy Sheet51.Range("B7")
..Range("C51").Copy Sheet52.Range("B7")
..Range("C52").Copy Sheet53.Range("B7")
..Range("F3").Copy Sheet54.Range("B7")
..Range("F4").Copy Sheet55.Range("B7")
..Range("F5").Copy Sheet56.Range("B7")
..Range("F6").Copy Sheet57.Range("B7")
..Range("F7").Copy Sheet58.Range("B7")
..Range("F8").Copy Sheet59.Range("B7")
..Range("F9").Copy Sheet60.Range("B7")
..Range("F10").Copy Sheet61.Range("B7")
..Range("F11").Copy Sheet62.Range("B7")
..Range("F12").Copy Sheet63.Range("B7")
..Range("F13").Copy Sheet64.Range("B7")
..Range("F14").Copy Sheet65.Range("B7")
..Range("F15").Copy Sheet66.Range("B7")
..Range("F16").Copy Sheet67.Range("B7")
..Range("F17").Copy Sheet68.Range("B7")
..Range("F18").Copy Sheet69.Range("B7")
..Range("F19").Copy Sheet70.Range("B7")
..Range("F20").Copy Sheet71.Range("B7")
..Range("F21").Copy Sheet72.Range("B7")
..Range("F22").Copy Sheet73.Range("B7")
..Range("F23").Copy Sheet74.Range("B7")
..Range("F24").Copy Sheet75.Range("B7")
..Range("F25").Copy Sheet76.Range("B7")
..Range("F26").Copy Sheet77.Range("B7")
..Range("F27").Copy Sheet78.Range("B7")
..Range("F28").Copy Sheet79.Range("B7")
..Range("F29").Copy Sheet80.Range("B7")
..Range("F30").Copy Sheet81.Range("B7")
..Range("F31").Copy Sheet82.Range("B7")
..Range("F32").Copy Sheet83.Range("B7")
..Range("F33").Copy Sheet84.Range("B7")
..Range("F34").Copy Sheet85.Range("B7")
..Range("F35").Copy Sheet86.Range("B7")
..Range("F36").Copy Sheet87.Range("B7")
..Range("F37").Copy Sheet88.Range("B7")
..Range("F38").Copy Sheet89.Range("B7")
..Range("F39").Copy Sheet90.Range("B7")
..Range("F40").Copy Sheet91.Range("B7")
..Range("F41").Copy Sheet92.Range("B7")
..Range("F42").Copy Sheet93.Range("B7")
..Range("F43").Copy Sheet94.Range("B7")
..Range("F44").Copy Sheet95.Range("B7")
..Range("F45").Copy Sheet96.Range("B7")
..Range("F46").Copy Sheet97.Range("B7")
..Range("F47").Copy Sheet98.Range("B7")
..Range("F48").Copy Sheet99.Range("B7")
..Range("F49").Copy Sheet100.Range("B7")
..Range("F50").Copy Sheet101.Range("B7")
..Range("F51").Copy Sheet102.Range("B7")
..Range("F52").Copy Sheet103.Range("B7")
..Range("A1:A2").Select
End With
MsgBox "New stock numbers aplied"
End If

ErrorHandler:
Application.enableevents = true
Application.screenupdating = true
End Sub


--
HTH...

Jim Thomlinson


"terilad" wrote:

Hi,

I have a code but I get an error because of another piece of code I have in
the workbook, here is the code I am using just now:

Sub ApplyStock()
Dim strPrompt As String
Dim intbuttons As Integer
Dim strTitle As String
strPrompt = "WARNING!! This action will reset all stock cards and apply
new stock numbers" & vbNewLine & "" & vbNewLine & "The screen will flicker
for a period" & vbNewLine & "" & vbNewLine & "This action cannot be undone
are you sure you want to continue?"
intbuttons = vbYesNo + vbInformation
strTitle = "Stock Maintenance © M Neil "
If MsgBox(strPrompt, intbuttons, strTitle) = vbYes Then
With Sheets("Stock Maintenance")
Range("C3").Copy Sheet4.Range("B7")
Range("C4").Copy Sheet5.Range("B7")
Range("C5").Copy Sheet6.Range("B7")
Range("C6").Copy Sheet7.Range("B7")
Range("C7").Copy Sheet8.Range("B7")
Range("C8").Copy Sheet9.Range("B7")
Range("C9").Copy Sheet10.Range("B7")
Range("C10").Copy Sheet11.Range("B7")
Range("C11").Copy Sheet12.Range("B7")
Range("C12").Copy Sheet13.Range("B7")
Range("C13").Copy Sheet14.Range("B7")
Range("C14").Copy Sheet15.Range("B7")
Range("C15").Copy Sheet16.Range("B7")
Range("C16").Copy Sheet17.Range("B7")
Range("C17").Copy Sheet18.Range("B7")
Range("C18").Copy Sheet19.Range("B7")
Range("C19").Copy Sheet20.Range("B7")
Range("C20").Copy Sheet21.Range("B7")
Range("C21").Copy Sheet22.Range("B7")
Range("C22").Copy Sheet23.Range("B7")
Range("C23").Copy Sheet24.Range("B7")
Range("C24").Copy Sheet25.Range("B7")
Range("C25").Copy Sheet26.Range("B7")
Range("C26").Copy Sheet27.Range("B7")
Range("C27").Copy Sheet28.Range("B7")
Range("C28").Copy Sheet29.Range("B7")
Range("C29").Copy Sheet30.Range("B7")
Range("C30").Copy Sheet31.Range("B7")
Range("C31").Copy Sheet32.Range("B7")
Range("C32").Copy Sheet33.Range("B7")
Range("C33").Copy Sheet34.Range("B7")
Range("C34").Copy Sheet35.Range("B7")
Range("C35").Copy Sheet36.Range("B7")
Range("C36").Copy Sheet37.Range("B7")
Range("C37").Copy Sheet38.Range("B7")
Range("C38").Copy Sheet39.Range("B7")
Range("C39").Copy Sheet40.Range("B7")
Range("C40").Copy Sheet41.Range("B7")
Range("C41").Copy Sheet42.Range("B7")
Range("C42").Copy Sheet43.Range("B7")
Range("C43").Copy Sheet44.Range("B7")
Range("C44").Copy Sheet45.Range("B7")
Range("C45").Copy Sheet46.Range("B7")
Range("C46").Copy Sheet47.Range("B7")
Range("C47").Copy Sheet48.Range("B7")
Range("C48").Copy Sheet49.Range("B7")
Range("C49").Copy Sheet50.Range("B7")
Range("C50").Copy Sheet51.Range("B7")
Range("C51").Copy Sheet52.Range("B7")
Range("C52").Copy Sheet53.Range("B7")
Range("F3").Copy Sheet54.Range("B7")
Range("F4").Copy Sheet55.Range("B7")
Range("F5").Copy Sheet56.Range("B7")
Range("F6").Copy Sheet57.Range("B7")
Range("F7").Copy Sheet58.Range("B7")
Range("F8").Copy Sheet59.Range("B7")
Range("F9").Copy Sheet60.Range("B7")
Range("F10").Copy Sheet61.Range("B7")
Range("F11").Copy Sheet62.Range("B7")
Range("F12").Copy Sheet63.Range("B7")
Range("F13").Copy Sheet64.Range("B7")
Range("F14").Copy Sheet65.Range("B7")
Range("F15").Copy Sheet66.Range("B7")
Range("F16").Copy Sheet67.Range("B7")
Range("F17").Copy Sheet68.Range("B7")
Range("F18").Copy Sheet69.Range("B7")
Range("F19").Copy Sheet70.Range("B7")
Range("F20").Copy Sheet71.Range("B7")
Range("F21").Copy Sheet72.Range("B7")
Range("F22").Copy Sheet73.Range("B7")
Range("F23").Copy Sheet74.Range("B7")
Range("F24").Copy Sheet75.Range("B7")
Range("F25").Copy Sheet76.Range("B7")
Range("F26").Copy Sheet77.Range("B7")
Range("F27").Copy Sheet78.Range("B7")
Range("F28").Copy Sheet79.Range("B7")
Range("F29").Copy Sheet80.Range("B7")
Range("F30").Copy Sheet81.Range("B7")
Range("F31").Copy Sheet82.Range("B7")
Range("F32").Copy Sheet83.Range("B7")
Range("F33").Copy Sheet84.Range("B7")
Range("F34").Copy Sheet85.Range("B7")
Range("F35").Copy Sheet86.Range("B7")
Range("F36").Copy Sheet87.Range("B7")
Range("F37").Copy Sheet88.Range("B7")
Range("F38").Copy Sheet89.Range("B7")
Range("F39").Copy Sheet90.Range("B7")
Range("F40").Copy Sheet91.Range("B7")
Range("F41").Copy Sheet92.Range("B7")
Range("F42").Copy Sheet93.Range("B7")
Range("F43").Copy Sheet94.Range("B7")
Range("F44").Copy Sheet95.Range("B7")
Range("F45").Copy Sheet96.Range("B7")
Range("F46").Copy Sheet97.Range("B7")
Range("F47").Copy Sheet98.Range("B7")
Range("F48").Copy Sheet99.Range("B7")
Range("F49").Copy Sheet100.Range("B7")
Range("F50").Copy Sheet101.Range("B7")
Range("F51").Copy Sheet102.Range("B7")
Range("F52").Copy Sheet103.Range("B7")
Range("A1:A2").Select
End With
MsgBox "New stock numbers aplied"
End If
End Sub

Here is the code I get an error with, is there a work around for this?

The error is line If Intersect(t, d) Is Nothing Then Exit Sub (this
highlighted yellow)

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
Set t = Target
Set d = Range("D7:D36")
If Intersect(t, d) Is Nothing Then Exit Sub
If t.Value = "" Then Exit Sub
Sheets(1).Activate
Range("A1").Select
End Sub

Many thanks for your help

Mark

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 342
Default error with code
What is the following code meant to accomplish?

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
Set t = Target
Set d = Range("D7:D36")
If Intersect(t, d) Is Nothing Then Exit Sub
If t.Value = "" Then Exit Sub
Sheets(1).Activate
Range("A1").Select
End Sub

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 141
Default error with code
When cells in sheets 4 to 103 range D7:D36 are filled by one cell at a time,
once filled with text sheet1 is activated.

Mark

"tompl" wrote:

What is the following code meant to accomplish?

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
Set t = Target
Set d = Range("D7:D36")
If Intersect(t, d) Is Nothing Then Exit Sub
If t.Value = "" Then Exit Sub
Sheets(1).Activate
Range("A1").Select
End Sub

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 141
Default error with code
Many thanks jim.

Regards


Mark

"Jim Thomlinson" wrote:

You can turn off events which will stop the SheetChange code from firing. I
would do it something like this

Sub ApplyStock()
Dim strPrompt As String
Dim intbuttons As Integer
Dim strTitle As String

on error goto ErrorHandler
strPrompt = "WARNING!! This action will reset all stock cards and apply
new stock numbers" & vbNewLine & "" & vbNewLine & "This action cannot be
undone
are you sure you want to continue?"
intbuttons = vbYesNo + vbInformation
strTitle = "Stock Maintenance © M Neil "
If MsgBox(strPrompt, intbuttons, strTitle) = vbYes Then

Application.screenupdating = false
Application.enableevents = false
With Sheets("Stock Maintenance")
.Range("C3").Copy Sheet4.Range("B7")
.Range("C4").Copy Sheet5.Range("B7")
.Range("C5").Copy Sheet6.Range("B7")
.Range("C6").Copy Sheet7.Range("B7")
.Range("C7").Copy Sheet8.Range("B7")
.Range("C8").Copy Sheet9.Range("B7")
.Range("C9").Copy Sheet10.Range("B7")
.Range("C10").Copy Sheet11.Range("B7")
.Range("C11").Copy Sheet12.Range("B7")
.Range("C12").Copy Sheet13.Range("B7")
.Range("C13").Copy Sheet14.Range("B7")
.Range("C14").Copy Sheet15.Range("B7")
.Range("C15").Copy Sheet16.Range("B7")
.Range("C16").Copy Sheet17.Range("B7")
.Range("C17").Copy Sheet18.Range("B7")
.Range("C18").Copy Sheet19.Range("B7")
.Range("C19").Copy Sheet20.Range("B7")
.Range("C20").Copy Sheet21.Range("B7")
.Range("C21").Copy Sheet22.Range("B7")
.Range("C22").Copy Sheet23.Range("B7")
.Range("C23").Copy Sheet24.Range("B7")
.Range("C24").Copy Sheet25.Range("B7")
.Range("C25").Copy Sheet26.Range("B7")
.Range("C26").Copy Sheet27.Range("B7")
.Range("C27").Copy Sheet28.Range("B7")
.Range("C28").Copy Sheet29.Range("B7")
.Range("C29").Copy Sheet30.Range("B7")
.Range("C30").Copy Sheet31.Range("B7")
.Range("C31").Copy Sheet32.Range("B7")
.Range("C32").Copy Sheet33.Range("B7")
.Range("C33").Copy Sheet34.Range("B7")
.Range("C34").Copy Sheet35.Range("B7")
.Range("C35").Copy Sheet36.Range("B7")
.Range("C36").Copy Sheet37.Range("B7")
.Range("C37").Copy Sheet38.Range("B7")
.Range("C38").Copy Sheet39.Range("B7")
.Range("C39").Copy Sheet40.Range("B7")
.Range("C40").Copy Sheet41.Range("B7")
.Range("C41").Copy Sheet42.Range("B7")
.Range("C42").Copy Sheet43.Range("B7")
.Range("C43").Copy Sheet44.Range("B7")
.Range("C44").Copy Sheet45.Range("B7")
.Range("C45").Copy Sheet46.Range("B7")
.Range("C46").Copy Sheet47.Range("B7")
.Range("C47").Copy Sheet48.Range("B7")
.Range("C48").Copy Sheet49.Range("B7")
.Range("C49").Copy Sheet50.Range("B7")
.Range("C50").Copy Sheet51.Range("B7")
.Range("C51").Copy Sheet52.Range("B7")
.Range("C52").Copy Sheet53.Range("B7")
.Range("F3").Copy Sheet54.Range("B7")
.Range("F4").Copy Sheet55.Range("B7")
.Range("F5").Copy Sheet56.Range("B7")
.Range("F6").Copy Sheet57.Range("B7")
.Range("F7").Copy Sheet58.Range("B7")
.Range("F8").Copy Sheet59.Range("B7")
.Range("F9").Copy Sheet60.Range("B7")
.Range("F10").Copy Sheet61.Range("B7")
.Range("F11").Copy Sheet62.Range("B7")
.Range("F12").Copy Sheet63.Range("B7")
.Range("F13").Copy Sheet64.Range("B7")
.Range("F14").Copy Sheet65.Range("B7")
.Range("F15").Copy Sheet66.Range("B7")
.Range("F16").Copy Sheet67.Range("B7")
.Range("F17").Copy Sheet68.Range("B7")
.Range("F18").Copy Sheet69.Range("B7")
.Range("F19").Copy Sheet70.Range("B7")
.Range("F20").Copy Sheet71.Range("B7")
.Range("F21").Copy Sheet72.Range("B7")
.Range("F22").Copy Sheet73.Range("B7")
.Range("F23").Copy Sheet74.Range("B7")
.Range("F24").Copy Sheet75.Range("B7")
.Range("F25").Copy Sheet76.Range("B7")
.Range("F26").Copy Sheet77.Range("B7")
.Range("F27").Copy Sheet78.Range("B7")
.Range("F28").Copy Sheet79.Range("B7")
.Range("F29").Copy Sheet80.Range("B7")
.Range("F30").Copy Sheet81.Range("B7")
.Range("F31").Copy Sheet82.Range("B7")
.Range("F32").Copy Sheet83.Range("B7")
.Range("F33").Copy Sheet84.Range("B7")
.Range("F34").Copy Sheet85.Range("B7")
.Range("F35").Copy Sheet86.Range("B7")
.Range("F36").Copy Sheet87.Range("B7")
.Range("F37").Copy Sheet88.Range("B7")
.Range("F38").Copy Sheet89.Range("B7")
.Range("F39").Copy Sheet90.Range("B7")
.Range("F40").Copy Sheet91.Range("B7")
.Range("F41").Copy Sheet92.Range("B7")
.Range("F42").Copy Sheet93.Range("B7")
.Range("F43").Copy Sheet94.Range("B7")
.Range("F44").Copy Sheet95.Range("B7")
.Range("F45").Copy Sheet96.Range("B7")
.Range("F46").Copy Sheet97.Range("B7")
.Range("F47").Copy Sheet98.Range("B7")
.Range("F48").Copy Sheet99.Range("B7")
.Range("F49").Copy Sheet100.Range("B7")
.Range("F50").Copy Sheet101.Range("B7")
.Range("F51").Copy Sheet102.Range("B7")
.Range("F52").Copy Sheet103.Range("B7")
.Range("A1:A2").Select
End With
MsgBox "New stock numbers aplied"
End If

ErrorHandler:
Application.enableevents = true
Application.screenupdating = true
End Sub


--
HTH...

Jim Thomlinson


"terilad" wrote:

Hi,

I have a code but I get an error because of another piece of code I have in
the workbook, here is the code I am using just now:

Sub ApplyStock()
Dim strPrompt As String
Dim intbuttons As Integer
Dim strTitle As String
strPrompt = "WARNING!! This action will reset all stock cards and apply
new stock numbers" & vbNewLine & "" & vbNewLine & "The screen will flicker
for a period" & vbNewLine & "" & vbNewLine & "This action cannot be undone
are you sure you want to continue?"
intbuttons = vbYesNo + vbInformation
strTitle = "Stock Maintenance © M Neil "
If MsgBox(strPrompt, intbuttons, strTitle) = vbYes Then
With Sheets("Stock Maintenance")
Range("C3").Copy Sheet4.Range("B7")
Range("C4").Copy Sheet5.Range("B7")
Range("C5").Copy Sheet6.Range("B7")
Range("C6").Copy Sheet7.Range("B7")
Range("C7").Copy Sheet8.Range("B7")
Range("C8").Copy Sheet9.Range("B7")
Range("C9").Copy Sheet10.Range("B7")
Range("C10").Copy Sheet11.Range("B7")
Range("C11").Copy Sheet12.Range("B7")
Range("C12").Copy Sheet13.Range("B7")
Range("C13").Copy Sheet14.Range("B7")
Range("C14").Copy Sheet15.Range("B7")
Range("C15").Copy Sheet16.Range("B7")
Range("C16").Copy Sheet17.Range("B7")
Range("C17").Copy Sheet18.Range("B7")
Range("C18").Copy Sheet19.Range("B7")
Range("C19").Copy Sheet20.Range("B7")
Range("C20").Copy Sheet21.Range("B7")
Range("C21").Copy Sheet22.Range("B7")
Range("C22").Copy Sheet23.Range("B7")
Range("C23").Copy Sheet24.Range("B7")
Range("C24").Copy Sheet25.Range("B7")
Range("C25").Copy Sheet26.Range("B7")
Range("C26").Copy Sheet27.Range("B7")
Range("C27").Copy Sheet28.Range("B7")
Range("C28").Copy Sheet29.Range("B7")
Range("C29").Copy Sheet30.Range("B7")
Range("C30").Copy Sheet31.Range("B7")
Range("C31").Copy Sheet32.Range("B7")
Range("C32").Copy Sheet33.Range("B7")
Range("C33").Copy Sheet34.Range("B7")
Range("C34").Copy Sheet35.Range("B7")
Range("C35").Copy Sheet36.Range("B7")
Range("C36").Copy Sheet37.Range("B7")
Range("C37").Copy Sheet38.Range("B7")
Range("C38").Copy Sheet39.Range("B7")
Range("C39").Copy Sheet40.Range("B7")
Range("C40").Copy Sheet41.Range("B7")
Range("C41").Copy Sheet42.Range("B7")
Range("C42").Copy Sheet43.Range("B7")
Range("C43").Copy Sheet44.Range("B7")
Range("C44").Copy Sheet45.Range("B7")
Range("C45").Copy Sheet46.Range("B7")
Range("C46").Copy Sheet47.Range("B7")
Range("C47").Copy Sheet48.Range("B7")
Range("C48").Copy Sheet49.Range("B7")
Range("C49").Copy Sheet50.Range("B7")
Range("C50").Copy Sheet51.Range("B7")
Range("C51").Copy Sheet52.Range("B7")
Range("C52").Copy Sheet53.Range("B7")
Range("F3").Copy Sheet54.Range("B7")
Range("F4").Copy Sheet55.Range("B7")
Range("F5").Copy Sheet56.Range("B7")
Range("F6").Copy Sheet57.Range("B7")
Range("F7").Copy Sheet58.Range("B7")
Range("F8").Copy Sheet59.Range("B7")
Range("F9").Copy Sheet60.Range("B7")
Range("F10").Copy Sheet61.Range("B7")
Range("F11").Copy Sheet62.Range("B7")
Range("F12").Copy Sheet63.Range("B7")
Range("F13").Copy Sheet64.Range("B7")
Range("F14").Copy Sheet65.Range("B7")
Range("F15").Copy Sheet66.Range("B7")
Range("F16").Copy Sheet67.Range("B7")
Range("F17").Copy Sheet68.Range("B7")
Range("F18").Copy Sheet69.Range("B7")
Range("F19").Copy Sheet70.Range("B7")
Range("F20").Copy Sheet71.Range("B7")
Range("F21").Copy Sheet72.Range("B7")
Range("F22").Copy Sheet73.Range("B7")
Range("F23").Copy Sheet74.Range("B7")
Range("F24").Copy Sheet75.Range("B7")
Range("F25").Copy Sheet76.Range("B7")
Range("F26").Copy Sheet77.Range("B7")
Range("F27").Copy Sheet78.Range("B7")
Range("F28").Copy Sheet79.Range("B7")
Range("F29").Copy Sheet80.Range("B7")
Range("F30").Copy Sheet81.Range("B7")
Range("F31").Copy Sheet82.Range("B7")
Range("F32").Copy Sheet83.Range("B7")
Range("F33").Copy Sheet84.Range("B7")
Range("F34").Copy Sheet85.Range("B7")
Range("F35").Copy Sheet86.Range("B7")
Range("F36").Copy Sheet87.Range("B7")
Range("F37").Copy Sheet88.Range("B7")
Range("F38").Copy Sheet89.Range("B7")
Range("F39").Copy Sheet90.Range("B7")
Range("F40").Copy Sheet91.Range("B7")
Range("F41").Copy Sheet92.Range("B7")
Range("F42").Copy Sheet93.Range("B7")
Range("F43").Copy Sheet94.Range("B7")
Range("F44").Copy Sheet95.Range("B7")
Range("F45").Copy Sheet96.Range("B7")
Range("F46").Copy Sheet97.Range("B7")
Range("F47").Copy Sheet98.Range("B7")
Range("F48").Copy Sheet99.Range("B7")
Range("F49").Copy Sheet100.Range("B7")
Range("F50").Copy Sheet101.Range("B7")
Range("F51").Copy Sheet102.Range("B7")
Range("F52").Copy Sheet103.Range("B7")
Range("A1:A2").Select
End With
MsgBox "New stock numbers aplied"
End If
End Sub

Here is the code I get an error with, is there a work around for this?

The error is line If Intersect(t, d) Is Nothing Then Exit Sub (this
highlighted yellow)

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
Set t = Target
Set d = Range("D7:D36")
If Intersect(t, d) Is Nothing Then Exit Sub
If t.Value = "" Then Exit Sub
Sheets(1).Activate
Range("A1").Select
End Sub

Many thanks for your help

Mark

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
error in code des-sa[_2_] Excel Discussion (Misc queries) 5 July 3rd 08 08:09 AM
error in this code [email protected] Excel Worksheet Functions 2 December 5th 06 05:21 AM
Code Error Tom Excel Discussion (Misc queries) 0 February 21st 06 03:01 PM
Error Code 481 MBlake Excel Discussion (Misc queries) 1 May 11th 05 01:27 PM
Code error ?? Anthony Excel Discussion (Misc queries) 2 February 9th 05 10:31 PM


All times are GMT +1. The time now is 01:12 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement