From: terilad on
The error hapens when I run the macro ApplyStock


Mark

"Don Guillett" wrote:

> If desired, send your file to my address below. I will only look if:
> 1. You send a copy of this message on an inserted sheet
> 2. You give me the newsgroup and the subject line
> 3. You send a clear explanation of what you want
> 4. You send before/after examples and expected results.
>
> And tell me where the error occurs
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett(a)gmail.com
> "terilad" <terilad(a)discussions.microsoft.com> wrote in message
> news:4D1738A7-41AE-4C58-8765-15977C0302E1(a)microsoft.com...
> > Hi Don,
> >
> > I have rewritten code to be like your example but I get an error because
> > of
> > another piece of code I have in the workbook, here is the code I am using
> > 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 code I get an error with, is there a work around for this?
> >
> > 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
> >
> > "Don Guillett" wrote:
> >
> >> Sub ApplyStock()
> >> 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")
> >> end with
> >> End Sub
> >>
> >> Even better
> >> Sub stockbetter()
> >> With Sheets("sheet1")
> >> For i = 3 To 6
> >> .Cells(i, "c").Copy Sheets(i + 1).Range("b7")
> >> Next i
> >> End With
> >> End Sub
> >> --
> >> Don Guillett
> >> Microsoft MVP Excel
> >> SalesAid Software
> >> dguillett(a)gmail.com
> >> "terilad" <terilad(a)discussions.microsoft.com> wrote in message
> >> news:1FA8951C-095E-4939-A7C5-D46004235B88(a)microsoft.com...
> >> > Hi I have a copy and paste code
> >> >
> >> > I was wondering if there is a way to clean it up a little to run it
> >> > more
> >> > better.
> >> >
> >> > Here's my code.
> >> >
> >> > Sub ApplyStock()
> >> > Range("C3").Copy
> >> > Sheet4.Select
> >> > Range("B7").Select
> >> > ActiveSheet.Paste
> >> > Sheets("Stock Maintenance").Select
> >> > Range("C4").Copy
> >> > Sheet5.Select
> >> > Range("B7").Select
> >> > ActiveSheet.Paste
> >> > Sheets("Stock Maintenance").Select
> >> > Range("C5").Copy
> >> > Sheet6.Select
> >> > Range("B7").Select
> >> > ActiveSheet.Paste
> >> > Sheets("Stock Maintenance").Select
> >> > Range("C6").Copy
> >> > Sheet7.Select
> >> > Range("B7").Select
> >> > ActiveSheet.Paste
> >> > End Sub
> >> >
> >> > Many thanks
> >> >
> >> >
> >> > Mark
> >>
> >> .
> >>
>
> .
>
From: Don Guillett on

See my previous msg about sending file.
I can't take the time to guess
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"terilad" <terilad(a)discussions.microsoft.com> wrote in message
news:10473DA1-FF3A-40FD-8724-877DDC19A22A(a)microsoft.com...
> The error hapens when I run the macro ApplyStock
>
>
> Mark
>
> "Don Guillett" wrote:
>
>> If desired, send your file to my address below. I will only look
>> if:
>> 1. You send a copy of this message on an inserted sheet
>> 2. You give me the newsgroup and the subject line
>> 3. You send a clear explanation of what you want
>> 4. You send before/after examples and expected results.
>>
>> And tell me where the error occurs
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguillett(a)gmail.com
>> "terilad" <terilad(a)discussions.microsoft.com> wrote in message
>> news:4D1738A7-41AE-4C58-8765-15977C0302E1(a)microsoft.com...
>> > Hi Don,
>> >
>> > I have rewritten code to be like your example but I get an error
>> > because
>> > of
>> > another piece of code I have in the workbook, here is the code I am
>> > using
>> > 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 code I get an error with, is there a work around for this?
>> >
>> > 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
>> >
>> > "Don Guillett" wrote:
>> >
>> >> Sub ApplyStock()
>> >> 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")
>> >> end with
>> >> End Sub
>> >>
>> >> Even better
>> >> Sub stockbetter()
>> >> With Sheets("sheet1")
>> >> For i = 3 To 6
>> >> .Cells(i, "c").Copy Sheets(i + 1).Range("b7")
>> >> Next i
>> >> End With
>> >> End Sub
>> >> --
>> >> Don Guillett
>> >> Microsoft MVP Excel
>> >> SalesAid Software
>> >> dguillett(a)gmail.com
>> >> "terilad" <terilad(a)discussions.microsoft.com> wrote in message
>> >> news:1FA8951C-095E-4939-A7C5-D46004235B88(a)microsoft.com...
>> >> > Hi I have a copy and paste code
>> >> >
>> >> > I was wondering if there is a way to clean it up a little to run it
>> >> > more
>> >> > better.
>> >> >
>> >> > Here's my code.
>> >> >
>> >> > Sub ApplyStock()
>> >> > Range("C3").Copy
>> >> > Sheet4.Select
>> >> > Range("B7").Select
>> >> > ActiveSheet.Paste
>> >> > Sheets("Stock Maintenance").Select
>> >> > Range("C4").Copy
>> >> > Sheet5.Select
>> >> > Range("B7").Select
>> >> > ActiveSheet.Paste
>> >> > Sheets("Stock Maintenance").Select
>> >> > Range("C5").Copy
>> >> > Sheet6.Select
>> >> > Range("B7").Select
>> >> > ActiveSheet.Paste
>> >> > Sheets("Stock Maintenance").Select
>> >> > Range("C6").Copy
>> >> > Sheet7.Select
>> >> > Range("B7").Select
>> >> > ActiveSheet.Paste
>> >> > End Sub
>> >> >
>> >> > Many thanks
>> >> >
>> >> >
>> >> > Mark
>> >>
>> >> .
>> >>
>>
>> .
>>

From: terilad on
Only problem being it is a very large file to try and send.

I will give it a go.

Thanks


Mark

"Don Guillett" wrote:

>
> See my previous msg about sending file.
> I can't take the time to guess
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett(a)gmail.com
> "terilad" <terilad(a)discussions.microsoft.com> wrote in message
> news:10473DA1-FF3A-40FD-8724-877DDC19A22A(a)microsoft.com...
> > The error hapens when I run the macro ApplyStock
> >
> >
> > Mark
> >
> > "Don Guillett" wrote:
> >
> >> If desired, send your file to my address below. I will only look
> >> if:
> >> 1. You send a copy of this message on an inserted sheet
> >> 2. You give me the newsgroup and the subject line
> >> 3. You send a clear explanation of what you want
> >> 4. You send before/after examples and expected results.
> >>
> >> And tell me where the error occurs
> >> --
> >> Don Guillett
> >> Microsoft MVP Excel
> >> SalesAid Software
> >> dguillett(a)gmail.com
> >> "terilad" <terilad(a)discussions.microsoft.com> wrote in message
> >> news:4D1738A7-41AE-4C58-8765-15977C0302E1(a)microsoft.com...
> >> > Hi Don,
> >> >
> >> > I have rewritten code to be like your example but I get an error
> >> > because
> >> > of
> >> > another piece of code I have in the workbook, here is the code I am
> >> > using
> >> > 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 code I get an error with, is there a work around for this?
> >> >
> >> > 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
> >> >
> >> > "Don Guillett" wrote:
> >> >
> >> >> Sub ApplyStock()
> >> >> 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")
> >> >> end with
> >> >> End Sub
> >> >>
> >> >> Even better
> >> >> Sub stockbetter()
> >> >> With Sheets("sheet1")
> >> >> For i = 3 To 6
> >> >> .Cells(i, "c").Copy Sheets(i + 1).Range("b7")
> >> >> Next i
> >> >> End With
> >> >> End Sub
> >> >> --
> >> >> Don Guillett
> >> >> Microsoft MVP Excel
> >> >> SalesAid Software
> >> >> dguillett(a)gmail.com
> >> >> "terilad" <terilad(a)discussions.microsoft.com> wrote in message
> >> >> news:1FA8951C-095E-4939-A7C5-D46004235B88(a)microsoft.com...
> >> >> > Hi I have a copy and paste code
> >> >> >
> >> >> > I was wondering if there is a way to clean it up a little to run it
> >> >> > more
> >> >> > better.
> >> >> >
> >> >> > Here's my code.
> >> >> >
> >> >> > Sub ApplyStock()
> >> >> > Range("C3").Copy
> >> >> > Sheet4.Select
> >> >> > Range("B7").Select
> >> >> > ActiveSheet.Paste
> >> >> > Sheets("Stock Maintenance").Select
> >> >> > Range("C4").Copy
> >> >> > Sheet5.Select
> >> >> > Range("B7").Select
> >> >> > ActiveSheet.Paste
> >> >> > Sheets("Stock Maintenance").Select
> >> >> > Range("C5").Copy
> >> >> > Sheet6.Select
> >> >> > Range("B7").Select
> >> >> > ActiveSheet.Paste
> >> >> > Sheets("Stock Maintenance").Select
> >> >> > Range("C6").Copy
> >> >> > Sheet7.Select
> >> >> > Range("B7").Select
> >> >> > ActiveSheet.Paste
> >> >> > End Sub
> >> >> >
> >> >> > Many thanks
> >> >> >
> >> >> >
> >> >> > Mark
> >> >>
> >> >> .
> >> >>
> >>
> >> .
> >>
>
> .
>