clean up code a little
in [General]
|
From: terilad on 12 Apr 2010 13:21 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 12 Apr 2010 13:51 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 12 Apr 2010 14:35
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 > >> >> > >> >> . > >> >> > >> > >> . > >> > > . > |