error with code

T

terilad

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
 
J

Jim Thomlinson

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
 
T

tompl

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
 
T

terilad

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
 
T

terilad

Many thanks jim.

Regards


Mark

Jim Thomlinson said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

clean up code a little 12
Halt Rest of Macro after Error – Jim Cone review 2
Code Help 2
userinterfaceonly=True fails with this code! 1
Select Case code error 8
Code with hidden flaw ERROR 400 13
Cancel Input Box error 16
.sendusingaccount ? for OL 2007 12

Top